Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic Excel Table Names

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

This is spot on.....

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

Not applicable
Author

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)

jonathandienst
Partner - Champion III
Partner - Champion III

Then try


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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein