This might seem like the kind of question that has been asked many times, but I can’t seem to find an answer to my specific problem on this forum or elsewhere. I hope that I’m not missing something obvious.
I am using an ADF (v2) copy activity to convert an Excel file to a (new) CSV file. Some of the column headers in the Excel source file have a single comma. Example: “foo, bar”. When inspecting the output CSV file with a text editor, the column headers are not quoted even though “Quote All Text” is selected on the Sink tab of the copy activity. Data in all other rows are quoted as expected. When I open the CSV file in the Excel UI, each column containing a comma in the header is split into two fields. For example, the (single) column “foo, bar” from the Excel file appears as two separate columns in the CSV: “foo” and “bar”, which is undesired.
The only potential solution I can think of is to uncheck “First Row as Header” in the Excel dataset so that the copy activity assigns “Prop_0”, “Prop_1” as the headers in the CSV and then create a second CSV from the first one, with “Skip Line Count” set to 1 in the Source settings on the copy activity. I haven’t tested this solution, but it seems inefficient and potentially costly to scale.
Does anyone know of a better solution?
Thanks in advance for any advice.



