Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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 *;
hi Gyspert
You're right but my goal is to have each excel file in different table separately.
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
Thank you Jonathan, it really helps.