• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 383
  • Last Modified:

bulk insert using format file

I thought I had this working but I guess not.  I need to import a csv file into a table in sql.  The name is a variable which I pass to a stored procedure.   My problem is that I keep getting collation errors then I try and import using the code below.

I would really be greatful if someone could point out what I am doing wrong. Thanks

*************** format file

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

*************** Stored procedure

bulk insert upload from 'd:\combined.csv' with (formatfile = 'd:\data.fmt' )

************* Sample of data in file
"Test, Person",8/14/1958,47,S81111-02,4/10/2006,124,mfc,124.1,12455,124-14-1245,f,1234 test Ct Unit D,Fort Lupton,XX,12344,B
"Test, Person",8/14/1958,47,S81111-02,8/12/2005,124,mfc,124.1,12455,124-14-1245,f,1234 test Ct Unit D,Fort Lupton,XX,12344,B


0
running32
Asked:
running32
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:

can you post the tabe structure (including collation  definitions )
0
 
running32Author Commented:
all collation definitions are database default.    SQL_latin1_General_CP1_CI_AS,  Thanks

lname  nvarchar(50)
fname  nvarchar(50)
dob  varchar(15)
age varchar(10)
chart#  nvarchar(15)
visitdate  varchar(15)
provider  nvarchar(10)
office  nvarchar(10)
diag1  nvarchar(10)
cpt nvarchar(10)
ssan  nvarchar(15)
sex  nvarchar(10)
address  nvarchar(50)
city  nvarchar(50)
state  nvarchar(15)
zip  nvarchar(15)
rating  nvarchar(15)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
8.0
17
1       SQLCHAR       0       100      ","        1     lname      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100      ","        2     fname      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       15       ","        3     dob        SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       10       ","        4     age        SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       30       ","        5     chart#     SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       15       ","        6     visitdate  SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       20       ","        7     provider   SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       20       ","        8     office     SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR       0       20       ","        9     diag1      SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR       0       20       ","        10    cpt        SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR       0       30       ","        11    ssan       SQL_Latin1_General_CP1_CI_AS
12      SQLCHAR       0       20       ","        12    sex        SQL_Latin1_General_CP1_CI_AS
13      SQLCHAR       0       100      ","        13    address    SQL_Latin1_General_CP1_CI_AS
14      SQLCHAR       0       100      ","        14    city       SQL_Latin1_General_CP1_CI_AS
15      SQLCHAR       0       30       ","        15    state      SQL_Latin1_General_CP1_CI_AS
16      SQLCHAR       0       30       ","        16    zip        SQL_Latin1_General_CP1_CI_AS
17      SQLCHAR       0       30       "\r\n"     17    rating     SQL_Latin1_General_CP1_CI_AS

0
 
running32Author Commented:
Thank you
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now