|
View previous topic :: View next topic
|
| Author |
Message |
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
NOT sure if this is bad data, business need to confirm, but I'm attempting to convert a CSV to flat file (FB 80) and ran into this scenario (the 2nd record has the comma in the quotes:
| Code: |
00000,MKE1827,00000,C,7,20.25
11111,",AE0001",11111,D,1,79.93
22222,DAY8904,22222,D,1,1977.39
33333,DUB1411,33333,D,1,145677.55
44444,USA1492,444445,D,1,176.18
55555,CLT7278,55555,D,1,556.94
|
I used this code, which worked for all rows except that record (I skipped the 1st record as it was the header):
| Code: |
OPTION COPY,SKIPREC=1
INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=20)
%02=(ENDBEFR=C',',FIXLEN=08)
%03=(ENDBEFR=C',',FIXLEN=15)
%04=(ENDBEFR=C',',FIXLEN=01)
%05=(ENDBEFR=C',',FIXLEN=01)
%06=(ENDBEFR=C',',FIXLEN=15)),
BUILD=(%01,JFY=(SHIFT=RIGHT,
LEAD=C'00000000000000000000',LENGTH=20),
21:%02,JFY=(SHIFT=LEFT),
29:%03,SFF,TO=PDC,LENGTH=07),
36:%04,
37:%05,
38:%06,SFF,TO=PDC,LENGTH=07) |
NOT sure how to handle that record, any suggestions? This file has about 200,000 records and about 100 records have data like this.
Any advice would be greatly appreciated! |
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1401
Location: Bamberg, Germany
|
|
|
|
Give this a try:
| Code: |
OPTION COPY,SKIPREC=1
INREC PARSE=(%01=(ENDBEFR=C',',FIXLEN=20),
%02=(ENDBEFR=C',',PAIR=QUOTE,FIXLEN=10),
%03=(ENDBEFR=C',',FIXLEN=15),
%04=(ENDBEFR=C',',FIXLEN=01),
%05=(ENDBEFR=C',',FIXLEN=01),
%06=(ENDBEFR=C',',FIXLEN=15)),
BUILD=(%01,UFF,M11,LENGTH=20,
21:%02,JFY=(SHIFT=LEFT,PREBLANK=C'"',LENGTH=08),
29:%03,SFF,TO=PDC,LENGTH=07,
36:%04,
37:%05,
38:%06,SFF,TO=PDC,LENGTH=07)
END |
|
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10896
Location: italy
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1401
Location: Bamberg, Germany
|
|
|
|
| While the document is still helpful in some aspects, it is however 15 years old now. |
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
Enrico-Sorichetti,
Yes, I reviewed the Sort tricks PDF but still cannot resolve my matter, as my output should be as follows for the 1st two fields (the other fields are correct):
| Code: |
00000000000000000000MKE1827
00000000000000011111AE0001
00000000000000022222DAY8904
00000000000000033333DUB1411
00000000000000044444USA1492
00000000000000055555CLT7278 |
This is what I get when I execute my code:
| Code: |
00000000000000000000MKE1827
0000000000000AE0001" <-- then the fields following this are "off" as well
00000000000000022222DAY8904
00000000000000033333DUB1411
00000000000000044444USA1492
00000000000000055555CLT7278 |
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1401
Location: Bamberg, Germany
|
|
|
|
| Code: |
| 21:%02,JFY=(SHIFT=LEFT,PREBLANK=C'",',LENGTH=08), |
You should provide expected Output, otherwise it's hard to help.
There are now enough solutions for the problem. |
|
| Back to top |
|
|
chillmo
New User
Joined: 31 Aug 2017
Posts: 54
Location: USA
|
|
|
|
| Thx Joerg.Fondeisen! I'll try and let you know. |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
