Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

read dynamically many excel files from the same folder

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;

NEXT

sFileName

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

1 Solution

Accepted Solutions
MVP
MVP

Re: read dynamically many excel files from the same folder

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies

Re: read dynamically many excel files from the same folder

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 *;


talk is cheap, supply exceeds demand
Not applicable

Re: read dynamically many excel files from the same folder

hi Gyspert

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

MVP
MVP

Re: read dynamically many excel files from the same folder

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: read dynamically many excel files from the same folder

Thank you Jonathan, it really helps.

Community Browser