I have to import it to mainframe and place every value in its respective fields.
For eg: Here I do have "AL,AR,...,TX". You could see 10 occurences inside the quotes. Even after the quotes you can see another set too like "ED,EE". At times I can receive even 5, but the maximum occurences can be 16. If I get only 5 occurences, its difficult for me to figure out if the next comma belongs to the same variable or to a different variable. I used delimited by comma but the values went to different fields.
Could you please help me with a logic on how I can get the values exactly populated to respective fields. If there are 5 occurences, rest of 11 occurences should be spaces considering the max is 16.
the attachments were deleted,
repost the data using the code tags
please follow Bill's advise to get more options.
my understanding says: i try to avoid reference modifications so see if this is what you want.
step1: use inspect and replace ',"' by '|'
step2 : use another inspect and replace '"' by ''
step3: unstrung delimited by '|' into your 10 fields and use a pointer adnavced by 1 to get data into next occurance .
step4: move each occurance into seaparate variable as you want
My internet got off for some time so could not edit the earlier post.
Step3 : you get the values in 1 variable instead of 10.
Step5:After that you still want to have AL OR AR and so on then perform another unstrung delimited by , into 10 different variable.
What more Help you need, Did you try what I have suggested?
You can show us the sample input and what output you want and use a code tags. You don't have to post the original requirement to us or tell us what do you mean by split the files in above context as you may get dfsort solution as well.
Your output does not match what you say you have done. INSPECT ... REPLACING ... can change character-sequences (not just single bytes), they just have to be the same length. You have "lost" some commas, so it looks like you've discovered that already.
"ED, EE" is data, not two fields. It is data containing a comma, which is why whatever created the CSV in the first place put quotes around it.
You can treat it is two fields. However, you need to be very sure of the source of the data. If it is user-input, then it could just as easily come as "ED,EE", even if that is by typo.
If it is fixed-format and always will have a leading space you could define a three-byte field but only use the last two to pick up the data, or you could use a second delimiter of ", ". Which may be better depends on your code.
Yes Bill. I tried moving the 2 bytes field to a 3 byte field.
Then Check if the first byte is spaces and if its YES I do move the 2nd and 3rd byte only. If first byte is not spaces then move the 1st and 2nd byte only.
its sure that it will have only 2 bytes and not more than that.
Instead of going by my logic which I felt its quite ugly, thought if I could use INSPECT verb to strip the spaces and move only the bytes which have values.
You can still do with inspect. try doing a reverse of a field and tally for leading spaces and the use that vaue +1 for your reference mod as a starting offset.
Do you have a DB2? if yes then instead of all this you can do everything in in one shot using REPLACE function.
1.scan the input field byte by byte and as soon as you hit the '"' then replace ',' by ';' till you hit ending '"' and continue this till the end of the string.
2.Do a inspect and replace ',' by '|'
N|88320|00254XMT03GUIDE|6U|6156|1/1/2001|GLOVEBOX XM C
ANNEL GUIDE|2016||||O|"ED; EE"'.
3. Unstring delimited by '|'
4. Now you will have "ED; EE"' value in one of the variable.
5. Work in this now , use unstring delimited by ';' into 10 variables.
6. You will get "ED in one of the variable from step5, now remove '"' by any method you want ( e.g. use byte by byte check and get rid of '"')