?
Solved

bulk insert using format file

Posted on 2006-05-15
4
Medium Priority
?
379 Views
Last Modified: 2008-02-01
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
Comment
Question by:running32
  • 2
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16682632

can you post the tabe structure (including collation  definitions )
0
 

Author Comment

by:running32
ID: 16682810
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16682935
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
 

Author Comment

by:running32
ID: 16684172
Thank you
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 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