bulk insert troubles

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

( ) writes:
> 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?
>...
> 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

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 DeleteThis @sommarskog.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/prodinfo/previousversions/books.mspx