Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I've been trying for a while now to loop through a list of sheets (JAN, FEB, ... , NOV, DEC), but I'm having issues loading more than one sheet. The following works, but as soon as I replace 'JAN' for 'JAN, FEB' I get the error 'Unknown file format specifier: FEB'. Is there a syntax nuance that I need to know or am I way off?
SET i = JAN;
FOR Each i in i;
LOAD
A,
B
FROM
[DOC.xlsx]
(ooxml, embedded labels, Table is $(i));
NEXT
Try:
SET i = 'Jan','Feb','Mar';
for each m in $(i);
LOAD A, B
FROM [DOC.xlsx] (ooxml, embedded labels, Table is [$(m)]);
next
ABC:
A,
B
FROM
[Table.xlsx]
(ooxml, embedded labels, table is Sheet1);
Hi Sasi,
How is this meant to loop through multiple sheets and load the data?
Try:
SET i = 'Jan','Feb','Mar';
for each m in $(i);
LOAD A, B
FROM [DOC.xlsx] (ooxml, embedded labels, Table is [$(m)]);
next
Hi Mathew,
See the below code.
The Inline table need not to contain all ur excel fields.
Just have 1 or 2 fields.
TestTable:
LOAD [
YourSampleFieldName, YourSampleFieldName1
];
LET vDataFolder= '..\YourDataPath\';
FOR EACH vFile in filelist('$(vDataFolder)*.xlsx');
let vFileName = SubField(vFile,'.',1);
let vFileName = mid(vFileName, index(vFileName, '\', -1) + 1);
//Connection to Excel Files
OLEDB CONNECT32 To [Excel Files;DBQ=$(vFile)];
//Read & Store the Sheets in Buffer
Temp_tables:
sqltables;
for iSheet = 0 to NoOfRows('SheetName') - 1
for iSheet = 0 to NoOfRows('Temp_tables')-1
let vSheetName = Peek('TABLE_NAME', iSheet, 'Temp_tables');
Let vSheetName=Replace(vSheetName,'$','');
Let vSheetName=Replace(vSheetName,Chr(39),'');
Concatenate(TestTable)
LOAD *
FROM
[$(vFile)]
(ooxml, embedded labels, table is [$(vSheetName)]);
DROP Table Temp_tables;
NEXT vFile;
DISCONNECT;
Thanks,
You beauty! This is perfect. I didn't need the '' around each i value, but the code is great. Cheers mate!
One question, with my variable file name I was able to pull in text to my columns. Do you know a way I can easily create a column that records 'JAN' in all the rows from JAN, 'FEB' in all the rows from FEB, ... etc. using this method?
Hi Parthiban,
I'm sure this works, but I asked for a solution that didn't use an ODBC connection.
Try
LOAD aaa, bbb, '$(m)' as ccc
FROM ...