Loading data from Excel from Specific Column

[kicchu465]

Hi Experts,

I have a problem in loading the data from Excel.

My requirement is to load from Excel from specific cell no. The Cell no is I19 to so on.

I have attached sample data and the load script I have used but no luck.

Could you please help me in getting this done.

I:

LOAD *

FROM [Sample Data1]

(biff, embedded labels,header is 19 lines, table is Table$)

Thanks,

S k

[arvind1494]

I your excel sheet their are no of tables which table do you want to load from cell no 19 ?

[kicchu465]

Hi Arvind,

I need it from Cell no i19.

I want to eliminate Columns A to H and Rows 1 to 18.

Thanks,

S K

[arvind1494]

LOAD 

     F9,

     [Cost Center],

     F11,

     [Cost Element],

     F13,

     [WBS Element],

     F15,

     [Internal Order],

     F17,

     [APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000]   

FROM

(biff, embedded labels, header is 8 lines, table is Table$);

Note: in your data for cell no 19 few values are null there for those columns are showing F9,F11,F13 and so on

[arvind1494]

This is output in front end as table object:

[kicchu465]

Hi Aravind,

Thanks for your reply.

The excel will be updated month on Month and therefore every month I'll be getting extra column after

[AUG 18 Actuals

in '000]

for example when data gets refresh  this month there will be on more column called

[SEP18 Actuals

in '000]

So the Script should be dynamic to pick those columns as well. Is that achievable?

Thanks,

S k

[arvind1494]

Tell me

the dynamic column will be for upcoming months only right?

[kicchu465]

Hi Arvind,

Yes.

The data will be updated till Mar'19 and in then we will receive next file for next financial year.

In the data file the below will static and there wont be any changes in column position.

F9,

     [Cost Center],

     F11,

     [Cost Element],

     F13,

     [WBS Element],

     F15,

     [Internal Order],

     F17

The rest of fields should be picked dynamically.

Thanks,

S k

[arvind1494]

let vThisMonth = num(month(Today()));

SET vFixCol =F9,

     [Cost Center],

     F11,

     [Cost Element],

     F13,

     [WBS Element],

     F15,

     [Internal Order],

     F17;

set vApr = [APR 18 Actuals

in '000];

set vMay = [APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000];

set vJun =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000];

set vJul =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000];

set vAug =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000];

set vSep =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000];

set vOct =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000];

set vNov =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000];

set vDec =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000],

[DEC 18 Actuals

in '000];

set vJan =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000],

[DEC 18 Actuals

in '000],

[JAN 19 Actuals

in '000];

set vFeb =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000],

[DEC 18 Actuals

in '000],

[JAN 19 Actuals

in '000],

[FEB 19 Actuals

in '000];

set vMar =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000],

[DEC 18 Actuals

in '000],

[JAN 19 Actuals

in '000],

[FEB 19 Actuals

in '000],

[MAR 19 Actuals

in '000];

TRACE $(vThisMonth);

let vMonthName= 'v'&month(Today());

TRACE $(vMonthName);

//Load statement

LOAD

     $(vFixCol),

     $($(vMonthName))

FROM

(biff, embedded labels, header is 8 lines, table is Table$);

[kicchu465]

Hi Arvind,

Excellent solution.

I am trying to below,but I am facing an issue.

Table:

CrossTable (Month, Measure,9)

LOAD  

     F9 as [Cost Description],

     [Cost Center],

     F11 as [Cost Center Description],

     [Cost Element],

     F13 as [Cost Element Description],

     [WBS Element],

     F15 as [WBS Element Name],

     [Internal Order],

     F17 as [Internal Order Text]