By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,442 Members | 1,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,442 IT Pros & Developers. It's quick & easy.

Load csv file into a table

P: 3

When I try to load data into a table using a csv file I get below errors.

Expand|Select|Wrap|Line Numbers
  1. bulk insert client_key
  2. from 'T:\CLIENT_KEY.txt'
  3. WITH (
  4.     FIELDTERMINATOR =',',
  5.     ROWTERMINATOR = '\r\n',
  6.     DATAFILETYPE  = 'widenative')
Msg 4866, Level 16, State 4, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Sample of file, first 2 lines

Expand|Select|Wrap|Line Numbers
  1. 56QS2LttX5P3DVN5WQVcOWyk5Y=,vIXIFDGBDVK62m2Ka6/yuPLCJMc=,2010-07-01 00:00:00.000,2019-09-09 00:00:00.000,NULL,NULL,NULL,NULL,NULL
  2. 5A7OUtuTDodcn59DhimXhpCFRQw=,7D751838D2D3DVZ5Q97A22FB6945F1FE559578681D0478815D3436A7DD1B14352,2009-01-01 00:00:00.000,2019-01-01 00:00:00.000,NULL,NULL,NULL,NULL,key2
Thank you all in advance.
Sep 26 '12 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 10K+
P: 12,430
I think the error message is fairly clear. It's saying the first row, first column in the text file is too long to fit in the table field it's trying to insert into.
Sep 26 '12 #2

P: 3
But the value I a mtrying to insert is very small.
Below is the create table stmt.

Expand|Select|Wrap|Line Numbers
  2.     [CLNT_REF_ID] [varchar](256) NOT NULL,
  3.     [CLIENT_KEY] [varchar](256) NULL,
  4.     [START_DATE] [datetime] NULL,
  5.     [END_DATE] [datetime] NULL,
  6.     [USM_CREATOR] [varchar](10) NULL,
  7.     [USM_CREATE_TS] [datetime] NULL,
  8.     [USM_UPDATOR] [varchar](50) NULL,
  9.     [USM_UPDATE_TS] [datetime] NULL,
  10.     [ENC_KEY] [varchar](50) NULL
  11. ) ON [PRIMARY]
Sep 26 '12 #3

Expert Mod 10K+
P: 12,430
Are you using SQL Server 2005 before cumulative update 4? Because apparently it's a known bug.

It suggests using ASCII instead of Unicode as a workaround.
Sep 26 '12 #4

P: 3
Rabbit - but my version of SQL Serverin which I am trying to insert is 2008 R2 not 2005.

And thank you next time i'll make sure I use code snippets.
Sep 27 '12 #5

Expert Mod 10K+
P: 12,430
I would try using ASCII anyways to see if that resolves the issue. Especially since your field in the table is ASCII but you're using unicode to read your text file.
Sep 27 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.