topic Re: read dynamically many excel files from the same folder in QlikView

read dynamically many excel files from the same folder

Fri, 02 Nov 2012 12:49:26 GMT

Hello ,

I'm trying to upload dynamically all the excel files existed in the same folder and each excel file uploaded will be saved as a table .

The name of these tables should be the same name of the excel files uploaded.

My problem is that all the excel files are being concatenated in a single table,i tried to use 'noconcatenate' but it didn't work.

This is my script:

let

vPath='D:\DistributionGroups\*.xls';

LET vFileName = subfield(vPath,'\',SubStringCount(vPath,'\')+1);

FOR

EACH sFileName IN FILELIST ('$(vPath)')

ODBC

CONNECT32 TO [Excel Files;DBQ=$(vFileName)];

SQLTables;

DISCONNECT;

['$(sFileName)']:

NoConcatenate

LOAD [Age Group] as ReadMemberAge ,

     Class as ReadMemberClas,

     [Insured Relation] as ReadMemberRelation,

     ReadNumber as Numb,

    [Insured Relation] & '|' & [Age Group] & '|' & Class as tarifKey



from [$(sFileName)](ooxml, embedded labels, table is Sheet1);

DISCONNECT;

sFileName

Hope that somebody will help me to solve this issue.Thank you.

Re: read dynamically many excel files from the same folder

Gysbert_Wassenaar — Fri, 02 Nov 2012 13:21:08 GMT

All excel files have the same fields, so everything ends up in one table. This is usually a good thing. You might consider adding an extra field with the file name the records are retrieved from. That way you have only one table, but can always see which file the records came from. If you really want separate tables you can add a qualify statement to the load script above the loop: qualify *;

Re: read dynamically many excel files from the same folder

Fri, 02 Nov 2012 13:52:53 GMT

hi Gyspert

You're right but my goal is to have each excel file in different table separately.

Re: read dynamically many excel files from the same folder

jonathandienst — Fri, 02 Nov 2012 14:09:25 GMT

May I ask why?

This is not the way QV is designed to operate. And if you force Qv to load into separate tables (which is possible) you will land up with a large synthetic key between all the tables which would have a major hit on performance.

If you qualify, then each spreadsheet will be a data island and it will be difficult to construct QV objects using this data.

The way this scenario is usually handled in QV is to create a single table, but add a line in the LOAD

FileName() as DataSource,

Then to view the data from each file, create a list box with DataSource and select the desired filename from there. If the filenames contain logic (like "Budget201201.xlsx", "Actual201201.xlsx", etc) you could also decode that and have the components in list box(es).

Hope this helps

Jonathan

Re: read dynamically many excel files from the same folder

Tue, 06 Nov 2012 09:34:14 GMT

Thank you Jonathan, it really helps.