Need help on string parsing

[#1]

Hi,

I have a input string like below (delimited by "|")

"curr_cd|curr_name|currency_symbol|note_num|status_ind|last_modify_dt|modify_person_num|lock_num|AUD|AUD|||1|20141017035818|ADMIN AdminUser|1|CAD|Canada $|||1|20140417090134|ADMIN AdminUser|1|EUR|EUR|||1|20140507172331|Ravi Paul|1|USC|US Cents|||1|20080421173710|ADMIN AdminUser|1|USD|US Dollars|||1|20140911092721|ADMIN AdminUser|52|ZAR|ZAR|||1|20141117081651|ADMIN AdminUser|30|20141125070440|"

Where strings in the bold are fieldnames.

I want to parse this string into a table.

Can anyone give some help.

Final output will be like below,

[Gysbert_Wassenaar]

In the File Wizard you can choose the Delimiter you need:

The resulting load script should look something like this:

LOAD curr_cd,

     curr_name,

     currency_symbol,

     note_num,

     status_ind,

     last_modify_dt,

     modify_person_num,

     lock_num

FROM

[MySourceFile.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

[#3]

Hi Gysbert,

My problem is that i am not reading data from any txt file.

I am getting this result as some web service using SOAP.

[Gysbert_Wassenaar]

In that case you can try using the subfield function.

[PradeepReddy]

Hi Sachin,

Please find the attached application. It might be helpful.

I have taken an Inline Load, you can change this to the data you are getting  from SAOP

[Anonymous]

Hi

What is the last long (red) number in the string doing, a timestamp for the response?

curr_cd|curr_name|currency_symbol|note_num|status_ind|last_modify_dt|modify_person_num|lock_num|

AUD|AUD|||1|20141017035818|ADMIN AdminUser|1|

CAD|Canada $|||1|20140417090134|ADMIN AdminUser|1|

EUR|EUR|||1|20140507172331|Ravi Paul|1|

USC|US Cents|||1|20080421173710|ADMIN AdminUser|1|

USD|US Dollars|||1|20140911092721|ADMIN AdminUser|52|

ZAR|ZAR|||1|20141117081651|ADMIN AdminUser|30|20141125070440|"

Best regards

Nicolai

[Anonymous]

Beautiful.

I think this does the trick, provided that one concatenated string only is recieved/read at a time.

[MarcoWedel]

Hi,

one generic solution without hard coded field names could be:

tabTemp:
LOAD FieldNo,
    AutoNumber(RowNo, FieldNo) as RecNo,
    FieldVal;
LOAD IterNo() as RowNo,
    Mod(IterNo()-1,8)+1 as FieldNo,
    SubField(string, '|', IterNo()) as FieldVal
Inline [
string
curr_cd|curr_name|currency_symbol|note_num|status_ind|last_modify_dt|modify_person_num|lock_num|AUD|AUD|||1|20141017035818|ADMIN AdminUser|1|CAD|Canada $|||1|20140417090134|ADMIN AdminUser|1|EUR|EUR|||1|20140507172331|Ravi Paul|1|USC|US Cents|||1|20080421173710|ADMIN AdminUser|1|USD|US Dollars|||1|20140911092721|ADMIN AdminUser|52|ZAR|ZAR|||1|20141117081651|ADMIN AdminUser|30|20141125070440|
]
While IterNo()<=SubStringCount(string,'|');

mapFieldNames:
Mapping LOAD FieldNo, FieldVal
Resident tabTemp
Where RecNo=1;

tabOutput:
Generic LOAD
  RecNo,
  FieldNo,
  FieldVal
Resident tabTemp
Where RecNo>1;

DROP Table tabTemp;

RENAME Fields using mapFieldNames;

hope this helps

regards

Marco

[MarcoWedel · Accepted solution]

even shorter:

tabTemp:
LOAD Div(RowNo()-1,8) as RecNo,
    Mod(RowNo()-1,8) as FieldNo,
    SubField(string, '|') as FieldVal
Inline [
string
curr_cd|curr_name|currency_symbol|note_num|status_ind|last_modify_dt|modify_person_num|lock_num|AUD|AUD|||1|20141017035818|ADMIN AdminUser|1|CAD|Canada $|||1|20140417090134|ADMIN AdminUser|1|EUR|EUR|||1|20140507172331|Ravi Paul|1|USC|US Cents|||1|20080421173710|ADMIN AdminUser|1|USD|US Dollars|||1|20140911092721|ADMIN AdminUser|52|ZAR|ZAR|||1|20141117081651|ADMIN AdminUser|30|20141125070440|
];

mapFieldNames:
Mapping LOAD FieldNo, FieldVal
Resident tabTemp
Where not RecNo;

tabOutput:
Generic LOAD
  RecNo,
  FieldNo,
  FieldVal
Resident tabTemp
Where RecNo;

DROP Table tabTemp;

RENAME Fields using mapFieldNames;

hope this helps

regards

Marco

[#10]

Thanks all.

Your valuable inputs really helps me.