Data iterator not honoring quoted text with comma

Running into an issue with records that have quoted text and a comma inside, like below, data iterator seems to be ignoring quote and finds the next comma and breaks the data into multiple outputData fields. So if there is a record without the quoted text the sequence of outputData fields is off between records...

LIN,US,1,1,CA_VENDOR12,4,2019-06-13,1,700000,700000,"DRAPE, STERI 3M 1089",DMS-US1,CS,48.00000,1.000,48.000,0.000,0.000,0.000,1.000,1.000,1.000,tecuser,cc1 buyer,cc2 buyer,cc3 buyer,cc4 buyer,2019-06-18 10:58:52,1212121.00000,121211212122.00000,9999999988.00,,,

Any suggestions on how to overcome this? The vendor could change the delimiter but that will cost me money to have them change it and everytime they have an upgrade...

IPA 10.x

Find more posts tagged with

Process Automation (IPA) - General Discussions

Accepted answers

All comments

unknown

Following - Had the same issue. "Band-aid" approach for now is to tell users not to put commas in the text boxes. This is not the ideal approach.

unknown

Harder approach is to do read each line and use javascript to cleanup.

Easy approach is to load that to a database table (bulk insert if SQL Server, control/load data Oracle). Once loaded, you can include replace the comma when you are doing SQL

lrjurgen

Its a big file and different data elements per row depending on the type of row so building out yet another process is a big pain point. I am already reading each line so maybe the javascript cleanup would be quicker. I'll check with google...

unknown

Lance,

I would consider using a data iterator to break up the individual lines, then feed the output of that directly to an assign node and use a replace function to remove your quotes. You could then feed that to a message builder to put everything back together. You should now have your file without quotes and you could then just work with it as you would normally.

john-miller

I have resolved similar issues for our files 2 different ways.
Run in an assign node in the data iterator:

if (di_outputData.indexOf(""")< 1){
i += 1
str = di_outputData
}else {
str += "," + di_outputData

This will keep the Field number correct (Will not count the second half of the string as an additional field and will build the 2 Fields together.

The other way is to strip the entire Data structure of Quotes before you iterate:

str = FileReader_outputData.replace(/"/g,'')

then run the data iterater for str

lrjurgen

Thanks JD, I have not used js in my IPA's much yet. Would this go in the first iterator that reads the file or the inner iterator that reads the lines?

john-miller

When using the If statement:
the if statement would go in the Per line iteraterator (Iterating based on ",")

When using the global replace:
the global replace would go Prior to iterating the line. If you're iterating from a file by line, stick an assign node between the Start of the File Iterator and the start of the Line iterator.

I can send you an example of each if you would like, just send an email address.

unknown

Interested in an example - tammy.viola@ctg.com - Thank you!

lrjurgen

lrjurgen@gundersenhealth.org - Thanks JD

lrjurgen

Hey JD would you be able to send that example? Maybe you did and our O365 blocked it ;-)

russel-cornelson

Being a programming language there a variety of ways to accomplish what you are looking for.

DataIterator not honoring quoted text with comma

For clarification, the DataIterator is essentially utilizing a split function on a specific delimiter. This is not the same as a CSV parser which would ignore the comma if found inside double quotes.

If you are parsing with a data iterator line by line AND don't have double quotes inside your data.

var da ='LIN,US,1,1,CA_VENDOR12,4,2019-06-13,1,700000,700000,"DRAPE, STERI 3M 1089",DMS-US1,CS,48.00000,1.000,48.000,0.000,0.000,0.000,1.000,1.000,1.000,tecuser,cc1 buyer,cc2 buyer,cc3 buyer,cc4 buyer,2019-06-18 10:58:52,1212121.00000,121211212122.00000,9999999988.00,,,';

var fields = []
fields=da.split(/,(?=(?:(?:[^"]*"){2})*[^"]*$)/)

for (item in fields) {
print (fields[item])
}

https://www.jdoodle.com/embed/v0/1W4b

You could also do this in a javascript expression inside your data iterator.

[fieldname1,fieldname2,fieldname3,fieldname4,fieldname5,.....].split(/,(?=(?:(?:[^"]*"){2})*[^"]*$)/);

Final Note
If you are on 10.1.1.x; and you are tearing down the entire contents of a file you should not use message builder to piece it back together. 10.1.1.x does NOT have the StringBuilder performance fix and can cause detrimental impact on the performance the LPA node.

If you are on 10.1.1.x and need to piece this data back together into a file you should use the solution I provided in:

https://community.inforxtreme.com/infor-process-automation-product-group/f/infor-process-automation-product-group---discussion/13553/infor-process-automation-performance---javascript

0712050808501819.doc

lrjurgen

Russel, thank you for chiming in. Very helpful references. I believe I found what will work for me literally 5 minutes before I got the email with your post.

Between my file iterator and my line iterator, I added an assign_node with this javascript expression:

DI1_outputData = DI1_outputData.replace(/(?!B"[^"]*),(?![^"]*"B)/gm,'|')

-- replace any comma NOT within a double quoted string" with a pipe.

Then I am using a file_access node to write the file out for the MA531CSV file that I need to create.

I need to test a bit more, but looks ok so far...

0712131256598360.doc

Copyright © 2025 Infor. All rights reserved.