December 20, 2011 at 9:41 am
I have been given a pipe delimited table to import into 2k5. I have done this many times before but for some reason this one is not working. The only thing different is the row delimiter wich is usually a cr with a lf
The structure is 82 columns with a pipe delimiter between each. The row delimiter is hex 0A or new line
The data seems to be well formed...checked it many times.
c1|c2| ..... |c81| c82 (char 10 at end of each line.)
...
I am using ....
bulk insert myDB.dbo.myTable from 'C:\pipeDelimited.txt'
with (FIELDTERMINATOR ='|',
ROWTERMINATOR ='')
and get
Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 82. Verify that the field terminator and row terminator are specified correctly.
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)".
I have trield \x10, , \r, \r, \r and every variation I could think of for the row delimiter and all throw the same error.
The table exists and is 82 columns of varchar(255)
Please help before I find a bridge to jump off of.
December 21, 2011 at 2:44 am
Hi Charlie,
I haven't actually used the bulk insert command (I generally use SSIS to import files into SQL), but my guess is that something is wrong with the source file.
Are you able to load it into Excel to see if any of the rows don't meet the criteria?
Just a thought,
Graham
December 21, 2011 at 2:52 am
Try *r*n or *n.
Note: SSC removes back-slash(\)n from the post so replacing '\' with '*'
December 21, 2011 at 3:09 am
See the BOL. There is a good example for your exact requirement.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply