September 27, 2010 at 4:27 am
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.
September 27, 2010 at 5:14 am
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)
September 27, 2010 at 5:30 am
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.
September 27, 2010 at 10:29 am
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
SELECT
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', FIRSTROW = 2) AS Z
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply