Bulk Insert Missing Data Row

Question

Post reply

Bulk Insert Missing Data Row

giles.clapham

SSCrazy

Points: 2204
More actions
September 27, 2010 at 4:27 am

#227079

I'm tasked with importing a file into a server on a daily basis.
Currently they are using OpenRowSet in a script but occasionally it returns an error about a linked server (null) and reading up on that the only way to resolve it is to restart the SQL Server service, which I think is unacceptable.
The only option available to me is to adjust the script so I wanted to use Bulk Insert.
The data has a header row which is comma delimited but not text qualified. The data is comma delimited and columns 2 onwards are text qualified.
The problem is it always misses the first row of data out.
I’m using a Format File and running the following statement
Bulk Insert BulkInsert_Customer
from 'c:\Client\SampleCustomer.csv' with ( FORMATFILE='C:\Client\Format_Customer.fmt' ,FIRSTROW=2)
The format file is quite long but the gist of it is:
9.0
80
1 SQLCHAR 0 510 ",\"" 1 acct_id Latin1_General_CI_AS
2 SQLCHAR 0 510 "\",\"" 2 salutation Latin1_General_CI_AS
3 SQLCHAR 0 510 "\",\"" 3 full_name_1 Latin1_General_CI_AS
4 SQLCHAR 0 510 "\",\"" 4 full_name_2 Latin1_General_CI_AS
5 SQLCHAR 0 510 "\",\"" 5 name_last_first_mi Latin1_General_CI_AS
6 SQLCHAR 0 510 "\",\"" 6 company_name Latin1_General_CI_AS
…
80 SQLCHAR 0 510 "\"\r" 80 name2_else_name1 Latin1_General_CI_AS
If I delete the header row out and try it again it works fine (obviously FIRSTROW=1)
So I’m guessing the header row is somehow different. It is terminated the same as the other rows.
Any help is appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

check what is row delimiter in your header row, is it the same as for your daa rows?

Note: by default BCP uses (new line character) for row terminator you can try using \r (Carriage return)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

giles.clapham SSCrazy Points: 2204 More actions · Answer 2

Thanks for helping.

80 SQLCHAR 0 510 "\r" 80 name2_else_name1 Latin1_General_CI_AS

Gives me the following error:

Msg 4832, Level 16, State 1, Line 1

Bulk load: An unexpected end of file was encountered in the data file.

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)".

Which I'm not surprised or worried about.

The row terminator (crlf) for the header line looks to be the same as the data rows and i've determined that from opening it in Notepad++ and Word and looking at the hidden characters.

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 3

I don't think you can do this very easily using BULK INSERT because of the irregular first row. You can instead use OPENROWSET BULK and treat the first row as a special case. Try this - you can test by running the SELECT statement independently

--INSERT BulkInsert_Customer

SELECT

RIGHT(acct_id, CHARINDEX(CHAR(10), REVERSE(acct_id)) - 1) AS acct_id,

salutation,

full_name_1,

full_name_2,

name_last_first_mi,

company_name,

...

name2_else_name1

FROM OPENROWSET (BULK 'c:\Client\SampleCustomer.csv', FORMATFILE = 'C:\Client\Format_Customer.fmt', LASTROW = 1) AS Z

UNION ALL