9 Replies Latest reply: Sep 28, 2018 5:40 AM by SK B RSS

    Loading data from Excel from Specific Column

    SK B

      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

        • Re: Loading data from Excel from Specific Column
          arvind pednekar

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

          • Re: Loading data from Excel from Specific Column
            arvind pednekar

            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

            [C:\Users\Arvind\Downloads\Sample Data1.xls]

            (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

            • Re: Loading data from Excel from Specific Column
              arvind pednekar

              This is output in front end as table object:

                • Re: Loading data from Excel from Specific Column
                  SK B

                  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

                    • Re: Loading data from Excel from Specific Column
                      arvind pednekar

                      Tell me

                      the dynamic column will be for upcoming months only right?

                        • Re: Loading data from Excel from Specific Column
                          SK B

                          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

                            • Re: Loading data from Excel from Specific Column
                              arvind pednekar

                              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

                              [C:\Users\Arvind\Downloads\Sample Data1.xls]

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

                                • Re: Loading data from Excel from Specific Column
                                  SK B

                                  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]