load multiple .csv files to create a single data files

[0li5a3a]

Hi All,

I need an advise from an expert in loading multiple .csv files.

EX:

abc1.csv, abc2.csv, abc3.csv, abc4.csv ...........abc100.csv.

All good the same haeaders names.

I tried something like this:

let PathToDirectory = 'Z:\query38\*';

for Each File in FileList ('$(PathToDirectory)abc*.csv)' )

yourtable:

LOAD

*

FROM '$(File)'(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT File;

Kind regards,

[sunny_talwar]

Why do you have an asterisk here?

May be try without it

LET PathToDirectory = 'Z:\query38\';

[rwunderlich]

You should be able to load them with just a wildcard in the filename:

LOAD ...

FROM abc*.csv

No for loop necessary.

-Rob

[hic]

Looks good. Does it work? You may have an asterisk too much, though - the one in the Let statement.

See also Loops in the Script

HIC

[sunny_talwar]

I was going to refer to hic‌ for a comment he might have made and here he is...

Henric - Correct me if you I am wrong, but I have read somewhere that you prefer to use For Loops over wildcard load as they Wildcards are problematic in some cases where it creates multiple tables. Is that something you do on case by case basis or is that the approach you would always recommend to take?

rwunderlich‌ - When is the next Master Summit in the US? Do we have a date yet?

[rwunderlich]

The 2018 US date has not been set yet, but it will be sometime in Sept-Oct.  Or come see us in Prague in April.

Problems do occur with wildcard loads if you use preceding load or load prefixes.  But for a straight-ahead load, I find they work fine and are easier to write (and read).

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[0li5a3a · Accepted solution]

vPath = 'Z:\query38';

For each File in FileList('$(vPath)\abc*.csv')

TableName:

Load

*

From $(File)(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

NEXT File;

this works now. Thanks all for the help

[hic]

I always use a For-Next loop for cases like this, and I think it is good practice.

Rob's solution with a wild card directly in the file reference works excellently, as long as

• All files have identical sets of fields.
• No preceding load exists.
• No load prefix exists

But, I have more than once started with the wild card in the file name, and later (sometimes weeks later) had to change it to a For-Next loop, since the newest log file now contains an additional field. Or I have had to add a preceding Load. So, now I always go for the For-Next solution right away.

HIC

[sunny_talwar]

I don't think my company would sponsor a trip to Prague, specially when there is a Master Summit which will take place in the US. I will keep my eyes and ears open for Master Summit 2018 (US).

Problems do occur with wildcard loads if you use preceding load or load prefixes.  But for a straight-ahead load, I find they work fine and are easier to write (and read).

Make sense .

[sunny_talwar]

Thanks hic‌ -  This is what I have found in most of the cases. But knowing when it might not work is the key to this. Thanks for sharing this information.

Best,

Sunny