BULK INSERT skipping first data row
-
Hi.
I've got a data file with a header row of column names. It's not importing the header row like I'd expect, but it's also skipping line 2, the first data row and starting on line 3. I'm completely boggled.
Ideas??
My bulk insert command is:
SELECT @nvchParamDef2 = N'@rcOUT INT OUTPUT, @errOUT INT OUTPUT'
SELECT @nvchDynamicSQL = '
BULK INSERT ' + @vchTableName + '
FROM "' + @gFTPDestinationPath + '\' + @vchFilename + '"' + '
WITH (
CODEPAGE = ''RAW'',
FIRSTROW = 2,
FORMATFILE = ''' + @gBulkInsertFormatFile + ''',
TABLOCK
) SELECT @rcOUT = @@ROWCOUNT, @errOUT = @@ERROR'
EXEC sp_executesql @nvchDynamicSQL, @nvchParamDef2, @rcOUT = @oiRowcount OUTPUT, @errOUT = @oiErrorID OUTPUT
-
It may be treating the 1st data row as FIRSTROW. Have you tried FIRSTROW = 1?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
The only time I've seen this happen is when the row with the column headings has a row delimiter that doesn't match the row delimiter in the format file. It may not be the answer, but that's where I'd look first.
And then again, I might be wrong ...
David Webb -
Yes, and I get the following error:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 1 (Ban).
It tries to read the header row, row 1.
Setting FIRSTROW = 2, it tries to read data row 2, row 3.
What gives??
-
In the data rows, there are spaces surrounding the commas. The header row using commas as the delimiter but has no spaces.
My format file looks like this:
8.0
28
1 SQLCHAR 0 0 " , " 1 Ban SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 " , " 2 subscriber_number SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 " , " 3 imsi SQL_Latin1_General_CP1_CI_AS
...
27 SQLCHAR 0 0 " , " 26 bc_month SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 0 "\r\n" 28 bc_year SQL_Latin1_General_CP1_CI_AS
Is there another way to tell BULK INSERT to ignore spaces, if they exist, around the commas?
-
Hmmmmm...
I wouldn't think that the column delimiters mattered as much as the row delimiter. Does the row that has the column headings end with a '\r\n'?
And then again, I might be wrong ...
David Webb -
Yes. It had a bunch of extra spaces after the last column header name, but I even removed those and put in a carriage return of my own.
This is very puzzling.
-
If I add a space on either side of the comma in the header row, it works. I won't be able to modify the text files coming in though.
Is there a way to set up the format file to handle a header row with no spaces around the comma "," but also the data rows with spaces around the comma " , "??
-
So, if you look at that first row in a hex editor it ends in '0D0A'?
That is puzzling. Does it behave the same way if you use the bcp command line utility?
And then again, I might be wrong ...
David Webb -
Yes, it ends in 0D0A.
-
Well, that sorta puts the whole thing into 'Let's try this and see ...' mode. I'd have to admit that I haven't seen this before.
I'd add the ROWTERMINATOR = '\r\n' and FIELDTERMINATOR= ' , ' clauses and remove the format file as a next step.
And then again, I might be wrong ...
David Webb -
Argh... BCP does the same thing.
-
I need the format file to slightly transform the data. And since these files are GBs I can't really use a staging table.
-
This is annoying. You'd think that if I tell it to start reading on row 2 it would ignore row 1 altogether. Who cares if there aren't spaces around the commas there??
Not sure how I'm going to make this work....
-
Eventually had to go with ye olde "INSERT INTO... SELECT FROM" with "ALTER DATABASE <name> SET RECOVERY BULK_LOGGED" instead of BULK INSERT with a format file.
It works but's probably slower than BULK INSERT or bcp.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply