topic Re: How to Load File Names in a Folder to a Table? in QlikView

How to Load File Names in a Folder to a Table?

retko1985 — Mon, 02 Jul 2018 09:55:23 GMT

Hello,

I tried something like this:

FOR Each vFile in FileList('$(vFileDir)*.csv')

LOAD

filename() AS AAA

FROM

[$(vFile)]

(txt, utf8, embedded labels, delimiter is ',', msq);

NEXT vFile;

Unfortunate it doesn't work. What I want is to have 1 row for every file name in the folder. But I get as many rows as the file have and every row has file name. This seems to be also quite slow. Tried to use FirstValue, but it is also slow.

Do you have some advices please?

Thanks

Re: How to Load File Names in a Folder to a Table?

enriquem — Mon, 02 Jul 2018 10:04:11 GMT

Hi!

Try this:

FOR Each vFile in FileList('$(vFileDir)*.csv')

LOAD

filename() AS AAA

FROM

[$(vFile)]

(txt, utf8, embedded labels, delimiter is ',', msq)

ORDER BY

AAA;

NEXT vFile;

Regards!

Re: How to Load File Names in a Folder to a Table?

big_dreams — Mon, 02 Jul 2018 10:10:06 GMT

might be distinct keyword help you..

FOR Each vFile in FileList('$(vFileDir)*.csv')

LOAD

filename() AS AAA

FROM

[$(vFile)]

(txt, utf8, embedded labels, delimiter is ',', msq);

NEXT vFile;

or use FIRST keyword

FOR Each vFile in FileList('$(vFileDir)*.csv')

First

LOAD

filename() AS AAA

FROM

[$(vFile)]

(txt, utf8, embedded labels, delimiter is ',', msq);

NEXT vFile;

Regards,

Re: How to Load File Names in a Folder to a Table?

Peter_Cammaert — Mon, 02 Jul 2018 10:12:35 GMT

Way simpler. vFile already contains the file name, so why do you want to open each file again?

FOR Each vFile in FileList('$(vFileDir)*.csv')

LOAD '$(vFile)' AS FileName

AUTOGENERATE 1;

NEXT vFile;

You can add RowNo() AS ID to get a sequential number for each filename.

Re: How to Load File Names in a Folder to a Table?

YoussefBelloum — Mon, 02 Jul 2018 10:20:12 GMT

Hi,

if you want ONLY the name without the extension, use FileBaseName() function

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/FileFunctions/FileBaseName.htm

Re: How to Load File Names in a Folder to a Table?

retko1985 — Mon, 02 Jul 2018 10:58:42 GMT

Exactly what I wanted

Thank you.