Import CSV File

Question

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?

Answer 1

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:

9.0 
18 
1       SQLCHAR       0       510     ";"     1     web1numdoss 
2       SQLCHAR       0       510     ";"     2     web1dem 
3       SQLCHAR       0       510     ";|"     3     web1def 
...
18      SQLCHAR       0       510     "\r\n"   18    Col018