Excel data load, multiple tabs

[#1]

Hi All,

Wonder if you could help me please as I am struggling with an excel dataload, am sure this is basic stuff but can't figure it out!

When I run the three seperate loads individually they work perfectly well however when I run them together they fetch the correct row count but then hang and force me to terminate the application as it freezes every time?

In this example I have commented out the third load to simplify things but surely that should also work if uncommented as they all have a common field?

Note tab 1 is called Opening and tab 2 is called closing - thanks in advance.

SET

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';


LOAD * FROM



(ooxml, embedded labels, table is Opening);

LOAD * FROM



(ooxml, embedded labels, table is Closing);


//LOAD [DWF Ref],

//     [Date Instructions Received],

//     [Amount of Insured Damages Recovered],

//     [Date Closed],

//     if([Amount of Insured Damages Recovered]>=0,1,0)

//

//FROM

//

//(ooxml, embedded labels, table is Closing)

[sreenivas]

Can you add the excel files data too

[Jason_Michaelides]

Try naming your tables by adding TableName: before the LOAD and give your If() statement an alias using AS RecoveredFlag

Hope this helps,

Jason

[#4]

Can you add the excel files data too -

Sorry I don't understand your question?

[sreenivas]

Add the excel files which you are refering

[#6]

Thanks Jason,

Apologies as I am new to this, what is the correct syntax for table name before the load?

Thanks

[#7]

I can add the excel file in a single data load as specified but can't add the tabs in a multiple load

[#8]

Jason,

I have changed the code to the following and it is still freezing?;

SET

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

TableOpening:
LOAD * FROM



(ooxml, embedded labels, table is Opening);

TableClosing:

LOAD * FROM



(ooxml, embedded labels, table is Closing);


//LOAD [DWF Ref],

//     [Date Instructions Received],

//     [Amount of Insured Damages Recovered],

//     [Date Closed],

//     if([Amount of Insured Damages Recovered]>=0,1,0) as 'No. of Cases Closed with Recovery'

//

//FROM

//

//(ooxml, embedded labels, table is Closing)

[sreenivas]

Goto-->Editscript and debug the code using step by step

I think it is having the same column names in both the tables it may cause the syn tables and break the operation.

In Tableclosing and TableOpening add all the column names instead of "*" and post that script

[#10 · Accepted solution]

paste

qualify *;

before first load

if works fine send us printscreen of structure from tableview