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 |
|
sek
Starting Member
27 Posts |
 Posted - 2009-06-30 : 10:57:32
|
| hi,i am new to sql server.i need to insert data from csv file into sql server 2000.my data in the csv file looks like below."3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","541448800000019637",55147,"Sibelgas Noord","Utility"you can see that my data in the columns is also contain comma(,) like below."Diegem, BEL (3M Belgium N.V. - Diegem, BEL)"i am using the following bulk insert command.BULK INSERT Acct_Template_JUN30 FROM 'C:\\shared\\master data\\Acct_Template_JUN30' WITH ( FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )the problem what i am getting is data is not importing exactly what i want as data also contains commas.when i kept in the FIELDTERMINATOR = '"",""', like this i am getting the following error.Server: Msg 4863, Level 16, State 1, Line 1Bulk insert data conversion error (truncation) for row 6, column 2 (Site Ref No).Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.The statement has been terminated.i want the data from the file to be inserted correctly and also with out any double quotes(") into the columns of the database table.how to do that??can anybody please help me????thanks in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-30 : 12:45:25
|
| seems like Acct_Template_JUN30 table's 6th field doesnt have enough length to hold the value coming which is causing this truncation error. |
 |
|
|
sek
Starting Member
27 Posts |
Posted - 2009-06-30 : 23:02:53
|
| Hi,when i do the same thing through wizard using import data option i am not getting any problem.i need to do it programmatically using query.i dont want to do file manipulation by changing the delimitor as my no of records are more than one lakh.is there any option other than bulk insert to do this?thanks in adavance. |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-01 : 06:10:20
|
| hiCan u change the FIELDTERMINATOR = '|' instead of FIELDTERMINATOR = ',' |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-01 : 06:11:38
|
| Data should be like this3M Belgium N.V.|4671|Diegem, BEL (3M Belgium N.V. - Diegem, BEL)|Hermeslaan |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-01 : 06:45:58
|
As OP said, he don't want to do file manipulation.Sorry not sure if it is possible because never used but maybe you can do:FIELDTERMINATOR = '",' (1 single quote, 1 double quote, 1 comma, 1 single quote)Fred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-01 : 06:51:46
|
| This data "3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan that column in between comma is there. but he has to use delimit comma.i want to know is it possiblebyraj...:) |
|
|
|
|
|
|
|
|
bulk insert problem