now csv insert fails, why? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

now csv insert fails, why?

Author

Topic

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 05:20:38

Dear SQL users,

I'd like to ask for your support, I'm new in SQL programming and so far I've followed internet instructions to load CSV tables into SQL Server 2008. So far so good, but now I'm getting the following error:


Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row 1, column 13. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 3
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

(0 row(s) affected)

My Query code is:


--create table using the database already created
use database
go

--create table to insert data
create table dbo.nav
(
nav_id int identity(1,1) primary key,
cmp int,
sx_dd float,
sy_dd float,
gx_dd float,
gy_dd float,
sx_utm float,
sy_utm float,
gx_utm float,
gy_utm float,
campaign_id varchar(25),
survey_id varchar(25),
profile_id varchar(100)
)
go

--bulk insert the data from the csv file
bulk
insert nav
	from 'd:\database\tables\nav.csv'
	with
		(
		fieldterminator = ';',
		firstrow = 2
		)
go

--check the data in the table
select *
from nav
go

The table I'm trying to load is as follows:


cmp;sx_dd;sy_dd;gx_dd;gy_dd;sx_utm;sy_utm;gx_utm;gy_utm;campaign_id;survey_id;profile_id
1000;-80.7179;-8.35613;-80.7189;-8.35658;531060;9.07632e+06;530950;9.07627e+06;geopeco;mcsl;geo09-9
1001;-80.7176;-8.35604;-80.7190;-8.35658;531093;9.07633e+06;530939;9.07627e+06;geopeco;mcsl;geo09-9
1002;-80.7176;-8.35604;-80.7188;-8.35658;531093;9.07633e+06;530961;9.07627e+06;geopeco;mcsl;geo09-9
1003;-80.7174;-8.35595;-80.7187;-8.35649;531115;9.07634e+06;530972;9.07628e+06;geopeco;mcsl;geo09-9
1004;-80.7174;-8.35595;-80.7187;-8.35649;531115;9.07634e+06;530972;9.07628e+06;geopeco;mcsl;geo09-9
1005;-80.7174;-8.35595;-80.7185;-8.35640;531115;9.07634e+06;530994;9.07629e+06;geopeco;mcsl;geo09-9
1006;-80.7172;-8.35586;-80.7185;-8.35640;531137;9.07635e+06;530994;9.07629e+06;geopeco;mcsl;geo09-9
1007;-80.7172;-8.35586;-80.7184;-8.35640;531137;9.07635e+06;531005;9.07629e+06;geopeco;mcsl;geo09-9
....

This table has 170613 rows and 12 columns.

Please any hints will be very grateful.

Best regards,

Gery

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-05-17 : 05:27:43

specify a rowterminator as well, you've probably got a funny character as the row terminator, it will usually be /n, but depending on where the file is produced, it can be something else (you may need a text editor that shows all characters to see it).

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 07:26:28

Thanks Rick for your answer, I did your suggestion but I'm still getting an error message:


Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row 1, column 13. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 3
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

(0 row(s) affected)

About the termination character, I check the CSV files I have and found that the one I'm trying to load here is "ASCII text" and the other ones are "ASCII text, with CRLF terminations". There is one with "ASCII text, with too long lines, with CRLF terminations". The first one, "ASCII text" was created in linux, the other ones were created in Excel (windows).

Any idea about this?

Thanks,

Gery

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 08:47:28

What did you try as the row terminator? Usually it can be CR or LF (ascii 13 or 10) or both (which is the default that SQL expects).

If you have a text editor that can open the file in binary mode, you can inspect it to see what the row terminator character is and then program accordingly.

Alternatively, just try the combinations. In your code, replace the bulk insert with what I have below, and try char(13) or char(10).


DECLARE @row_terminator CHAR;
SET @row_terminator = CHAR(13); -- or char(10)

DECLARE @stmt NVARCHAR(2000);;
SET @stmt = '
	bulk
	insert nav
		from ''d:\database\tables\nav.csv''
		with
			(
			fieldterminator = '';'',
			firstrow = 2,
			ROWTERMINATOR='''+@row_terminator+'''
			)'
exec sp_executesql @stmt;

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 10:23:44

thanks for your answer sunitabeck, I tried your suggestion and got new error messages:

with char(10)


Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34468, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34469, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34470, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34471, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34472, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34473, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34474, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34475, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34476, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34477, column 13 (profile_id).
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34478, column 13 (profile_id).
Msg 4865, Level 16, State 1, Line 2
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

with char(13)


Msg 4866, Level 16, State 8, Line 2
The bulk load failed. The column is too long in the data file for row 1, column 13. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 2
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

why the difference? and why is so difficult? perhaps I can send you my file and you can test it, could you?, I really don't know why is so complicated...

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 10:24:44

also, it has something to do the data type? I'm setting everything as nvarchar(100)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 10:49:32

