subfield unequal string length

[Michiel_QV_Fan]

Hi all,

I have a log file from SAP load and want to extract the number of lines fetched form the log file (txt).

I tried these functions or combinations:

if(@26:n like '*lines fetched*', 'lines fetched', '') as find_lines,
subfield(subfield(@26:n, ','), 'lines fetched') as text_parts,
subfield(@26:n, 'lines fetched') as bla,
textbetween(@26:n, ',' , 'lines fetched') as lines,
substringcount(@26:n, ',') as [, count],
subfield(@26:n, ',', substringcount(@26:n, ',')) as lines?,
index(@26:n, ',') as index,

none delivered the last part of the log file sentence which contains "<number> lines fetched"

The text looks like this:

4 fields found: DIMID, SID_0CHNGID, SID_0RECORDTP, SID_0REQUID, 36 lines fetched

or like this

1 fields found: Dimensionloaded, 0 lines fetched

or .....

How can I extract the last part of each sentence? Your help is much appreciated.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

The following should work

Lines:LOAD KeepChar(Right(@1:n, Len(@1:n) - Index(@1:n, ', ', -1) +1), '0123456789') AS LinesFROM File.csv(fix, codepage is 1252);

Hope that helps.

[rbecher]

You could use a stacked LOAD with a where clause:

LOAD * WHERE sfield like '*lines fetched";
LOAD ... subfield(subfield(@26:n, ',') as sfield ...;

- Ralf

[Michiel_QV_Fan]

Works great. Thanks!

[Michiel_QV_Fan]

Ralf, this statement doesn't work.

Subfield needs more parameters is the QV message.

my final row is grey despite that the subfield coding has the correct colors.

syntax check gives an error on 'as sfield'

the code:

copa2:
load * where beschrijving like '*lines fetched';
LOAD @1:10 as datum,
@11:19 as tijd,
@20:20 as :,
@21:25 as regel,
@26:n as beschrijving,
subfield(subfield(@26:n, ',') as sfield,
FileName() as logbestand
FROM

error: Error in expression: ')' expected

added )

copa2:
load * where beschrijving like '*lines fetched';
LOAD @1:10 as datum,
@11:19 as tijd,
@20:20 as :,
@21:25 as regel,
@26:n as beschrijving,
subfield(subfield(@26:n, ',')) as sfield,
FileName() as logbestand
FROM

error: Error in expression: SubField takes 2-3 parameters

[rbecher]

Sorry, one subfield too much!

LOAD * WHERE sfield like '*lines fetched";
LOAD ... subfield(@26:n, ',') as sfield ...;

- Ralf