Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Loading file content into QV from a list of files

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?

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

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;

NEXT


talk is cheap, supply exceeds demand
7 Replies
Not applicable

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

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

MVP & Luminary
MVP & Luminary

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

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?


talk is cheap, supply exceeds demand
Not applicable

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

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.

MVP & Luminary
MVP & Luminary

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

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;

NEXT


talk is cheap, supply exceeds demand
Not applicable

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

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

MVP & Luminary
MVP & Luminary

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

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

Not applicable

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

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