Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can you help me with the below script? I tried to run it with multiple spreadsheets, each spreadsheet would contain similar data with Supplier (Vendor) and Type in the first 2 columns but with different date for different FY.
The script runs .... but I get the below script error.
Thank you
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
directory;
FOR EACH vFile in FileList('D:\Users\d642527\Desktop\Example\*.xlsx');
ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1
Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);
//==================================================
FY14:
CrossTable(Date, Data, 3)
LOAD
'$(vSheet)' as [Tab Name],
Supplier,
Type,
[41821],
[41852]
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));
Next;
DROP Table Sheets;
Next;
Final:
LOAD [Tab Name],
Supplier,
Type as [Project Type],
Date(Num#(Date)) AS Reporting_Date,
Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
Data
Resident FY14;
Drop Table FY14;
//==========================================================
FY15:
CrossTable(Date, Data, 3)
LOAD
'$(vSheet)' as [Tab Name],
Supplier,
Type,
[42186],
[42217]
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));
Next;
DROP Table Sheets;
Next;
Final:
LOAD [Tab Name],
Supplier,
Type as [Project Type],
Date(Num#(Date)) AS Reporting_Date,
Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
Data
Resident FY15;
Drop Table FY15;
So your problem is that you don't want to hardcode the Date Fields (ex: 41821 and 41852) since they may vary for each file? Have you tried adding a '*' in your load to load in all fields?
For example:
CrossTable(Date, Data, 3)
LOAD
'$(vSheet)' as [Tab Name],
*
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));
Hope this helps!!
So your problem is that you don't want to hardcode the Date Fields (ex: 41821 and 41852) since they may vary for each file? Have you tried adding a '*' in your load to load in all fields?
For example:
CrossTable(Date, Data, 3)
LOAD
'$(vSheet)' as [Tab Name],
*
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));
Hope this helps!!
Hi,
Based on your Previous thread sample data Re: How to load EXCEL worksheets and retrieve tab names
You can try the below script for Multiple sheet with Multiple Headers
Updated:
DIRECTORY;
For Each vFile in FileList('FY1314_Report.xlsx')
ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1
Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);
Temp:
CrossTable(Date, Data, 3)
LOAD '$(vSheet)' as [Tab Name],*
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet)) Where not IsNull(VENDOR);
Next;
DROP Table Sheets;
Next;
Final:
LOAD [Tab Name],
VENDOR,
TYPE as [Project Type],
Date(Num#(Date)) AS Reporting_Date,
Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
Data
Resident Temp;
Drop Table Temp;
Hi,
Try like this, instead of columns just put *
CrossTable(Date, Data, 3)
LOAD
'$(vSheet)' as [Tab Name],
*
FROM
[$(vFile)]
(ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));
Next;
DROP Table Sheets;
Next;
Regards,
Jagan.
Thanks Jeremiah! This is what I need!