Replication sets and delimiters

I'm having difficulty with some fields in the replication set that have additional commas in the data. Is there a way to change the delimiter used to create a csv rep set? If I do that then I can remove the commas from the data without losing my separators. If not, is there java script code that can remove commas from just the data? I'm not very versed in java script and have not found a solution on the internet.

Find more posts tagged with

Process Automation (IPA) - General Discussions

Accepted answers

All comments

russel-cornelson

Good afternoon Ann,
Replication sets generate data as CSV; that means fields that contain commas in the data are surrounded by double quotes.

EmployeeID,PresentationName,Company
"999999","Cornelson, Russ","Infor"

You can place a javascript function in an assign node at the start of your flow with the code that is found at the bottom of this post. To leverage this you use a dataiterator to parse the file by line, and for each line you can use javascript in an assign node to call that function by line.

var [vEmpId,vName,vComany] = CSVtoArray(DataIterator###_outputData);
that will split the data from that line into 3 variables which you can use.

NOTE: This CSVtoArray function uses a regex lookahead which is EXTREMELY expensive if you send a large volume of data at it. Only use this to parse a single line of CSV data into their fields.

/************************
Function CSVtoArray:
If valid CSV is found; return array of items
If invalid CSV is found; return NULL
************************/
function CSVtoArray(text) {
var re_valid = /^s*(?:'[^'\]*(?:\[Ss][^'\]*)*'|"[^"\]*(?:\[Ss][^"\]*)*"|[^,'"s\]*(?:s+[^,'"s\]+)*)s*(?:,s*(?:'[^'\]*(?:\[Ss][^'\]*)*'|"[^"\]*(?:\[Ss][^"\]*)*"|[^,'"s\]*(?:s+[^,'"s\]+)*)s*)*$/;
var re_value = /(?!s*$)s*(?:'([^'\]*(?:\[Ss][^'\]*)*)'|"([^"\]*(?:\[Ss][^"\]*)*)"|([^,'"s\]*(?:s+[^,'"s\]+)*))s*(?:,|$)/g;

// INVALID INPUT -- Return null
if (!re_valid.test(text)) return null;

var a = []; // Initialize array to receive values.
text.replace(re_value, // "Walk" the string using replace with callback.
function(m0, m1, m2, m3) {

// Remove backslash from ' in single quoted values.
if (m1 !== undefined) a.push(m1.replace(/\'/g, "'"));

// Remove backslash from " in double quoted values.
else if (m2 !== undefined) a.push(m2.replace(/\"/g, '"'));
else if (m3 !== undefined) a.push(m3);
return ''; // Return empty string.
});

// Handle special case of empty last value.
if (/,s*$/.test(text)) a.push('');
return a;
};

unknown

I have used this code to replace the , delimiter with | and ignore any , that is within a quoted field. then you can assign your variables based on the dataline array.

MyRecord = processRepSetOut_outputData

// replace , with | but don't replace if within a quoted field.
// at the end remove the quotes
q = false;
for (x=0;x<MyRecord.length;x++) {
if (MyRecord.substr(x,1) == '"') {
if (q== true) q= false;
else q=true;
}
if (MyRecord.substr(x,1) == ",") {
if (q==false) MyRecord = MyRecord.substr(0,x)+"|"+MyRecord.substr(x+1,MyRecord.length-x-1);
}
}
MyRecord = MyRecord.replace(/"/gi, '');

dataline = MyRecord.split("|");

EmployeeID = dataline[0];
Name = dataline[1];
Company = dataline[2];

Thanks,
Pam

unknown

OK, thanks so much!

This looks like the way to go since I have about 29k employees in this rep set, too many for the previous suggestion I guess. This is what I was wanting to do, replace the comma with something without disturbing the data.

I’ll give this a try.

Ann

russel-cornelson

Good morning AnnH,

If you are on a Multi-tenant system, I would really prefer if you used the solution I provided above. The second solution walks through the string substringing each character looking for double quotes and is not nearly as efficient.

I set up a test of both methods; my path in the first post is 30% faster, and allocates 32% less memory.

If you are on a single tenant system that probably isn't a problem but if you are on our MT system, that may result in a memory alert which could prevent it's execution in the future. Also depending on what you want to do with each record there may be an even more efficient way.

If the javaScript above is too daunting just open a ticket with me in Infor Support I would gladly assist you with implementing it in your flow.

unknown

Russel that is great news. We have 13 IPAs that consume Rep Sets in GHR and 5 IPAs in FSM.

But they usually have 30 or more fields in the Rep Set. The largest being 93 fields.

Is this really scalable to do that many fields? I believe I'd have to send all 93 variables into the function and the function would have to have variables m0 to m92.

Thanks,
Pam

unknown

Hahaha... saw right through my ruse. I could understand Pam's code, I could not grasp the other. I will probably open a ticket after I try to utilize this myself. Once I try it I'll have better questions.

Copyright © 2025 Infor. All rights reserved.