CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008bulkinsert
Tippu
Tippu

Reputation: 1261

Issue with bulk insert

I am trying to insert the data from this link to my SQL server https://www.ian.com/affiliatecenter/include/V2/CityCoordinatesList.zip

I created the table

CREATE TABLE [dbo].[tblCityCoordinatesList](
    [RegionID] [int] NOT NULL,
    [RegionName] [nvarchar](255) NULL,
    [Coordinates] [nvarchar](4000) NULL
) ON [PRIMARY]

And I am running the following script to do the bulk insert

BULK INSERT tblCityCoordinatesList
FROM 'C:\data\CityCoordinatesList.txt' 
WITH 
( 
    FIRSTROW = 2, 
    MAXERRORS = 0, 
    FIELDTERMINATOR = '|', 
    ROWTERMINATOR = '\n'
)

But the bulk insert fails with following error

Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

When I google, I found several articles which says the issue may be with RowTerminator, but I tried everything like \n\r, \n etc, but nothing is working.

Could anyone please help me to insert this data into my database?

Upvotes: 26

Views: 58069

Answers (7)

Frank Geo
Frank Geo

Reputation: 1

This is an end of line issue.
Use Advanced text editor to show end of line markers to see what you got.
Change this to what you need depending on your environment.

Upvotes: -2

mohamed ali
mohamed ali

Reputation: 1

**Filename** = C:\Users\hp\Desktop\dataset\canvas_size.csv
**File format**: 'CSV'
**Data shown as follows**

size_id,width,height,label
20,20,,20" Long Edge
24,24,,24" Long Edge
30,30,,30" Long Edge
36,36,,36" Long Edge
40,40,,40" Long Edge
48,48,,48" Long Edge
56,56,,56" Long Edge
1522,15,22,15" x 22"(38 cm x 56 cm)
1618,16,18,16" x 18"(41 cm x 46 cm)
1620,16,20,16" x 20"(41 cm x 51 cm)

**create table script**
create table canvas_size(size_id int,width int,height int,label varchar(200));

**execute select query of the table, it shows no rows.**
select * from canvas_size;

**MS SQL server bulk load script**
BULK INSERT canvas_size FROM 'C:\Users\hp\Desktop\dataset\canvas_size.csv'
   WITH (
        FORMAT = 'CSV',
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n',
        FIRSTROW = 2
        );
**you will get below error**
Msg 4879, Level 16, State 1, Line 18
Bulk load failed due to invalid column value in CSV data file C:\Users\hp\Desktop\dataset\canvas_size.csv in row 2, column 4.
Msg 7399, Level 16, State 1, Line 18
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 18
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

**Solution:**
problem is if you look at the column name label data has double quotes, bulk load script what i wrote was not correct.

**Solution to modify the bulk load script**
BULK INSERT canvas_size FROM 'C:\Users\hp\Desktop\dataset\canvas_size.csv'
   WITH (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n',
        FIRSTROW = 2,
        DATAFILETYPE = 'char'
        );

data is successfully loaded into table (canvas_size)

select * from canvas_size;
size_id width   height  label
20  20  NULL    20" Long Edge
24  24  NULL    24" Long Edge
30  30  NULL    30" Long Edge
36  36  NULL    36" Long Edge
40  40  NULL    40" Long Edge
48  48  NULL    48" Long Edge
56  56  NULL    56" Long Edge
1522    15  22  15" x 22"(38 cm x 56 cm)
1618    16  18  16" x 18"(41 cm x 46 cm)
1620    16  20  16" x 20"(41 cm x 51 cm)

Upvotes: 0

MDrake42
MDrake42

Reputation: 31

I had this on SQL2019 when the FORMAT='CSV' option was used, and there was a comma on the end of each line in the source file. So the table your BULK inserting into needed to have an extra dummy field to cater for the fact each record has essentially a blank field in the source file.

!CSV file example

Upvotes: 3

Erik Mattson
Erik Mattson

Reputation: 151

I get the same error, probably from the file encoding problem. I fixed it by opening the problem CSV file using Notepad++, select everything and copy to clipboard. Next, create a new text file (making sure it has the CSV file extension), open it using Notepad++, then paste the text to the new file. Save and close all files. You should be able to successfully load the new CSV file into the SQL server.

Upvotes: 0

pablo.bueti
pablo.bueti

Reputation: 151

I got the same error message, and as you had mention, it was related to unexpected line ending. In my case the line ending was specified in a fmt file as a Windows Line ending (CRLF), written as \r\n, and the data file to process has a Mac classic one (CR).

I solved it with an editor that can show the current line ending and change it. I used EditPad Lite wich shows the opened file line ending in the bottom bar and pressing it allow to replace with the expected one.

Upvotes: 1

user5480949
user5480949

Reputation: 1688

This can also happen if the number of columns mismatch between the table and the imported file

Upvotes: 29

user339597
user339597

Reputation:

Try ROWTERMINATOR = '0x0a'. it should work.

Upvotes: 73

Related Questions