Pages

Search This Blog

Monday, October 1, 2012

The bulk load failed. The column is too long in the data file for row 1, column x. Verify that the field terminator and row terminator are specified correctly.


Hit error below when try to execute bulk insert script

Msg 4866, Level 16, State 1, Line 3
The bulk load failed. The column is too long in the data file for row 1, column 6. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 3
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 3
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

(0 row(s) affected)

Script that hit error:


CREATE TABLE GeoIPCountryWhois2
(
[startIp] [nvarchar](50) NULL,
[endIp] [nvarchar](50) NULL,
[startIpNum] [nvarchar](50) NULL,
[endIpNum] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[Country] [nvarchar](150) NULL
) ON [PRIMARY]
GO


BULK INSERT GeoIPCountryWhois2
FROM 'C:\GeoIPCountryWhois.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '/n'

)
GO
--Check the content of the table.
SELECT *
FROM GeoIPCountryWhois2
GO

Fix:



CREATE TABLE GeoIPCountryWhois2
(
[startIp] [nvarchar](50) NULL,
[endIp] [nvarchar](50) NULL,
[startIpNum] [nvarchar](50) NULL,
[endIpNum] [nvarchar](50) NULL,
[CountryCode] [nvarchar](50) NULL,
[Country] [nvarchar](150) NULL
) ON [PRIMARY]
GO


BULK INSERT GeoIPCountryWhois2
FROM 'C:\GeoIPCountryWhois.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a'
)
GO
--Check the content of the table.
SELECT *
FROM GeoIPCountryWhois2
GO

1 comment:

Unknown said...

That worked for my issue too. Thanks for the hard work!