Parse Delimited by New Lines, new lines enclosed in the rows of data
Hi community,
I have an interesting problem with the Parse Delimited rule, in that my requirement is to split a CSV file first into lines, and then parse each line so that I can build a page-list. The csv format includes text qualifiers as double quotes, where the data itself has a special character such as a comma or a new line. When I apply the first parse, using \n as the delimiter, if a newline is also included inside one of the rows of data, it gets split into 2 rows. Example data:
Name, Address Line 1, Date of Birth
Bob, 1 The road, 01/01/2022
Fred, "6 the grove
Warrington", 01/02/2022
Elizabeth, The Road, 01/03/2022
What I need is the parse rule to export 4 lines here. Like this:
Name, Address Line 1, Date of Birth
Bob, 1 The road, 01/01/2022
Fred, "6 the grove{return char here}Warrington", 01/02/2022
Elizabeth, The Road, 01/03/2022
But unfortunately I cannot get a configuration to work for this case, but it is fairly common for this to be the case, so it must have a solution!
Updated: 8 Jul 2022 21:29 EDT
Accenture
AU
@JamesR61 there's a few things to consider when parsing CSV files
- The first thing to check is the OS where it was generated, since windows/dos applications uses carriage return and line feed ("\r\n") as a line ending whereas uinx based systems use just line feed ("\n").
- Assuming that the file has just line feed ("\n") as the line ending, if a "Service File" rule is being used process the CSV then do the following
- In the request tab use Delimited Parse rule to parse the CSV content , this is key
- The Parse rule will be as follows
and that's it .. it should work