SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,705 questions
The column delimiter for column "" was not found.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(64, 93%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EWT%3C/text%3E%3C/svg%3E)
Winston TheFifth
106
Reputation points
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.
SQL Server Integration Services
{count} vote
-
Dillon Silzer 57,831 Reputation points Volunteer Moderator2022-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 41,496 Reputation points2022-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,831 Reputation points Volunteer Moderator
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)
-
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.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Yitzhak Khabinsky 26,586 Reputation points2022-07-12T14:18:29.457+00:00 *.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. -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(169.60000000000002, 72%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKD%3C/text%3E%3C/svg%3E)
kalesha dharmavarapu 0 Reputation points2024-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
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(307.2, 78%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EZB%3C/text%3E%3C/svg%3E)
Zahid Butt 961 Reputation points2024-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:
Sign in to comment
Sign in to answer