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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.