read dynamically many excel files from the same folder

[#1]

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.

[Gysbert_Wassenaar]

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

[#3]

hi Gyspert

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

[jonathandienst · Accepted solution]

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

[#5]

Thank you Jonathan, it really helps.