Multiple tabs of excel files

LET vFilePath = ‘C:\Users\Sudhiksha\Desktop\New folder\DATA.xlsx’;
ODBC CONNECT32 TO [Excel Files;DBQ=$(vFilePath)];
tables:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows(‘tables’)-1
LET sheetName = purgeChar(purgeChar(peek(‘TABLE_NAME’, i, ‘tables’), chr(39)), chr(36));
Temp:
CrossTable(Month, Value, 3)
LOAD *
FROM $(vFilePath)
(ooxml, embedded labels, header is 1 lines, table is $(sheetName));
Data:
LOAD
Employee,
Country,
Branch,
MonthName(Left(Month, 5)) AS Month,
Value,
If(Len(Month) = 5, ‘Salary’,
If(Len(Month) = 6 AND Right(Month, 1) = 1, ‘Comm’,
If(Len(Month) = 6 AND Right(Month, 1) = 2, ‘Sales’))) AS ValueType
RESIDENT Temp;
DROP TABLE Temp;
Next
EXIT Script;

Advertisements