topic Re: How to Load excel file name from specific folder into listbox in QlikView

How to Load excel file name from specific folder into listbox

saivina2920 — Sat, 16 Nov 2024 04:17:16 GMT

I am having multiple Excel file in my specific folder. I just want to fetch all the file excel filname in my list box.

For example,

C:\Test\sample1.xlsx

\sample2.xlsx

......................

\sample10.xlsx

.......................

In my listbox, i just want to list all the file such as "sample1.xlsx,sample2.xlsx,.......sample10.xlsx".....)

file is dynamic and it should automatically load into listbox.

Re: How to Load excel file name from specific folder into listbox

jonathandienst — Mon, 04 Mar 2019 11:07:08 GMT

Something like:

For Each vFile in FileList('C:\Test\sample*.xlsx')

	Files:
	LOAD FileBaseName() as FileName
	FROM [$(vFile)]
	(ooxml);
	
Next

Now use FileName in your listbox.

Re: How to Load excel file name from specific folder into listbox

saivina2920 — Mon, 04 Mar 2019 11:50:07 GMT

Thanks for your reply.

I want to trim and get only first words of every filename.

Filename will be : sample1_20190_989.xlsx, sample2_20190_989.xlsx,Test101_20190_989.xlsx,etc

So final list box would be

sample1

sample2

Test101

i am getting Script error.

Error Details below :

LOAD FileBaseName() as ProjectName
FROM [C:\projects\~$101_210_Main report.xlsx]
(ooxml)

Note : File name may be anything. so i want get all the files from the folder.

Re: How to Load excel file name from specific folder into listbox

chrislemm — Mon, 04 Mar 2019 12:57:22 GMT

If you want to use wildcards in your filenames you can replace the "wild"-part with an *.

For example:

SET vPathFilter = '<filepath\*_specific_file_ending.xlsx';
For Each vTempFile in FileList('$(vPathFilter)')
Images:
LOAD
'$(vTempFile)' as FileName
AutoGenerate(1);
Next

Re: How to Load excel file name from specific folder into listbox

saivina2920 — Mon, 04 Mar 2019 13:08:11 GMT

Thanks..

not load the corrected script.

SET vPathFilter = '<C:\Projects\\*_.xlsx>';
For Each vTempFile in FileList('$(vPathFilter)')
Images:
LOAD
'$(vTempFile)' as FileName
AutoGenerate(1);
Next

"FileName" not loading in the listbox. i'm using wild *_ to use first word...

Re: How to Load excel file name from specific folder into listbox

jonathandienst — Tue, 05 Mar 2019 06:15:01 GMT

To get only first part of filename and get all files, not just excel:

For Each vFile in FileList('C:\Projects\*.*')

	Let vFileBase = SubField(SubField(vFile, '\', -1), '_', 1);
	
	Files:
	LOAD '$(vFileBase)' as FileName
	Autogenerate 1;
	
Next

Re: How to Load excel file name from specific folder into listbox

saivina2920 — Tue, 05 Mar 2019 07:26:04 GMT

Excellent. Its working...