Cntd...from #747675 How to split a single row

[senarath]

Hi,

Further to above thread, I'm trying to load data from a file and does not work.

It works fine when load inline as below.

Budget:

Load Factory,Customer,Area,Amount,Month,Amount2/DaysInMonth As Amount2, Date(Date#(Month,'MMM-YY') + IterNo() - 1) As Date

While IterNo() <= DaysInMonth;

Load Factory,Customer,Area,Month, Day(MonthEnd(Date#(Month,'MMM-YY'))) As DaysInMonth, Amount, Amount2 Inline [ 

Factory,Customer,Area,Month,Amount, Amount2 

FactoryA,JIT customers,NorthArea,Feb-15,1600.00, 175

FactoryB,Direct customers,NorthArea,Feb-15,1750.00, 140];

DOES NOT upload the data when I tried the same from a file as below. Script completes with no values

Budget:

Load Factory,Customer,Area,Amount,Month,Amount2/DaysInMonth As Amount2, Date(Date#(Month,'MMM-YY') + IterNo() - 1) As Date

While IterNo() <= DaysInMonth;

Load Factory,Customer,Area,Month, Day(MonthEnd(Date#(Month,'MMM-YY'))) As DaysInMonth, Amount, Amount2

FROM

[Budget_L_2015-16.xlsx]

(ooxml, embedded labels, table is HK);

Am I missing something here. Script completes but no data picked up

thanks

[robert_mika]

Could you attached your xlsx file?

[Gysbert_Wassenaar · Accepted solution]

Possibly your excel Month values are not strings, but dates. The date# function wouldn't work then so DaysInMonth becomes a string value and IterNo() <= DaysInMonth will be a number to text comparison with the result that no records will be loaded.

[senarath]

Hi Gysbert,

You are correct. In my excel, date format was set as text and now it's working fine.

Thanks for your expert advise as usual and for your your time.

thanks

Senarath

[senarath]

Hi Robert,

Thanks for th reply. Before upload the file, I tried what Gysbert had mentioned and it worked.

thanks for your time.

Rgds,

Senarath