1

I'm trying a bulk insert of a csv file into a SQL table using BCP but can't fix this error: "The column is too long in the data file for row 1, column 2. Verify that the field terminator and row terminator are specified correctly." - Can anyone help please?

Here's my SQL code:

BULK INSERT UKPostCodeStaging
FROM 'C:\Users\user\Desktop\Data\TestFileOf2Records.csv'
WITH (
DATAFILETYPE='char',
FIRSTROW = 1,
FORMATFILE = 'C:\Users\User\UKPostCodeStaging.fmt');

Here's my test data contained in TestFileOf2Records.csv:

"HS1 2AA",10,14,93,"S923","","S814","","S1213","S132605"
"HS1 2AD",10,14,93,"S923","","S814","","S1213","S132605"

And here's my BCP file that I have attempted to edit appropriately:

10.0
11
1   SQLCHAR  0  0  "\""      0  FIRST_QUOTE                 SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR  8  0  "\","     1  PostCode                    SQL_Latin1_General_CP1_CI_AS
3   SQLINT   1  0  ","       2  PositionalQualityIndicator  ""
4   SQLINT   1  0  ","       3  MetresEastOfOrigin          ""
5   SQLINT   1  0  ",\""     4  MetresNorthOfOrigin         ""
6   SQLCHAR  8  0  "\",\""   5  CountryCode                 SQL_Latin1_General_CP1_CI_AS
7   SQLCHAR  8  0  "\",\""   6  NHSRegionalHACode           SQL_Latin1_General_CP1_CI_AS
8   SQLCHAR  8  0  "\",\""   7  NHSHACode                   SQL_Latin1_General_CP1_CI_AS
9   SQLCHAR  8  0  "\",\""   8  AdminCountyCode             SQL_Latin1_General_CP1_CI_AS
10  SQLCHAR  8  0  "\",\""   9  AdminDistrictCode           SQL_Latin1_General_CP1_CI_AS
11  SQLCHAR  8  0  "\"\r\n"  10 AdminWardCode               SQL_Latin1_General_CP1_CI_AS

Any ideas where I am going wrong? thanks

Improve this question
2
  • quote qualifier make bulk insert a hell job this way.load the data using , as field terminator(you dont need to use format file in this case) and then use replace() function to remove quotes from the data. Commented Aug 30, 2012 at 16:15
  • Thanks - I have done that to get the data loaded this time. However it would be good to know how to get the BCP file right as I don't want to apply a manual fix every time the data is updated. Commented Aug 31, 2012 at 9:04
Related questions
1
What bcp format file that I'm gonna used to insert 59 columns?
0
Import CSV with data sections with BCP,BULK Insert or openrowdataset
4
Import into SQL Server with format file and bulk insert
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.