topic automatically load all Data from directory in QlikView

automatically load all Data from directory

Wed, 05 Jan 2011 13:04:39 GMT

Hi,

I have several CSV data in Directory for example C:\test\. The all Data have the same filed. In every month come the new date in this directory for actual month.

For example:

1) Date_1 (data from January)

2) Date_2 (data from February) and.....

How can I load automatically the all Data from Directory: C:\test\*.csv in QVW and concanate all together automatically in one table?

Thanks.

automatically load all Data from directory

Miguel_Angel_Baeyens — Wed, 05 Jan 2011 13:19:01 GMT

Hello,

Something like the following should work

SET vDirectory = 'C:\QlikView\Data\'; Data: LOAD * FROM [$(vDirectory)*.qvd] (qvd);

or in case of csv

SET vDirectory = 'C:\QlikView\Data\'; Table:LOAD *FROM // enclosed in case there are some blanks in the path(txt, codepage is 1252, no labels, delimiter is '\t', msq);

If all files have the same number and name of fields, they will be concatenated implicitly with no need of loop or conversion.

Hope this helps

automatically load all Data from directory

Wed, 05 Jan 2011 13:21:20 GMT

Hi ,

the statemant you need will look like this:

LOAD

YourFields

FROM [Date_*.xls] (biff, embedded labels, table is Sheet1$);

Are field names the same QV automatically concatenate it.

Good luck!

Rainer

automatically load all Data from directory

Wed, 05 Jan 2011 13:24:48 GMT

Hey i use to do the same many times . Put as many as xls file in you folder it will come to your doc.

Use the Below in your edit script

SET MODELED_Folder="C:\Users\Administrator\Desktop\MY_Report";

for each file in filelist('$(MODELED_Folder)\*.xls')
load*
from $(file)(biff, embedded labels, table is Sheet1$);
next file

If req any help or query ... invited 🙂

Regards

Ashwin

automatically load all Data from directory

Wed, 05 Jan 2011 16:57:05 GMT

Hi,

Thanks, that works perfekty.

Regards

automatically load all Data from directory

Thu, 27 Jan 2011 17:18:57 GMT

Just wanted to thank you because this post has saved me a LOT of time.

automatically load all Data from directory

Tue, 08 Mar 2011 18:26:25 GMT

Hi Miguel

I read your reply with interest and have a similar problem. I have a lot of "identical" files that are snapshots taken on different days. For convenience they have been organised into folders by month i.e. I have a directory for a specific report with folders Jan, Feb, Mar etc. I know I can lift all the files from within one of these folders through a single load statement but is there a way to lift all those in the subfolders without having a separate load statement for each subfolder?

I'm just starting with QV and have a lot of files that are organised this way that I would potentially want to lift into QV.

Thanks

automatically load all Data from directory

Miguel_Angel_Baeyens — Wed, 09 Mar 2011 09:30:54 GMT

Hello Kathryn,

As far as I know (have tested) LOAD in conjunction with "*" works with the current directory only, either one set manually, variable, using the "Directory;" statement or using the current one (when no directory is set).

So to loop in a given tree structure, you should use a loop function, creating variables so you get the next directory to be read, and once there, yes, using the "*" to load all files within it. There are many posts about this, this one, for example.

Hope that helps.

automatically load all Data from directory

Wed, 09 Mar 2011 10:07:40 GMT

Hi Miguel

Thank you, that was exactly what I was looking for. I had looked through the community site but didn't find that post - think I need to work on my searches

with thanks

Kathryn