Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody:
I need the help with one script.
I need load several excell files, more than 100, with the same structure.
For this I have used the following script which works very weel:
Set vConcatenate = ;
sub ScanFolder(Root)
for each FileExtension in 'xls'
for each FoundFile in filelist( Root & '\*.' & FileExtension)
FileList:
$(vConcatenate)
LOAD
*,
'$(FoundFile)' as SourceFile
FROM [$(FoundFile)] (biff, embedded labels, header is 3 lines, table is Hoja1$) where [NUM. ROLLO]<>'0';
Set vConcatenate = Concatenate;
next FoundFile
next FileExtension
for each SubDirectory in dirlist( Root & '\*' )
call ScanFolder(SubDirectory)
next SubDirectory
end sub
Call ScanFolder('C:\Users\inigoelv\Documents\Informes') ;
The problem is in all files are additional information in some specific cell value, it is only one value.
When I try to insert that specific value as value common in every row of each file that doesn´t works:
left join
FIRST 1 LOAD
F3 as Descripcion,
F21 as Orden
FROM [$(FoundFile)] (biff, embedded labels, header is 1 lines, table is Hoja1$);
left join
FIRST 1 LOAD
@19 as MP
FROM
[$(FoundFile)]
(biff, no labels, header is 1 lines, table is Hoja1$);
Depending of the position I inserted, some times freeze, some times works with the first file and no with the others, etc...
Any solution?
Thanks in advance.
You should really fix this in your excel sheets. Putting that one specific value on every row in an extra column would be the easiest fix. You'd have to make no changes to the script.
An other solution is more complicated. You could create a new table with two fields: the file name of the excel as SourceFile and the specific single value in a Value field. Create that table first as a mapping table. Then when you load the data from the excel files in the loop you can use Applymap to add a column using the file name to look up the value in the mapping table.
Hi Elviro,
Same Extention? what ever u r loading files.
Regards
Sathish