The column delimiter for column "" was not found.

Winston TheFifth 106

I'm importing a csv file. I'm getting an error: The column delimiter for column "" was not found. It's always on the last column. And one of the columns in the csv file has a comma in the column value.

Dillon Silzer 57,431 Reputation points

2022-07-11T18:46:24.207+00:00

Is it possible to post the csv file here? Where are you trying to import the csv file to? Excel?
Winston TheFifth 106 Reputation points

2022-07-11T23:20:52.457+00:00

I'm trying to figure out how the file was created. I can't get to the file from here. This is what I'm seeing in SSIS in the flat file connection manager. I'll post again when I figure it out. I'm importing to a SQL DB.

ex 1
Select the csv file.
General selected on the left
Code page automatically selects 65001 (If you change it, it will change back the next time you open it.)
text qualifier is set to <none>
click Columns on the left
all the column headers and values are surrounded by double quotes
The column with the comma in the value displays incorrectly in 2 columns instead of the same column.

ex 2
Select the csv file.
General selected on the left
Code page automatically selects 65001 (If you change it, it will change back the next time you open it.)
text qualifier is set to "
click Columns on the left
no double quotes appear in the column header or values
The column with the comma in the value displays correctly.
ZoeHui-MSFT 37,671 Reputation points

2022-07-12T01:34:04.257+00:00

Hi @Winston TheFifth ，

Could you please open the file with notepad++ to check the delimiter?

If possible you may share the file with us so we could do more analysis, remember to protect the sensitive data.
Dillon Silzer 57,431 Reputation points

2022-07-12T03:53:18.707+00:00

My guess is you have a column with a comma in it. You need to escape commas in a csv using double quotes ("").
Winston TheFifth 106 Reputation points

2022-07-12T12:37:39.233+00:00

I opened the file in Notepad++ the delimiter is a comma. I attached a test file. It wouldn't let me upload a csv file. It's behaving the same when I select <none> as the text qualifier. But it's not running at all (build error) when I use " as the text qualifier instead of the original error, "The column delimiter for column "" was not found."

Exception deserializing the package "The process cannot access the file 'C:\Users\username\Documents\Visual Studio 2019\Integration Services Project1\Integration Services Project1\bin\Development\Integration Services Project1.ispac' because it is being used by another process.". (Microsoft.DataTransformationServices.VsIntegration)

219931-csvex.txt
Winston TheFifth 106 Reputation points

2022-07-12T12:46:10.867+00:00

If I use <none> as the text qualifier the value is already pushed into another column in the flat file connection manager. If I use " as the text qualifier I get an error.
Yitzhak Khabinsky 25,956 Reputation points

2022-07-12T14:18:29.457+00:00

@Winston TheFifth ,

*.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data.
The most reliable format for data feeds is XML enforced by XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, and enforce data quality.
kalesha dharmavarapu 0 Reputation points

2024-07-30T10:17:13.3866667+00:00

Hi, I am also getting the same type of error, i will share the data with you. csv file into odbc destination, kindly look into this , awating for your reply, thanks in advancetest.txt
Zahid Butt 956 Reputation points

2024-07-30T12:09:08.6266667+00:00

Hi @Winston TheFifth,

I just picked up your text file & uploaded file successfully in SQL server 2022 (using import wizard) without any error using both methods (Text qulaifier as " & without text qualifier).

Output with qualifier as " :

Output with qualifier Null:

Share via

The column delimiter for column "" was not found.

Your answer