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 |
|
ryoka012
Starting Member
20 Posts |
 Posted - 2012-09-27 : 02:59:48
|
| Hi can any one help me with bulk insertBelow is the scenario i have the below codeBULK INSERT CustomerInfo FROM 'C:\CustInfo\Apo(ALL).csv'WITH (FIELDTERMINATOR='\t',rowterminator='\n')When using FIELDTERMINATOR=',' i can upload the file.But using FIELDTERMINATOR=',' with a value of a cell is equal to "4309 S Morgan Street, Chicago, IL, 60609, United States" it creates a 5 column instead a combine column of 1.Using FIELDTERMINATOR='\t' Produce an error belowMsg 4832, Level 16, State 1, Line 2Bulk load: An unexpected end of file was encountered in the data file.Msg 7399, Level 16, State 1, Line 2The 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 2Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".Please really desperate on this.Also importing using task for csv file it gives me an error. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-27 : 06:58:22
|
| Using \t as the field separator will not work, because that is not your field separator, and the error message indirectly is indicating that.The problem that you see when you use comma as the field separator is a perennial problem with comma-separated files. If the data itself has the commas - which it often does, a piece of software cannot tell whether a given comma is a field separator, or part of the data. So it assumes that every comma is a field separator - that is why you are seeing additional columns.To get around this problem, people use escape characters, usually double quotes. However, as far as I know, bulk insert is not sophisticated enough to make use of that.So your choices as I see it are:a) Get a new file with a different field separator - pipe or tab, for example.b) If the csv file that you have now does use escape characters (i.e., cells that have comma as part of the data are enclosed in double-quotes), use a different method for importing - for example, SSIS or even Import/Export Wizard.c) If the If the csv file that you have now does use escape characters (i.e., cells that have comma as part of the data are enclosed in double-quotes), use bulk insert to import the data into a single column staging table and use some scripts to parse the data accounting for the presence of commas as part of the data and the escape characters. I don't have a script ready for doing this, and it takes some efforts to create one. |
 |
|
|
ryoka012
Starting Member
20 Posts |
Posted - 2012-09-27 : 07:54:29
|
| Thanks for your reply i have change my delimiter to tab. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-27 : 09:13:04
|
| Glad you were able to. Most of the time I get data from our vendors and then it is a struggle to get them to change the delimiter or use escape characters - one vendor in particular, who shall remain unnamed :) |
|
|
|
|
|
|
|
|
Error using Bulk insert