This means that your row terminator is char(10).

The error message you are getting is because of the identity column in the destination table. You have 13 columns in the table, but only 12 in the file. You have 2 options here:

1. change the input file to include an integer number for use by the identity column and then import. When you import, you can specify that the values you provide should be inserted by specifying the KEEPIDENTITY option. If you don't specify KEEPIDENTITY option, the import process will still read the numbers, but not insert them into the table - instead it will insert auto-generated identity values.

2. Use a format file to specify that you do not want the identity column to be inserted in the file.

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 10:53:15

It worked! I used your suggestion sunitabeck but removed the line "nav_id int identity(1,1) primary key," from my code. However, I'm getting repeated rows (similar from the beginning of the file) at the end of the file, it is weird that. Anyway, how can I put an ID column based on automatically generated numbers?

Also, the data type for numbers should be nvarchar(100)? or float?

Thanks,

Here is my current code (the one recently worked):


--create table using the database already created
use database
go

--create table to insert data
create table dbo.navigation
(
cmp nvarchar(25),
sx_dd nvarchar(100),
sy_dd nvarchar(100),
gx_dd nvarchar(100),
gy_dd nvarchar(100),
sx_utm nvarchar(100),
sy_utm nvarchar(100),
gx_utm nvarchar(100),
gy_utm nvarchar(100),
campaign_id nvarchar(25),
survey_id nvarchar(25),
profile_id nvarchar(100)
)
go

--bulk insert the data from the csv file
DECLARE @row_terminator CHAR;
SET @row_terminator = CHAR(10); -- or char(10)

DECLARE @stmt NVARCHAR(2000);;
SET @stmt = '
	bulk
	insert navigation
		from ''d:\database\tables\navigation.csv''
		with
			(
			fieldterminator = '';'',
			firstrow = 2,
			ROWTERMINATOR='''+@row_terminator+'''
			)'
exec sp_executesql @stmt;

--check the data in the table
select *
from navigation
go

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 11:03:55

Usually the easiest thing is to create a staging table which is identical to the real table except for the identity column, import the data into the staging table just like you did now, and then copy it to the real table using

insert into dbo.nav select * from dbo.nav_staging;

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 11:16:13

Thanks sunitabeck! u are the best! one more question, could you write the code I need to add to my current code to get the autogenerated numbers in the ID column? because of I create these csv files in linux I'll just add an extra column to the original file.

Thanks again!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 11:26:24

You don't really need any additional code. You will need to create the two tables, if you have not already, as follows:


create table dbo.navigation_staging
(
   cmp nvarchar(25),
   sx_dd nvarchar(100),
   -- other columns here
)
GO

create table dbo.navigation
(
   nav_id INT NOT NULL IDENTITY(1,1),
   cmp nvarchar(25),
   sx_dd nvarchar(100),
   -- other columns here
)
GO

The only difference is the identity column in the dbo.navigation table. Then, each time you want to import, you would do the following:


TRUNCATE TABLE dbo.navigation_staging
-- bulk insert statements here. You should insert into the dbo.navigation_staging staging table.
INSERT INTO dbo.navigation SELECT * FROM dbo.navigation_staging;

That last insert statement will automatically insert the auto-generated id's along with the data you imported into the dbo.navigation table.

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 11:51:37

thanks sunitabeck, I'll try your solution.

I just tried to import another table, similar to the navigation CSV, using your code but this new table is 3 GB in size. After 40 minutes, I received the following message:

[code]
Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object 'dbo.bathymetry' in database 'database' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
[\code]

I have 160 GB free space in C where SQL server 2008 is installed, and 122 GB free space in D where this new table is stored. Also I'm running the SQL server in Windows 7 64-bits, 8 GB RAM, Intel i5 3.2 GHz.

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 12:05:52

Is autogrowth enabled for the database? You can see this by right clicking on the database name in object explorer in SSMS and selecting properties->files. You can also see how much space is being used.

Also, probably there are other/better ways to see this, but you can see how much space the database files have and how much of it is being used by right-clicking on the database in object explorer and selecting tasks->shrink->files. (Don't click OK to actually shrink the files in the dialog that pops up - just look at the window to see how much space is available).

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 12:23:48

I'm not sure about what I'm actually seeing, so I'm attaching a PNG to see if you can tell me what to do here. The autogrowth function is enabled but I don't undertand what should I do, sorry

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 12:34:22

The PNG file did not come through. In any case, I am close to being illiterate when it comes to database files and hardware. Can you post the error message you posted previously in a new thread and ask for advice? People who are experts at this type of thing may not look at this thread because seeing the number of responses, they would think that the question presented has been answered.

Gery
Starting Member

16 Posts

Posted - 2011-05-17 : 12:36:51

that's a great idea, thank you for everything, I will do a new post about this issue. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources