topic Re: load multiple .csv files to create a single data files in QlikView

load multiple .csv files to create a single data files

0li5a3a — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: load multiple .csv files to create a single data files

sunny_talwar — Thu, 18 Jan 2018 15:29:46 GMT

Why do you have an asterisk here?

May be try without it

LET PathToDirectory = 'Z:\query38\';

Re: load multiple .csv files to create a single data files

rwunderlich — Thu, 18 Jan 2018 15:29:50 GMT

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

LOAD ...

FROM abc*.csv

No for loop necessary.

-Rob

Re: load multiple .csv files to create a single data files

hic — Thu, 18 Jan 2018 15:30:50 GMT

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

Re: load multiple .csv files to create a single data files

sunny_talwar — Thu, 18 Jan 2018 15:34:29 GMT

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?

Re: load multiple .csv files to create a single data files

rwunderlich — Thu, 18 Jan 2018 15:42:28 GMT

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

Re: load multiple .csv files to create a single data files

0li5a3a — Thu, 18 Jan 2018 15:45:24 GMT

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

Re: load multiple .csv files to create a single data files

hic — Thu, 18 Jan 2018 15:45:49 GMT

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

Re: load multiple .csv files to create a single data files

sunny_talwar — Thu, 18 Jan 2018 15:46:38 GMT

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 .

Re: load multiple .csv files to create a single data files

sunny_talwar — Thu, 18 Jan 2018 16:01:05 GMT

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

Re: load multiple .csv files to create a single data files

Anonymous — Thu, 18 Jan 2018 16:32:56 GMT

anyone know how I can load multiple sheets from one excel, but all sheets have differente names and different data + tables? :s

Re: load multiple .csv files to create a single data files

rwunderlich — Thu, 18 Jan 2018 19:19:23 GMT

I suggest you start this question in a new thread.

-Rob

Re: load multiple .csv files to create a single data files

0li5a3a — Fri, 19 Jan 2018 07:51:42 GMT

maybe the below code can help you.

a, b, c, d... are tabs in an excel folder.

ex:

FOR EACH vSheet IN 'A','B','C','D'... etc

LOAD*

FROM

(ooxml, embedded labels, table is $(vSheet));