Bulk Insert with Null Value

Question

I'm having trouble inserting null value from a bulk insert statement.

The first Row inserted successfully,but the second not.i also checked with CHECK_CONSTRAINTS,but the problem remains

please help me

Here is an extract of the csv file:

First Name, Last Name, Age

Name1,HK,23

Name2,YH, 

Here the bulk Insert statement:

Bulk insert dbsct 
	FROM 'C:\Test1\dstd.CSV'
	WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR =',',
    ROWTERMINATOR = '\n'
    KEEPNULLS
    )

Answer 1

What error message do you get?

Does the file really have those blank lines?

How does your table definition look like?

Answer 2

(1 row(s) affected).

no.

First Name   Varchar(100)

Last Name   Varchar(100)

Age   int

Answer 3

yes.The file has blank lines.

Then you should specify the record terminator as \n\n. But if there is no blank line after the last line with data, you will probably lose that record.

Answer 4

sorry.there is no blank line after the last line

and also there is no  blank line between the rows

Answer 5

I did tests with the data you published and functioned normally.

Reinforcing the Erland's thesis: How does your table definition look like?

Answer 6

Table dbsct

First Name   Varchar(100)

Last Name   Varchar(100)

Age   int

CSV file

First Name, Last Name, Age
Name1,HK,23
Name2,YH,

Bulk insert dbsct 
	FROM 'C:\Test1\dstd.CSV'
	WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR =',',
    ROWTERMINATOR = '\n'
    KEEPNULLS
    )

The last row not added

Answer 7

Make sure to keep an space in the NULL field when it is the last column(Age column in the second row).

Answer 8

now its working.

But the csv file is already generated.i can't modify it.

there is any other option

Thanks for your valuable reply.

Answer 9

Can you upload the file somewhere, so that we can look at the exact bytes? BULK INSERT is a binary tool, and is not the most flexible guy in town. My guess is that there is no terminating CR-LF on the last line.

Answer 10

Use Notepad++ to replace the expression ",\r\n" by ", \r\n".

Ignore quotation marks in expression.