7 Replies Latest reply: Jan 4, 2017 11:01 AM by Marcus Sommer RSS

    Load 1st row as Header Product name and second row as Data header + Data

    ANDRE Bisson

      Hello,

      I have file which look like this

       

      Product Name1

      SP1    SP2    SP3 ...

      100     150     215                   

       

      Product Name2

      SP1    SP2    SP3 ...

      215     174     157    

       

      The goal is to load SPx data and associate it to the product name, so it should look like this

                                 SP1    SP2    SP3 ... 

      Product Name1     100     150     215 ...

      Product Name2     215     174     157 ...

       

      I am capable to load all SPx data but cannot associate it the Product Name which 1 row above.

       

      Also to complicate a bit, I need to load multiple txt file because each day a file is created with the product built in that day.

       

      Your help will be appreciated.

       

      Andre

        • Re: Load 1st row as Header Product name and second row as Data header + Data
          Marcus Sommer

          I think I would use a loop which loads only those record which have a content and would transform them with The Crosstable Load. I mean something like this:

           

          Counter:

          LOAD [@1:1]

          FROM [..\Community\Dec 02 2016 data.txt] (fix, utf8);

           

          for i = 7 to noofrows('Counter') step 8

              table:

              first 1 load * FROM

              [..\Dec 02 2016 data.txt] (txt, utf8, embedded labels, delimiter is '\t', header is $(i) lines);

          next

           

          final:

          crosstable(Category, Value, 2) load * resident table;

           

          drop table Counter, table;

           

          - Marcus

            • Re: Load 1st row as Header Product name and second row as Data header + Data
              ANDRE Bisson

              Hello Marcus,

               

              Thanks for the quick answer, but it does not provide the expected result.

              Maybe I did not well explain my problem.

               

              The file contain a lot of line that will be populated each 5 min with equipment setting. The data is regroup in consequetive 3 row and is explain below:

               

              Product name  is on 1st row --> only information on this row

              Data Header  is on 2nd row --> Multiple data header more than 50

              Actual Data is on 3rd row --> Multiple data

               

              What I will like to achieve is to associate the 1st row with row 2 and row 3 and so forth for each other group.

              The end result should look like:

               

              Product Name (from row 1) --> Data Header 1 (from row 2)          Data Header 2 (from row 2) …etc

              Product Name (from row 1) --> Data 1 (from row 3)                          Data 2 (from row 3) … etc

               

              And perform same action on all other set of 3 row.

               

               

              PS (maybe first row of each file is blank, so if start at row 2 than the group will 2,3 and 4)

              PS2, linking row 2 and 3 is easy but I don’t know how to link Row1 with 2 and 3

              Bonne journée / Best Regards,

               

              André Bisson <mailto:%20andre.bisson@miranda.com> | PCBA Process/ Project Specialist - Manufacturing Engineering

              Grass Valley, A Belden Brand<http://new.grassvalley.com/> | Cel: (438) 998-2177 - Tel: (514) 333-1772 Ext: 3020

              3499 Douglas-B.-Floreani, Montreal, Quebec, Canada, H4S 2C6