Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Properties Page 1

enter image description here

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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 
share|improve this answer
    
Thank you for the quick and relevant response +1. I will go ahead and try that out. –  Craig Efrein Aug 20 '12 at 13:26
    
Mr. Brownstone, I have tried a variety of methods using bcp, bulk insert and openrowset to get this file imported and the results have been unpredictable at best. Any suggestions? –  Craig Efrein Aug 21 '12 at 9:05
    
The problem you have is that you have now set all field terminators as pipes. I wil add an example to my answer that should help you. –  Mr.Brownstone Aug 21 '12 at 9:35
    
That was it, I'm still having some formatting issues with a couple of the columns. Like you said, I will have to do lots of testing. –  Craig Efrein Aug 21 '12 at 10:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.