topic Re: Load file with delimiters? in App Development

Load file with delimiters?

Vasiliy_Beshentsev — Thu, 19 Jan 2017 08:51:37 GMT

Hello everyone!

I Need to load file with delimiter comma (',') until i get word "Context=" and then i need to get all after "Context=..." into last field?

How to do that?

Re: Load file with delimiters?

marcus_sommer — Thu, 19 Jan 2017 08:54:19 GMT

I think some sample-records of this text-file would be useful.

- Marcus

Re: Load file with delimiters?

Vasiliy_Beshentsev — Thu, 19 Jan 2017 09:04:52 GMT

Here, please

Re: Load file with delimiters?

marcus_sommer — Thu, 19 Jan 2017 09:19:04 GMT

The first record is quite different to the following records - this meant you might need several load-steps to the header and the records and to match them in any way. Here a suggestion how the following records could be loaded:

records:

LOAD subfield([@1:n], ':', 1) as F1, subfield([@1:n], ':', 2) as F2, subfield([@1:n], ':', 3) as F3

FROM [test.txt] (fix, codepage is 1252, header is 1 lines);

In a similar way (with subfield() and/or other string-functions) would it be possible to load also the first record.

- Marcus

Re: Load file with delimiters?

Vasiliy_Beshentsev — Thu, 19 Jan 2017 11:02:55 GMT

So now i need to update last field with Context=

How to add data to this field?

Re: Load file with delimiters?

marcus_sommer — Thu, 19 Jan 2017 11:10:12 GMT

Based on your sample records - how should be the output-table look like?

- Marcus

Re: Load file with delimiters?

Vasiliy_Beshentsev — Thu, 19 Jan 2017 11:16:58 GMT

I need to get this,

Last field must be "Context=words words words"

Re: Load file with delimiters?

marcus_sommer — Thu, 19 Jan 2017 13:28:05 GMT

Regarding to your sample-data would be something like this possible:

Map_Extra:

mapping load filebasename() as FilebaseName, concat([@1:n], ' ') as Value

From [test.txt] (fix, codepage is 1252, header is 1 lines);

Data:

first 1 LOAD

@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13,

@14 & applymap('Map_Extra', filebasename(), '#NV') as @14

FROM [test.txt] (txt, codepage is 1252, no labels, delimiter is ',');

- Marcus