Hello,

I am trying to load a csv file using bulk insert. Everything goes fine
but no data is loaded! All I get is a message saying "(0 row(s)
affected)". Any idea what s going on?


Set up:
create table dbo.test (member_id varchar (50),
prod_name varchar(50), prod_score varchar(50))


SQL:
bulk insert dbo.test
from 'C:/temp/test.csv'
with (FIRSTROW = 2,FORMATFILE = 'C:/temp/my-format.fmt')


Format File: my-format.fmt
9.0
3
1 SQLCHAR 0 50 "\",\"" 1 member_id SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\",\"" 2 prod_name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "\"\r\n" 3 prod_score SQL_Latin1_General_CP1_CI_AS


Data file:
2,a,3
2,b,1
2,c,5
3,a,3
3,b,4
3,c,0

(ableport (AT) gmail (DOT) com) writes:
The format file does not match the data file. The file uses only comma
as delimiter, but according to your format file, the file is expected
to look like this:


2","a","3"
2","b","1"
2","c","5"
3","a","3"
3","b","4"
3","c","0"




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx