Bulk Insert for text file with irregular terminators

  • I have run into an issue with several clients when importing their files into tables. I can manually import by using the Import\Export wizard and hit the correct row count. However, using OpenRowSet and Bulk insert, I come up rows short. Searching down into the millions of lines, I find areas where the field terminator suddenly changes from the default "tab" to just a singular space and then back again. This results in a shorter row count as the bulk insert fills the fields with the next available data. Does anyone know of a way to handle this? I realize that we can build an SSIS package, but the insert needs to be called from a routine. The clients are reluctant to change their exports and are working through Zerion. The code below is what I am executing through a called stored procedure passing @path

    Truncate table tblClientData

    DECLARE @SQL_BULK VARCHAR(MAX)

    Set @SQL_BULKInsert = 'BULK

    INSERT tblClientData

    FROM '''+ @path +'''

    WITH

    (

    FIELDTERMINATOR = ''\t'',

    OWTERMINATOR = '''',

    FIRSTROW=2

    )'

    Exec(@SQL_BULKInsert)

    Thank you in advance for your ideas and suggestions

    jfh2841

  • Are there any 'singular spaces' anywhere else in the data?

    If yes, you have a very solid basis on which to complain to the creators of the files – how would any system ever be able to differentiate between 'space character' and 'space delimiter'?

    Regardless of the answer, it's very poor form on the part of the file creators and, if it were me, I'd be embarrassed into fixing it.


  • Thank you. I had to convince my employers by showing them graphically what the files looked like. They are now contacting the creators.

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

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