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.
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 1The 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 1The 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 1Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".===============can anyone please help?thx===bulk insert [dbo].csvfrom '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 OptimizerTG |
|
|
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].csvfrom 'C:\ColdFusion9\wwwroot\cfusionmx\cfapps\app\Report.csv'with (fieldterminator = ';', rowterminator = '\n') |
|
|
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 = 2Be One with the OptimizerTG |
|
|
kt
Yak Posting Veteran
88 Posts |
Posted - 2014-02-18 : 17:12:03
|
no column header. here are some source1;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; |
|
|
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 clipboardBe One with the OptimizerTG |
|
|
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 OptimizerTG |
|
|
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 testingUSE [rept]GOINSERT 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 |
|
|
|
|
|
|
|