5 Replies Latest reply: Dec 12, 2016 10:57 AM by Jozsef Peitli RSS

    Field to Record

    Jozsef Peitli

      Dear All,

      I have .txt files. A lot.

      Each has a lot of record.

      Each record has 3+28 field I needed.

      I would like that the 28 field became 28 record.

      How?

       

      Thank you for your kind reply!

       

      BR.

      Jozsef

        • Re: Field to Record
          Daniel Ansell

          Hi

           

          I think you need to use the crosstable function.

           

          Test:

          CrossTable(Header, Data, 2)

          LOAD

              @5 as Date,

              @6 as Field1,

              @28,

              @29,

              @30,

              @31,

              @32,

              @33,

              @34,

              @35,

              @36,

              @37,

              @38,

              @39,

              @40,

              @41,

              @42,

              @43,

              @44,

              @45,

              @46,

              @47,

              @48,

              @49,

              @50,

              @51,

              @52,

              @53,

              @54,

              @55,

              @56

          FROM

          [..Downloads\source.txt] (replace with your file location)

          (txt, utf8, no labels, delimiter is ';', msq);

          Drop field Header;

            • Re: Field to Record
              RAjesh Bhardwaj

              Hi

               

              I agree. The crosstable function will be the most effective to the results you need.

               

              Adopt the method used by Daniel and you should get th results with a bit of trial and error.

              • Re: Field to Record
                Jozsef Peitli

                Dear Daniel,

                 

                Just what I needed! Thank you very much!

                 

                 

                BR.

                Jozsi

                • Re: Field to Record
                  Jozsef Peitli

                  Dear !Dan,

                   

                  Could you please help me a little bit?

                  What if I need a bit different result, something like This:

                  10/24/2016 14:19:47;   0303F8400660; CF5595001; A; 1;

                  10/24/2016 14:19:47;   0303F8400660; CF4895001; A; 2;

                  10/24/2016 14:19:47;   0303F8400660; CF4195001; A; 3;

                  10/24/2016 14:19:47;   0303F8400660; CF3495001; A; 4;

                  10/24/2016 14:19:47;   0303F8400660; CF5495001; A; 5;

                  10/24/2016 14:19:47;   0303F8400660; CF4795001; A; 6;

                  10/24/2016 14:19:47;   0303F8400660; CF4095001; A; 7;

                  10/24/2016 14:19:47;   0303F8400660; CF3395001; B; 1;

                  10/24/2016 14:19:47;   0303F8400660; CF5395001; B; 2;

                  10/24/2016 14:19:47;   0303F8400660; CF4695001; B; 3;

                  10/24/2016 14:19:47;   0303F8400660; CF3995001; B; 4;

                  10/24/2016 14:19:47;   0303F8400660; CF3295001; B; 5;

                  10/24/2016 14:19:47;   0303F8400660; CF5295001; B; 6;

                  10/24/2016 14:19:47;   0303F8400660; CF4595001; B; 7;

                  10/24/2016 14:19:47;   0303F8400660; CF3895001; C; 1;

                  10/24/2016 14:19:47;   0303F8400660; CF3195001; C; 2;

                  10/24/2016 14:19:47;   0303F8400660; CF5195001; C; 3;

                  10/24/2016 14:19:47;   0303F8400660; CF4495001; C; 4;

                  10/24/2016 14:19:47;   0303F8400660; CF3795001; C; 5;

                  10/24/2016 14:19:47;   0303F8400660; CF3095001; C; 6;

                  10/24/2016 14:19:47;   0303F8400660; CF5095001; C; 7;

                  10/24/2016 14:19:47;   0303F8400660; CF4395001; D; 1;

                  10/24/2016 14:19:47;   0303F8400660; CF3695001; D; 2;

                  10/24/2016 14:19:47;   0303F8400660; CF2995001; D; 3;

                  10/24/2016 14:19:47;   0303F8400660; CF4995001; D; 4;

                  10/24/2016 14:19:47;   0303F8400660; CF4295001; D; 5;

                  10/24/2016 14:19:47;   0303F8400660; CF3595001; D; 6;

                  10/24/2016 14:19:47;   0303F8400660; CF2895001; D; 7;

                   

                  But the source is the same as was last time.

                  The new part is the last two field. It isn't on the source file.

                  There is any possibilities I sort it out during the load phase or I have to prepare the data structures before I load it?

                   

                  I hope it is clear.

                   

                  Thank you for your help!