Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
This is spot on.....
Except I forgot I need to take off the file extension (.xls) - any ideas?
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)
Then try
Let vTable = SubField(SubField(FoundFile, '\', -1), '.', 1);