script error

[#1]

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;

[jerem1234 · Accepted solution]

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!!

[settu_periasamy]

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;

[jagan]

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.

[#5]

Thanks Jeremiah! This is what I need!