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: 
inigoelv
Creator
Creator

Automatic load files with additional static column

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.

2 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
satheshreddy
Creator III
Creator III

Hi Elviro,

Same Extention? what ever u r loading files.

Regards

Sathish