Short version: Is it possible to preserve quotes on certain data fields in a comma-delimited file that also have commas between the quotes?
I have a .csv flat file that needs to be transformed into a a different csv format. The problem arises around quotes and commas.
Input example (this profile is limited to 7 elements):
123456789, L, "Doe, John", 9/28/1970, "Washington, DC", CompanyName, DepartmentName, Cost Center
Due to the commas in the quoted regions, it messes up the positioning.
So, say as an example I want to simply concatenate CompanyName and Department Name.
With no options in either the inbound or outbound file, we lose data at the end.
123456789, L, "Doe, John", 9/28/1970, "Washington, DC"
If the inbound file uses a text qualifier, we lose the quotes, but all data goes to its correct field.
123456789, L, Doe, John, 9/28/1970, Washington, DC, CompanyName-DepartmentName,, Cost Center
The problem arises now when the above data enters the destination system
Finally, I can output all data "correctly" if I set the output profile to use double quotes. However, this quotes every single piece of character data, which is not what the end system (out of my control) expects.
Thanks for any and all help.