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
 bulk insert

Author  Topic 

kt
Yak Posting Veteran

88 Posts

Posted - 2014-02-18 : 16:45:56
Hi,

I have the csv file with format below
76;0;76;TEST PASS;Time Completed;319.852292060852;0.177007354795933;;119.95;WATER;;;HIGH

I try the bulk insert and got an error:

===============
Msg 4866, Level 16, State 1, 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)".

===============
can anyone please help?
thx
===
bulk insert [dbo].csv
from 'C:\ColdFusion9\wwwroot\cfusionmx\cfapps\app\Report.csv'
with (fieldterminator = ',', rowterminator = '\n')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-18 : 16:48:26
your sample row uses a semicolon (;) as a fieldterminator but your bulk insert command specifies a comma (,)

Be One with the Optimizer
TG
Go to Top of Page

kt
Yak Posting Veteran

88 Posts

Posted - 2014-02-18 : 16:57:15
already corrected but still got the same error, any other suggestions?

bulk insert [dbo].csv
from 'C:\ColdFusion9\wwwroot\cfusionmx\cfapps\app\Report.csv'
with (fieldterminator = ';', rowterminator = '\n')
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-18 : 17:10:26
post the structure of dbo.csv.
Does your source file have column headers? If so specify FIRSTROW = 2

Be One with the Optimizer
TG
Go to Top of Page

kt
Yak Posting Veteran

88 Posts

Posted - 2014-02-18 : 17:12:03
no column header. here are some source

1;Test;;;;3.100681;3.088473;;50.35;6 - AIR SEAT P;40.0;120;5.0;13/07/2012;12:55:42;
2;Test;;;;3.088473;3.064058;;91.15;6 - AIR SEAT P;40.0;120;5.0;13/07/2012;12:59:35;
3;Test;;;;3.112888;3.088473;;45.0;6 - AIR SEAT P;40.0;120;5.0;13/07/2012;13:02:59;
4;Test;;;;7.641835;7.507553;;70.55;3 - WATER SEAT P;40.0;120;5.0;13/07/2012;13:08:43;
5;Test;;;;20.43519;17.70074;;105.05;4 - WATER SEAT N;315.0;120;5.0;13/07/2012;13:18:38;
6;Test;;;;3.064058;3.039644;;119.95;6 - AIR SEAT P;40.0;120;5.0;13/07/2012;15:42:37;
7;Test;;TEST PASS;Time Completed;3.015229;3.015229;;119.95;7 - AIR SEAT N;40.0;120;5.0;13/07/2012;15:47:38;
8;Test;;TEST PASS;Time Completed;0.0;0.0;;103.2;3 - WATER SEAT P;315.0;120;5.0;13/07/2012;15:55:29;
9;Test;;TEST PASS;Time Completed;17.89605;15.78417;;119.95;3 - WATER SEAT P;315.0;120;5.0;13/07/2012;16:14:27;
10;Test;;TEST PASS;Time Completed;24.10962;23.5847;;119.95;4 - WATER SEAT N;315.0;120;5.0;13/07/2012;17:57:34;
11;Test;;TEST PASS;Time Completed;26.40461;25.74541;;119.95;4 - WATER SEAT N;315.0;120;5.0;13/07/2012;18:03:39;
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-18 : 17:14:04
and this?
quote:
post the structure of dbo.csv.


EDIT:
and please script the table and post that so we can easily recreated it.
ie: <right click table> | script table | as create | to clipboard

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-18 : 17:28:31
Just noticed this error:
quote:
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

From where are you running this BULK INSERT command? Try it first in a query window directly on the target server.

EDIT:
and another observation is that the line should not end with a <fieldTerminator> unless there is an additional blank column. So all those trailing semicolons may be a problem.

Be One with the Optimizer
TG
Go to Top of Page

kt
Yak Posting Veteran

88 Posts

Posted - 2014-02-18 : 17:59:38
not sure what you meant, but here is the script for my testing
USE [rept]
GO

INSERT INTO [dbo].[csv]
([one]
,[two]
,[three]
,[four]
,[five]
,[six]
,[seven]
,[eight]
,[nine]
,[ten]
,[elveven]
,[twelve]
,[thirteen]
,[fourteen]
,[fifteen])
VALUES
(<one, varchar(50),>
,<two, varchar(50),>
,<three, varchar(50),>
,<four, varchar(50),>
,<five, varchar(50),>
,<six, varchar(50),>
,<seven, varchar(50),>
,<eight, varchar(50),>
,<nine, varchar(50),>
,<ten, varchar(50),>
,<elveven, varchar(50),>
,<twelve, varchar(50),>
,<thirteen, varchar(50),>
,<fourteen, varchar(50),>
,<fifteen, varchar(50),>)
GO


Go to Top of Page
   

- Advertisement -