SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 bulk insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

82 Posts

Posted - 02/18/2014 :  16:45:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/18/2014 :  16:48:26  Show Profile  Reply with Quote
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

82 Posts

Posted - 02/18/2014 :  16:57:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/18/2014 :  17:10:26  Show Profile  Reply with Quote
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

82 Posts

Posted - 02/18/2014 :  17:12:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/18/2014 :  17:14:04  Show Profile  Reply with Quote
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

Edited by - TG on 02/18/2014 17:17:30
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/18/2014 :  17:28:31  Show Profile  Reply with Quote
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

Edited by - TG on 02/18/2014 17:35:23
Go to Top of Page

kt
Yak Posting Veteran

82 Posts

Posted - 02/18/2014 :  17:59:38  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000