?
Solved

Invalid Collation Name

Posted on 2006-05-09
7
Medium Priority
?
376 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:running32
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16642075
interesting...
can you post the table structure, and a sample data row from the file (to simplify my testing task)
0
 

Author Comment

by:running32
ID: 16642200
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16642542
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:running32
ID: 16650830
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16650894
can you check which collation the field rating has in the table?
0
 

Author Comment

by:running32
ID: 16651040
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
 

Author Comment

by:running32
ID: 16651049
collation for 17 is database default
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question