Bulk insert ,double Fieldterminator at the last column issue

Question

when I insert from a csv with bulk, the last column has FIELDTERMINATOR twice when it should be null, in the csv that field is empty.

`

   BULK INSERT my_table FROM 'path'
   WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      FIRSTROW = 2
         )

`

the way that the last column is null

Does this answer your question? [Insert a empty string on SQL Server with BULK INSERT](https://stackoverflow.com/questions/15581814/insert-a-empty-string-on-sql-server-with-bulk-insert) — nbk, Nov 25 '22 at 01:23
no, I have other columns that are empty and when I insert them automatically they are placed as null, except the last one which places a double comma. — Ivan Grande, Nov 25 '22 at 02:10
Sounds like your CSV file has more columns than the destination table. e.g.: for a table with 3 columns, all text from column 3 and beyond in the CSV file will get read into the third column of the table. On a line-by-line basis. Excel is notorious for causing this - deleting data in a column still writes out blank columns (and so field delimiter characters) to the CSV file. — AlwaysLearning, Nov 25 '22 at 03:15
The csv I'm working with has data removed from the last column in excel, is there a way to correct this without having to modify the csv directly. — Ivan Grande, Nov 25 '22 at 05:34

score 0 · Answer 1 · answered Nov 25 '22 at 03:23

Check your CSV file to see if it contains extra columns and/or delimiter characters on the affected lines.

Consider the following CSV example:

Foo,Bar,Baz
1,2,
1,2,,
1,2,3
1,2,3,
1,2,3,4
1,2,3,4,
1,2,3,4,5
1,2,3,4,5,

When reading it into a table with the following SQL code:

use tempdb
go
drop table if exists my_table;
go
create table my_table (
    [First] varchar(10),
    [Second] varchar(10),
    [Third] varchar(10)
);
go
BULK INSERT my_table FROM 'C:\Temp\example.csv' WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);
select * from my_table;
go

It will yield the following imported results:

First	Second	Third
1	2	NULL
1	2	,
1	2	3
1	2	3,
1	2	3,4
1	2	3,4,
1	2	3,4,5
1	2	3,4,5,

Bulk insert ,double Fieldterminator at the last column issue

1 Answers1