Field to Record

[jozsefpeitli]

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

[danansell42 · Accepted solution]

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;

[#3]

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.

[jozsefpeitli]

Dear Daniel,

Just what I needed! Thank you very much!

BR.

Jozsi

[danansell42]

Glad to be of assistance.

Please mark as correct if it answers you question.

This helps other community answers.

Thanks

Dan

[jozsefpeitli]

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!