Currently, we have a DTS package on SQL Server 2000 that imports a file called suicoweb.csv. The DTS csv file properties pages look like this.
Here is a sample line from the CSV file
500071;343497;260712;|Some Text; : employer : some more text|;29
The columns are seperated by semi-colons(;) and the text qualifier is a pipe (|) and the lines by a carriage-return
Using Mr. Brownstone's answer, I created a format file using the following command:
bcp databasename.dbo.tablename format nul -c -f d:\formatfile.fmt -T
Here is the output of that file:
9.0
18
1 SQLCHAR 0 510 "|" 1 web1numdoss
2 SQLCHAR 0 510 "|" 2 web1dem
3 SQLCHAR 0 510 "|" 3 web1def
4 SQLCHAR 0 510 "|" 4 web1douv
5 SQLCHAR 0 510 "|" 5 web1dversjj
6 SQLCHAR 0 510 "|" 6 web1dversmm
7 SQLCHAR 0 510 "|" 7 web1dversaa
8 SQLCHAR 0 510 "|" 8 web1mntvers
9 SQLCHAR 0 510 "|" 9 web1soldos
10 SQLCHAR 0 510 "|" 10 web1dactjj
11 SQLCHAR 0 510 "|" 11 web1dactmm
12 SQLCHAR 0 510 "|" 12 web1dactaa
13 SQLCHAR 0 510 "|" 13 web1actnat
14 SQLCHAR 0 510 "|" 14 web1actlib
15 SQLCHAR 0 510 "|" 15 web1archdoss
16 SQLCHAR 0 510 "|" 16 web1numdem
17 SQLCHAR 0 510 "|" 17 web1numdef
18 SQLCHAR 0 510 "\r\n" 18 Col018
I replaced \t with a pipe(|) because that's what I would like to use as the text qualifier.
Then I used this BULK INSERT command to import:
BULK INSERT ScpCambron.dbo.suicoweb1
FROM 'D:\SqlFtp\scpcambron\suicoweb1.txt'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',
FORMATFILE='D:\SqlFtp\scpcambron\suicoweb1.fmt'
)
When I try to import the csv file with the BULK INSERT command I am getting the following error:
Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (web1numdoss).
Any suggestions on how I should modify the format file to correctly import the csv file? Should I be using BCP instead to import the csv file my table?