Invalid Collation Name

I am trying to use a bcp to import a text file into sql.  The problem is that column one in the csv file is stored as lastname, firstname and I want to import them as seperate fields.   I was pointed toward using a format file but it keeps giving me the error.

Cannot perform bulk insert.  Invalid collatin name for source column 17 in format file 'd:\bcpfmt.txt'.  Can someone please tell me what I am doing wrong.  Thanks

8.0
17
1     SQLCHAR      0     7     ""     1     lname     Latin1_General_CI_AI
2     SQLCHAR      0     11    ""     2     fname     Latin1_General_CI_AI
3     SQLDATETIME  0     28    ""     3     dob       Latin1_General_CI_AI
4     SQLFLT8      0     13    ""     4     age       Latin1_General_CI_AI
5     SQLNCHAR     0     16    ""     5     chart#    Latin1_General_CI_AI
6     SQLDATETIME  0     21    ""     6     visitdate Latin1_General_CI_AI
7     SQLNCHAR     0     26    ""     7     PROVIDER  Latin1_General_CI_AI
8     SQLNCHAR     0     9     ""     8     ofFice    Latin1_General_CI_AI
9     SQLNCHAR     0     12    ""     9     diag1     Latin1_General_CI_AI
10    SQLNCHAR     0     14    ""     10     cpt      Latin1_General_CI_AI
11    SQLNCHAR     0     9     ""     11     ssan     Latin1_General_CI_AI
12    SQLNCHAR     0     9     ""     12     sex      Latin1_General_CI_AI
13    SQLNCHAR     0     9     ""     13     address  Latin1_General_CI_AI
14    SQLNCHAR     0     9     ""     14     city     Latin1_General_CI_AI
15    SQLNCHAR     0     9     ""     15     state    Latin1_General_CI_AI
16    SQLNCHAR     0     9     ""     16     zip      Latin1_General_CI_AI
17    SQLNCHAR     0     6     "\r\n"     17     rating   Latin1_General_CI_AI
running32Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, you field structure and your format file don't really match.
also, try to avoid loading directly into field data types other than char/varchar.

create table x
(lname  nvarchar(500)
,fname  nvarchar(500)
,dob  varchar(500)
,age varchar(500)
,chart#  nvarchar(500)
,visitdate  varchar(500)
,provider  nvarchar(500)
,office  nvarchar(500)
,diag1  nvarchar(500)
,cpt nvarchar(500)
,ssan  nvarchar(500)
,sex  nvarchar(500)
,address  nvarchar(500)
,city  nvarchar(500)
,state  nvarchar(500)
,zip  nvarchar(500)
,rating  nvarchar(500)
)
go
bulk insert x from 'c:\temp\data.txt' with (formatfile = 'C:\temp\data.fmt' )
go
select * from x
go
drop table x



the format file will be like this, however you have 1 big problem:
the column terminator should be a tab "\t" instead of " ", but requires that you actually have a tab between the fields
otherwise, you need to specify the CORRECT and EXACT length of the fields

8.0
17
1     SQLCHAR      0     0     " "     1     lname     Latin1_General_CI_AI
2     SQLCHAR      0     0    " "     2     fname     Latin1_General_CI_AI
3     SQLCHAR      0     0    " "     3     dob       Latin1_General_CI_AI
4     SQLCHAR      0     0    " "     4     age       Latin1_General_CI_AI
5     SQLNCHAR     0     0    " "     5     chart#    Latin1_General_CI_AI
6     SQLCHAR      0     0    " "     6     visitdate Latin1_General_CI_AI
7     SQLNCHAR     0     0    " "     7     PROVIDER  Latin1_General_CI_AI
8     SQLNCHAR     0     0     " "     8     ofFice    Latin1_General_CI_AI
9     SQLNCHAR     0     0    " "     9     diag1     Latin1_General_CI_AI
10    SQLNCHAR     0     0    " "     10     cpt      Latin1_General_CI_AI
11    SQLNCHAR     0     0     " "     11     ssan     Latin1_General_CI_AI
12    SQLNCHAR     0     0     " "     12     sex      Latin1_General_CI_AI
13    SQLNCHAR     0     0     " "     13     address  Latin1_General_CI_AI
14    SQLNCHAR     0     0     " "     14     city     Latin1_General_CI_AI
15    SQLNCHAR     0     0     " "     15     state    Latin1_General_CI_AI
16    SQLNCHAR     0     0     " "     16     zip      Latin1_General_CI_AI
17    SQLNCHAR     0     0     "\r\n"     17     rating   Latin1_General_CI_AI

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
interesting...
can you post the table structure, and a sample data row from the file (to simplify my testing task)
0
 
running32Author Commented:
thanks, the table structure is in sql.  The data in the file is in a csv.  The problem is the name has a , in it, so I cannot use a , as a seperator.  Thank you again for looking.

sql table structure is

lname  - nvarchar
fname - nvarchar
dob - datetime
age - float
chart# - nvarchar
visitdate - datetime
provider - nvarchar
office - nvarchar
diag1 - nvarchar
cpt - nvarchar
ssan - nvarchar
sex - nvarchar
address - nvarchar
city - nvarchar
state - nvarchar
zip - nvarchar
rating - nvarchar

file structure is


Cxxxxx, Lxxx     11-02-48     58     S1111-02     1-Feb-06     d22     mfc     203.3     12455     124-42-1245     f     401 Taaddd Dr     xxxxx Cxxxxx     xx     8xxxx     XX

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
running32Author Commented:
thanks but I am still getting the error

Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 17 in format file 'd:\data.fmt'.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you check which collation the field rating has in the table?
0
 
running32Author Commented:
It appears that it has something to do with the file.  I don't think it can find the end of the csv file.  My problem is when I open it in access I cannot see where it ends.  any ideas?

Thanks
0
 
running32Author Commented:
collation for 17 is database default
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.