Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Integration / Customization
IPA/ProcessFlow
CSV file with embedded commas
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
LynneB
Past 24 Hours:
0
Prev. 24 Hours:
0
Overall:
5123
People Online:
Visitors:
216
Members:
0
Total:
216
Online Now:
New Topics
Lawson S3 Financials
PO Matched Dollars and Matched Quantity
5/10/2024 6:51 PM
Can anyone share the Lawson calculation to determi
S3 Customization/Development
COBOL CSV file
5/10/2024 2:36 PM
We have a custom Cobol program ZU200 that creates
S3 Systems Administration
Using MFA for Lawson S3 Access
5/8/2024 1:23 PM
Does anyone use Multi Factor Authentication when u
Lawson Landmark
Executing IPA from PR160
5/3/2024 2:46 PM
We're facing a situation where we need to upda
IPA/ProcessFlow
Executing IPA from PR160
5/3/2024 2:46 PM
We're facing a situation where we need to upda
S3 Customization/Development
Cobol - Extract Current Time
4/24/2024 7:21 PM
How do you extract just the Current System Time in
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1357
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
IPA/ProcessFlow
CSV file with embedded commas
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
Terry P
Veteran Member
Posts: 234
8/8/2013 4:44 PM
I have a CSV file being delivered by an outside agency. It is a CSV file in that commas separate the fields, but they are also putting the ALPHA fields wrapped in double quotes. A typical record might look like this:
12345,"Terry","Pickering","this, that and others"
I'm using standard data iterator activity to parse out each field. I also can remove the "quote" characters. The problem is when I encounter a record where they have embedded the comma in a field surrounded by quotes. The parser picks up the comma in the middle of the field.
Any suggestions on how to get around this?
Bob Canham
Veteran Member
Posts: 217
8/8/2013 4:58 PM
I haven't found a solution to that (and would look forward to hearing from anyone who has). What we were able to do was get the outside agency to send it as pipe | delimited file rather than comma delimited. That took care of the problem for us.
John Henley
Posts: 3352
8/8/2013 4:58 PM
The best solution I've used is regex (regular expression) in JavaScript. It's very powerful for parsing any type of string data. Not easiest thing to learn/grasp but it works well for what you're trying to do (trust me, I've been down this road
jamesraceson
Veteran Member
Posts: 52
8/8/2013 5:03 PM
Terry,
If you are familar with regular expressions and Javascript, you could write one to look for the pattern [space comma] [comma space] and [space comma space]. You could put the code inside of an assign node after each pass for the record. Because most CSV files do not have a leading or trailing space for each field, if you found a match to your expression this would most likely be a comma inserted in somewhere in the data (in between the quotes). You would then replace the matches with just one space. I would run a check to make sure before committing any changes, but that should help get rid of the unwanted commas.
Terry P
Veteran Member
Posts: 234
8/8/2013 5:08 PM
Good suggestions. I like the last one. I could do a replace on the entire record and do a replace ", " with " ". This is then before I parse the individual fields. I'll post the solution if it works.
Terry P
Veteran Member
Posts: 234
8/8/2013 5:24 PM
That worked!
I added an Assign between the two data iterator activities to a new variable with this code:
CSVFile_outputData.replace(/, /g," ")
Then I replaced the input for the second one to use this new variable.
You are not authorized to post a reply.