Bulk Insert for text file with irregular terminators

• John.F.Houck

  SSC Rookie

  Points: 31

  More actions

  April 5, 2016 at 7:06 am

  #399981

  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

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  April 5, 2016 at 7:35 am

  #1869804

  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.

  ---

• John.F.Houck

  SSC Rookie

  Points: 31

  More actions

  April 5, 2016 at 9:15 am

  #1869849

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