How to handle file delimiter present in field value

[#1]

Hi All,

I have a comma separated file with values as:

CustID, Order_id,Quantity, Status

10,ORD1,50,0

11,ORD1,ORD2,60,1

12,ORD2,70,-1

Here, Order_id has a value "ORD1,ORD2" for the 2nd record.

When i load the data directly as comma separated and check the output in a Table Box,

For the 2nd record, Order_id has value ORD1 and Quantity has value ORD2, and Status has value 60, while the other 2 records are fine as there is no comma delimiter in any field value.

What is the workaround if i have to get the values as follows for the 2nd record:

CustID 11

Order_id ORD1,ORD2

Quantity 60

Status 1

Regards,

Mahi

[maxgro]

Directory;

Table:

LOAD

  @1 as Source,

  if(SubStringCount(@1, ',')=4, 1,0) as Flag

FROM file.txt

(txt, codepage is 1252, no labels, delimiter is ';', msq, header is 1 lines);

FinalTable:

Load

  SubField(Source,',',1) as CustID,

  SubField(Source,',',2) as Order_id,

  SubField(Source,',',3) as Quantity,

  SubField(Source,',',4) as Status

Resident Table

Where Flag=0;

Concatenate (FinalTable)

Load

  SubField(Source,',',1) as CustID,

  SubField(Source,',',2) & ',' & SubField(Source,',',3) as Order_id,

  SubField(Source,',',4) as Quantity,

  SubField(Source,',',5) as Status

Resident Table

Where Flag=1;

DROP Table Table;

[sasiparupudi1]

Please Try

Data:

Load * Inline

[

Text

"10,ORD1,50,0"

"11,ORD1,ORD2,60,1"

"11,ORD1,ORD2,ORD3,60,1"

"11,ORD1,ORD2,ORD3,ORD4,ORD5,ORD6,60,1"

"12,ORD2,70,-1"

];

let vX=SubField('11,ORD1,ORD2,60,1',',',SubStringCount('11,ORD1,ORD2,60,1',',')+1);

LOAD

SubField(Text,',',1) as CustID,

SubField(Text,',',SubStringCount(Text,',')+1) as Status,

SubField(Text,',',SubStringCount(Text,',')) as Quantity,

TextBetween(Text,SubField(Text,',',1)&',',','&SubField(Text,',',SubStringCount(Text,','))&','&SubField(Text,',',SubStringCount(Text,',')+1)) as OrderID

Resident Data;

drop Table Data;

[#4]

Thanks for the responses.

Regards,

Mahi

[sasiparupudi1]

Please close this thread if you got your answer