topic Re: Loading file content into QV from a list of files in QlikView

Loading file content into QV from a list of files

Fri, 24 Apr 2015 15:48:39 GMT

I have a list of files located on a remote server mapped to M:

This file, QVLogs.txt, is a file with file path names such as:

M:\QDS_ENV_4\App_Data\1\Log\20150324\ThisOne\DocumentLog.txt

M:\QDS_ENV_4\App_Data\1\Log\20150324\AnotherDirectory\DocumentLog.txt

M:\QDS_ENV_4\App_Data\2\Log\20150321\OneMore\DocumentLog.txt

M:\QDS_ENV_4\App_Data\3\Log\20150322\MoreMoreMore\DocumentLog.txt

M:\QDS_ENV_4\App_Data\1\Log\20150326\LastOne\DocumentLog.txt

These are QDS (QlikView) log files, similar to document log files when generating a QVW. The files will remain on remote server.

I would like to load the content of each of these files into one table in QlikView.

I have looked into filelist function, but that seems to return a list of files from a directory, rather than loading them.

This seems like it would be simple, but I'm stuck.... any ideas?

Re: Loading file content into QV from a list of files

Fri, 24 Apr 2015 15:50:44 GMT

added information: the list of files with the path/file names is stored locally, in C:\Temp.

Re: Loading file content into QV from a list of files

Gysbert_Wassenaar — Fri, 24 Apr 2015 16:04:20 GMT

You can find code to loop through subdirectories and files in those directories here: loop through to load all files from a folder and its subfolders?

Re: Loading file content into QV from a list of files

Fri, 24 Apr 2015 16:25:26 GMT

Thanks Gysbert -

I understand that that code is if I have the files in directories, not a list of files. I may not understand fully.

Re: Loading file content into QV from a list of files

Gysbert_Wassenaar — Fri, 24 Apr 2015 16:31:15 GMT

Oh, right. My mistake. Perhaps something like this then:

Files:

LOAD @1 as LogFile FROM QVLogs.txt (txt, ...etc);

SET vConcatenate= ;

FOR i =0 to NoOfRows('Files') - 1

LET vFileName = peek('LogFile',$(i));

$(vConcatenate)

LogFileData:

LOAD * FROM [$(vFileName)] (txt, ...etc);

SET vConcatenate = Concatenate;

Re: Loading file content into QV from a list of files

Fri, 24 Apr 2015 19:07:11 GMT

Great information Gysbert! The results are:

- the first time it runs (if I comment out the NEXT), it's fine. The first log file is loaded. But the variable vFileName is not being set, so....

- when it runs again (via NEXT), it can't find file.

- I will continue troubleshooting this, but it's very close!

Thanks,

Matt

Re: Loading file content into QV from a list of files

rwunderlich — Fri, 24 Apr 2015 20:18:56 GMT

It's a common mistake. You need to add the tablename parameter into the peek.

LET vFileName = peek('LogFile',$(i),'Files');

Leaving off the tablename works first time through the loop, but not subsequent times.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Loading file content into QV from a list of files

Mon, 27 Apr 2015 18:56:43 GMT

Thank you Gysbert and Rob!

Here is the final snippet:

Files:
LOAD @1 as LogFile FROM (txt, codepage is 1252, no labels, delimiter is '\t', msq);
SET vConcatenate= ;

FOR i =0 to NoOfRows('Files')-1

LET vFileName = peek('LogFile', $(i), 'Files');

$(vConcatenate)
LOAD * FROM $(vFileName) (txt, codepage is 1252, no labels, delimiter is '\t', msq);

SET vConcatenate = Concatenate;
//
NEXT