4 Replies Latest reply: Jun 6, 2017 6:19 AM by Marcus Sommer RSS

    Looping through excel rows..

    sravan Vennu

      Hi All,

       

      I am trying to pull table structure from excel, instead of mentioning each field name at the Script level.

      To avoid changes at script level, if any new fields add in to the table.

       

      table structure in excel: In the below table 1st column will be field name and 2nd will be Alias name.

       

      MetricID,MetricID,
      MetricName,    MetricName,
      MetricCode,    MetricCode,
      Quadrants,    Quadrants,
      Cellformat,    Cellformat,
      OrderID    OrderID

       

      .Script:

       

      Set vQVD='\\..\QVD\';

       

      refMetric_Fields:
      LOAD FieldName,
      AsName
      FROM
      [\\...\Excel Tables.xls]
      (
      biff, embedded labels, table is tbl_BI_Fact$);

      LET vTableCount = NoOfRows('refMetric_Fields');

      For vi =0 to $(vTableCount)-1
      LET vFieldName = Peek('FieldName',$(vi),'refMetric_Fields');
      LET vAsName = Peek('AsName',$(vi),'refMetric_Fields');

      refMetric:
      LOAD
      $(vFieldName) as $(vAsName)
      FROM $(vQVD)D_TABLE_Metric.qvd
      (
      qvd);

      NEXT

      DROP Table refMetric_Fields;

       

      which is giving me below error....

      Syntax error, missing/misplaced FROM:

      refMetric:

       

      Please help me with the issue.

       

      Thanks,

       

      Regards,

      Sravan.

        • Re: Looping through excel rows..
          Silambarasan Manickam

          Hi,

           

          Can you please share the source file.

           

          or else you can refer the below code.

           

          SheetNameFile:

          LOAD

              SheetName,

              FileName

          FROM [lib://Excel File/SheetNameFile.xlsx] (ooxml, embedded labels, table is Sheet1);

           

          For i = 0 to NoOfRows('SheetNameFile')-1

           

           

                 LET vExcelSheets = PEEK('SheetName',i,'SheetNameFile');

                 LET vFileName = PEEK('FileName',i,'SheetNameFile');

                

          Target_Temp:

          LOAD

                *

          FROM [lib://Excel File/$(vFileName).xlsx]

          (ooxml, embedded labels, table is '$(vExcelSheets)');

           

           

          NEXT i

          Drop Table SheetNameFile;

            • Re: Looping through excel rows..
              sravan Vennu

              Hi,

               

              Thanks for replay..

              Please find sample data format.

               

              Metric table:

                 

              MetricIDMetricNameMetricCodeQuadrant
              E123AS123People
              B345DF234People
              C456FG345Finace
              D567HJ456HR

               

              I have multiple tables like above, with field names and corresponding values.

              Some times the field names has to replace and there will be scenarios where new fields will be added to table.

               

              So, instead of loading like below..

               

              LOAD MetricID,
              MetricName,
              MetricCode as MetricID,
              Quadrant
              FROM
              [\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
              (
              ooxml, embedded labels, table is Fact);

               

              Want to take field names in excel and loop through the rows and save the field names.

               

                 

              FieldNameAsName
              MetricIDMetricID
              MetricNameMetricName
              MetricCodeMetricID//For example
              QuadrantQuadrant

               

              Regards,

              Sravan.

                • Re: Looping through excel rows..
                  Silambarasan Manickam

                  Hi,

                   

                  Instead of taking like below,

                   

                  LOAD MetricID,
                  MetricName,
                  MetricCode as MetricID,
                  Quadrant
                  FROM
                  [\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
                  (
                  ooxml, embedded labels, table is Fact);


                  you can use,

                  LOAD *

                  [\\svrin000mbp06.global.anz.com\vs3$\Desktop\Sample Data.xlsx]
                  (
                  ooxml, embedded labels, table is Fact);


                  If you want the exact query, better you can create a sample data excel file and share it.

              • Re: Looping through excel rows..
                Marcus Sommer

                Here you will find various methods to create such a load-statement:

                 

                Re: Interesting challenge: How to create a real table from a list of field names?

                 

                - Marcus