5 Replies Latest reply: Apr 22, 2014 10:46 AM by nard elsman RSS

    question about formatting my excel data

    nard elsman

      Hello,

       

      I have a problem with my datasource.

       

      I want to import data from an excel dat i get delivered from my clients, this excel is an export from some program.

      I do not have the possibility to change the export so i want to format it while loading the tables.

       

      As you can see in the attachment, sheet: Problem. It is some strange form of reporting.

      I have tried formatting the data by using a For loop to create a correct table.

      sadly this did not work.

       

      In the sheet: Result is shown how i would like to see the data imported.

       

      Does anyone know how i can perform this?

       

      I appreciate any answer.

        • Re: question about formatting my excel data
          Alessandro Saccone

          Look at this syntax, I never used it directly but I know it works ...

           

          Hope it helps

           

          LOAD [Server Name],

               Date,

               Time,

               User,

               Program,

               [Security Audit Log message text]

          FROM

          [C:\Users\.....\Documents\Book2_tres.xlsx]

          (ooxml, embedded labels, table is Sheet1, filters(

          Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

          ));

          • Re: question about formatting my excel data
            Bert Dijks

            This should do the trick:

            This code is generated by the import wizard  --> transformatiion steps:

             

            Problemdata:

            LOAD Artikel as Date,
               
            Quantity,
               
            [Price/each],
               
            F8 As Artikel
            FROM
            [Problem excel.xls]
            (
            biff, embedded labels, header is 1 lines, table is Problem$, filters(
            ColXtr(1, RowCnd(Compound,
            RowCnd(CellValue, 1, StrCnd(contain, '0', not)),
            RowCnd(CellValue, 1, StrCnd(start, 'Artikel', not))
            ), 0),
            Replace(8, top, StrCnd(null)),
            Remove(Row, RowCnd(Compound,
            RowCnd(CellValue, 1, StrCnd(contain, '0', not)),
            RowCnd(CellValue, 1, StrCnd(contain, 'Artikel', not))
            ))
            ));

            • Re: question about formatting my excel data
              Patrick Tabin

              Got a different script from the wizard than Bert's -

               

              LOAD Artikel as Date,

                   Quantity,

                   [Price/each],

                   F8 as Artikel

              FROM

              [C:\Temp\Problem excel.xls]

              (biff, embedded labels, header is 1 lines, table is Problem$, filters(

              ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),

              Replace(8, top, StrCnd(null)),

              Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),

              Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Total'))),

              Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Totaal'))),

              Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

              Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Overall Total')))

              ));

               

              HTH!