BULK failed if first character is the separator

MAHE, David 0 Reputation points
2023-07-19T20:56:41.6466667+00:00

Hi,

I want to BULK a CSV into a table with all fields of nvarchar NULL.

when the lines are

1;something;other;end

it's OK

if the separator begin the lines (the first field is nothing, null)

;someone;any;begin

it failed with the error

Bulk ERROR_NUMBER 7330

Bulk ERROR_MESSAGE Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Any idea ?

Thank's

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,240 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,561 Reputation points
    2023-07-19T21:07:09.6333333+00:00

    Hi @MAHE, David,

    *.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. Or data elements are missing completely.

    The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, and enforce data quality.

    0 comments No comments

  2. MAHE, David 0 Reputation points
    2023-07-20T08:35:50.72+00:00

    Thank you all for quickly respond !

    I did a lot of test before asking the question, it worked with 8 different files I have to import, and the only one witch failed was the file with the separator at the beginning of the lines.

    Now that I make sample file to post, it's working well. I don't understand what was wrong. I am very sorry of that.

    Sometimes, just asking a question solve the problem ! Thank's again...