topic Dynamic Excel Table Names in QlikView

Dynamic Excel Table Names

Wed, 25 Nov 2020 16:16:04 GMT

Dear Community,

I am using this script from another thread to load from multiple Excel files in a folder but I need something in the script that can change the table name.

Set vConcatenate = ;

sub ScanFolder(Root)

for each FileExtension in 'csv'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

FileList:

$(vConcatenate)

LOAD *, '$(FoundFile)' as SourceFile

FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

The section that goes: (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Needs to be replaced with something like the below - the table names are the first 31 characters of the file name which occurs after the final backslack in the "FoundFile".

Let vTable = mid($(FoundFile),index($(FoundFile),'\',-1),31);

(ooxml, embedded labels, table is [$(vTable)]);

As my table names are the first 31 characters of the file name.

Please can someone help me script this correctly

Re: Dynamic Excel Table Names

jonathandienst — Mon, 27 Jun 2016 08:40:04 GMT

Simpler expression (and you don't need a $ exapansion in the let):

Let vTable Left(SubField(FoundFile, '\', -1), 31);

The rest of the problem you have already answered.

Re: Dynamic Excel Table Names

Mon, 27 Jun 2016 09:08:03 GMT

This is spot on.....

Except I forgot I need to take off the file extension (.xls) - any ideas?

Re: Dynamic Excel Table Names

Mon, 27 Jun 2016 09:11:14 GMT

sorry that might not be clear - turns out the sheet name is the full file name less the file extension - I need to exclude everything from the "." onwards (.xls)

Re: Dynamic Excel Table Names

jonathandienst — Mon, 27 Jun 2016 10:57:11 GMT

Then try

Let vTable = SubField(SubField(FoundFile, '\', -1), '.', 1);