Parsing String Fields

[Anonymous]

Hello

I am bringing a field called Textline2 with the following string structure

AT CSICU ON 05/30/15 AT 1141 BY TestUser.

I would like to parse the above string into the following columns

Location          Date                      Time              User

CSICU            5/50/15                   1141               TestUser

I have tried using the subfield function but my results are parsing as follows

String

AT CSICU ON 05/30/15 AT 1141 BY TestUser

.

Code                                                                                                 My Result

   subfield(TextLine2, 'AT', 2 ) AS Location,                                             CSICU ON 05/30/15

   subfield(TextLine2, 'ON', 2 ) AS Date                                                   05/30/15 AT 1141 BY TestUser

    subfield(TextLine2, 'AT', 3 ) AS Time,                                                   1141 BY TestUser

Please help.

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD Trim(TextBetween(F1, 'AT', 'ON')) as Location,

Trim(TextBetween(F1, 'ON', 'AT')) as Date,

Trim(SubField(Mid(F1, Index(F1, 'AT', 2) + 2), 'BY', 1)) as Time,

Trim(SubField(F1, 'BY', -1)) as User;

LOAD * INLINE [

    F1

    AT CSICU ON 05/30/15 AT 1141 BY TestUser

];

[antoniotiman]

Hi Salamon,

maybe this

LOAD
SubField(Field,' ',2) as Location,
SubField(Field,' ',4) as Date,
SubField(Field,' ',6) as Time,
SubField(Field,' ',-1) as User
Inline [ Field
AT CSICU ON 05/30/15 AT 1141 BY TestUser
];

Regards,

Antonio

[its_anandrjs]

Try wit subfield function here with combination

LOAD *,
SubField(Str,' ',1) as Location,
SubField(Str,' ',2) as 2,
SubField(Str,' ',3) as 3,
SubField(Str,' ',4) as Date,
SubField(Str,' ',5) as 5,
SubField(Str,' ',6) as Time,
SubField(Str,' ',7) as 7,
SubField(Str,' ',8) as 8;
LOAD Str
FROM
C:\Users\Home\Desktop\Qcomm\StrTable.xlsx
(ooxml, embedded labels, table is Sheet1);

[kuczynska]

You  can try a combination of subfield & replace functions too:

test:

load * Inline [

Textline

AT CSICU ON 05/30/15 AT 1141 BY TestUser

];

test1:

Load

updated_text,

subfield(updated_text, '//', 2) as location,

    subfield(updated_text, '//', 3) as date,

    subfield(updated_text, '//', 4) as time,

    subfield(updated_text, '//', 5) as user

;

Load

replace(replace( replace(Textline, 'AT', '//') , 'ON', '//'), 'BY' , '//') as updated_text

resident test;

[brunobertels]

Hi

not sure of my approche and not tested but

Try with space character as delimiter :

subfield(TextLine2,' ',2) as Location,

subfield (TextLine2,' ',3) as time ,

subfield(textline2,' ',4) as Date,

etc

[its_anandrjs]

Finally as your requirement load this ways

LOAD *,
Second&' '&Third&' '&Date as LocationFinal,
Date &' '& Location&' '&Time &' '&Seventh &' '& Eight as DateFinal,
Time &' '& Eight as TimeFinal;


LOAD *,
SubField(Str,' ',1) as Location,
SubField(Str,' ',2) as Second,
SubField(Str,' ',3) as Third,
SubField(Str,' ',4) as Date,
SubField(Str,' ',5) as Fifth,
SubField(Str,' ',6) as Time,
SubField(Str,' ',7) as Seventh,
SubField(Str,' ',8) as Eight;
LOAD Str
FROM
C:\Users\Home\Desktop\Qcomm\StrTable.xlsx
(ooxml, embedded labels, table is Sheet1);

[antoniotiman]

Sunny,

good idea.

However for Time Field You can use

TextBetween(Field,'AT ',' BY',2) as Time,

[sunny_talwar]

I tried that, but for some reason it did not work for me... Did it work for you?

[antoniotiman]

Yes,Sunny.

(You can add Trim)

LOAD
TextBetween(Field,'AT ',' ON') as Location,
TextBetween(Field,'ON ',' AT') as Date,
TextBetween(Field,'AT ',' BY',2) as Time,
SubField(Field,'BY',-1) as User
Inline [ Field
AT CSICU ON 05/30/15 AT 1141 BY TestUser
];