My question may be stupid, but I am using PARSE for first time..... Can I not use FIXLEN and still use PARSE? I am able to do it with FIXLEN, but that is creating another problem and spacing between two fields is getting messed up now. When I tried removing the FIXLEN, it gave me syntax error.
If at all possible, get the input data rearranged so that the fields which may be bounded by quotes are the last fields. Then it is easy, and you can even use PARSE afterwards to rearrange the data if needed into the original order.
I guess you can't do that, because otherwise they could just pipe-delimit anyway and you'd have nothing to do. There's a plan.
Confirm you can't do that. Also, are the commas necessary in the amount field?
It can probably be done in SORT as is, but with a lot of code.
Good searching Marso. It's actually mentioned in the topic I linked to, but...
With this route the potential presence of multiple embedded blanks add interest. SQZ can protect against those (using a different PAIR=QUOTE/APOST). But in the sample data text fields without commas don't have quotes around them. Back to trickiness.
Need to insert quotes around text fields and FINDREP double-quotes to single quotes, or put apostrophes and have two source values (' and ") in a final FINDREP. Actually, doesn't sound as bad as I was imagining. Still...
Probably (perhaps) the producer could have said "put quotes around all text fields", but they didn't. As they didn't use pipe as the initial delimiter. As they didn't put the fields in an order which would be easier to process.