CODEWITHSUNDEEP
javasqlsql-servercsvbulkinsert
blueSky
blueSky

Reputation: 659

Bulk insert CSV file

I have a Java code which creates a CSV file and then I use the SQL Sever "BULK INSERT" to insert the data into database; I have no format file. The SQL server is 2008.

When I run the code thru unit test and then run the following sql code everything works fine and the data gets inserted into table:

declare
@csvFileName VARCHAR (1024)= 'file.csv',
@csvFormatFileName VARCHAR (1024)= null,
@tableName VARCHAR (256) = 'tableName';

EXECUTE (       
'BULK INSERT '+ @tableName +' FROM ''' + @csvFileName + ''' WITH 
(FIRSTROW=2, FIELDTERMINATOR=''\t'', ROWTERMINATOR=''\n'', MAXERRORS=0);');

When my Java code runs in production and creates the CSV files, if I grab one of the CSV files created, and run the above SQL, it gives me no error, however no data is inserted into table, I just get:

(0 row(s) affected)

I first thought its the production DB server vs. dev DB server that causes the issue, but that was not the case.

This is the sample data in CSV file:

ID  Date    number1 number2 number3

88  2013-01-14  2   0   0.00

64  2013-01-14  0   0   0.00

I cannot see any difference bet. the CSV file that runs with no problem and the one that does not! After all they are created with the same Java code.

Is there any editor that can show me the contents of the files exactly as they are with tab, new line, ...?

Any help is greatly appreciated.

Upvotes: 0

Views: 1909

Answers (1)

blueSky
blueSky

Reputation: 659

The ones on my machine created on Windows which adds CRLF at the end of each line. The ones on production created on Uinx which adds only LF. The solution would be to use format file created by MS SQL Server bcp utility which works on any platform.

Upvotes: 1

Related Questions