Automatic load files with additional static column

inigoelv — Sat, 21 Jan 2017 07:31:45 GMT

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.

Re: Automatic load files with additional static column

Gysbert_Wassenaar — Sat, 21 Jan 2017 08:52:25 GMT

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.

Re: Automatic load files with additional static column

satheshreddy — Sat, 21 Jan 2017 10:12:13 GMT

Hi Elviro,

Same Extention? what ever u r loading files.

Regards

Sathish

topic Re: Automatic load files with additional static column in QlikView

Automatic load files with additional static column

Re: Automatic load files with additional static column

Re: Automatic load files with additional static column