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
 Error using Bulk insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ryoka012
Starting Member

Philippines
20 Posts

Posted - 09/27/2012 :  02:59:48  Show Profile  Reply with Quote
Hi can any one help me with bulk insert
Below is the scenario
i have the below code

BULK 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 below

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot 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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/27/2012 :  06:58:22  Show Profile  Reply with Quote
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.
Go to Top of Page

ryoka012
Starting Member

Philippines
20 Posts

Posted - 09/27/2012 :  07:54:29  Show Profile  Reply with Quote
Thanks for your reply i have change my delimiter to tab.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/27/2012 :  09:13:04  Show Profile  Reply with Quote
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 :)
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.17 seconds. Powered By: Snitz Forums 2000