Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a number of Excel docs in a folder with naming convention of FileName_YYMMDD.
The problem is that the docs may contain a different number of sheets i.e. Doc1 has Sheet1/Sheet2
while Doc2 has Sheet1/Sheet2/Sheet3 etc.
I need to create a table of Doc name and Sheets belonging to that Doc.
Then I want to load the sheets from their associated docs in a loop statement.
Anyone any ideas?
Thanks
You can add '$(sheetname)' as Year in the load script.
Sorry to bother u again & again Sir,
Since i've a cross table with form Crosstable(A,B,7) Load col1,col2........col9, col10,col11 from excel file;
where shud i add this ?
if i add this in script then is it correct to have as:
Crosstable(A,B,8) Load col1,col2,'$(sheetname)' as Year........col9, col10,col11 from excel file;
i've also tried:
load *,
'$(sheetname)' as Year resident data;
drop table data;
But failed, plz tell what to do
Regards
You can use the below load statments to get the sheetname as Year field.
for each file in FileList('C:\Temp\*.xlsx')
ODBC CONNECT TO [Excel Files;DBQ=$(file)];
tables:
SQLTABLES;
DISCONNECT;
for i = 0 to NoOfRows('tables')-1
let sheetname = purgeChar(peek('TABLE_NAME', i, 'tables'), '$');
data:
CrossTable(Month, Quantity, 2) // Hear control the number of Qualifier fields - i.e. 2 means, Salesman and Year are qualifier fields and rest are the crosstable
LOAD Salesman,
$(sheetname) as Year, // Here you call the sheetname as Year
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM '$(file)' (ooxml, embedded labels, table is $(sheetname));
next i
drop table tables;
next file
Regards,
Sajeevan
I don't have words thank you, it worked fine.
Now Sir, actually i'm loading the table as:
Tab1:
Crosstable(A,B,7) Load col1,col2........col9, col10,col11 from excel
Tab2:
Crosstable(C,D) Load col1,col2,col3 from excel
Since i've two crosstable, i'vetwo questions
1) How to join these two crosstables in the script to have one table (QV doesn't allow me to directly use the Join prefix before Tab2).
2) How to modify the script you suggested for this scenario of two Crosstables
Regards
Sir,
Ques. 2 is solved
I just copied & pasted the script for Tab2, & its has fetched the rows but the problem lies with the $(sheetname) as Year, if i use it again in this script it generates a synthetic key (Year+the common Col1).
Is there a way to avoid this,still linking this table data with Year.
& yes the general problem 1 remains to be solved.
Regards
Can you attach the screenshot of your table. Press Ctrl+T to go to the table layout.
You can join the two tables by
Fianl_Table:
Load * Resident Tab1;
concatenate
Load * Resident Tab2;
Drop Table Tab1;
Drop Table Tab2;
Regards,
Sajeevan
Dear Sir,
When i do this then due to drop statements Tab1 & Tab2 are lost & also Final_Tab is also not there in the Layout Diag, thus all the expressions are lost
Regards
Then, can you attach a screenshot of your original table structure and loading statements?
Ok,
here are the diags before & after the resident
I can't find the picture. I need the screenshot of table structure before combining the resident tables. Use the advanced editor and attach the picture or paste the picture.