Comma-Quote Delimited Parsing Techniques

Greetings All,

Does anyone have any logic or suggestions for parsing comma-quote delimited structured files? If you are unfamiliar with this concept, these are comma separated value (CSV) files with double-quotes used to delimit strings within a field so that the strings can contain commas if need be.

Thanks In Advance,

Casey

Casey,

Parse to what degree? Are you attempting to add the contents of the .csv file into a dataset are are you looking into looping the contents of the .csv file to grab specific information and looking to use a proper delimiter to gather the data? There is our CSV to Dataset activity that will parse the contents into a dataset that will allow you to loop through the appropriate columns and rows for data.

-Leonard

Hi Leonard,

Is the CSV to Dataset action available in v.9.0.4.5?

Basically I am having difficulty navigating the comma-quote structure. The objective is to extract a sub-string from a quote-delimited field within the CSV structure, and then replace the contents of the same field with the sub-string, leaving only the sub-sting. So in the example below, I need to remove everything but the "ID sub-string" from Field5.

Before:     Field1, Field2, "Field3,sub-string,sub-string", Field4, "Field5,sub-string,sub-string,ID sub-string"

After:     Field1, Field2, "Field3,sub-string,sub-string", Field4, "ID sub-string"

EDIT: It seems straightforward enough, conceptually, to accomplish with nested loops, but it's turning out so be not so simple in practice.

Thanks,

Casey

Even more specifically...

In order to extract the initial string, I can simply ignore the quotes and loop through the record as a list separated by commas. When I am having trouble is identifying the correct field again so that I can replace it's contents with the extracted value.

Casey

Hello Casey,

One suggestion would be to loop through the list, and since we know that the fifth loop will always be the one to extract data from, we can use a variable counter to loop through and essentially count to 5. Inside the loop, we can use an "if statement" to check if counter is at 5, and to do the data replacement using "replace - text" action. Inside of this "if condition" remember to reset the counter back down to 0 so you can start again.

Thanks for the feedback Alex and Leonard. The way I ended up solving for this is as follows.

I ignored the columns altogether and just looked for double-quotes, That allowed me to find the correct field within a couple of iterations. The ID was always preceded by the same sting, so I crated and IF condition to check each quote-delimited string, and then when I identified the one that I wanted, I saved off the ID into a variable.

As for the replacement, I used similar logic as above to identify the field, and then just parsed through that one field as thought it was a comma-separated list. Once I got to the end, I saved off the sub-string and then did a Replace action on the record to identify the sub-sting and replace it with the ID.

Casey