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?
Best Answer
From what you are describing it sounds like you needs to provide the BULK INSERT command a format file so it knows how to parse your data. You can use the bcp command to auto-generate one for you, however I have always had to edit them afterwards before they work properly. (Lots of testing I am afraid).
The links below should be good starting points for you:
Format Files: http://msdn.microsoft.com/en-us/library/ms190393.aspx
BCP: http://msdn.microsoft.com/en-us/library/ms162802.aspx
BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx
EXAMPLE: http://msdn.microsoft.com/en-us/library/ms178129.aspx
I hope this helps you.
EDIT
Based on the extra information that you have added above it looks like you have set all field terminators to pipes, which is not what you want. You will need to edit the format file on a per column basis.
For instance, in the example above your first two columns are delimited by a semi-colon. The third would need to be delimited by ";|". Here is an example of what I mean for the first few columns: