BULK INSERT fails - tried many variations on row delimiter

I am struggling to script a BULK INSERT for my .dat files. I am getting many errors depending on what I try, but I'm pretty sure it's related to the row delimiter. I can successfully use the SQL import wizard for my .dat files. Under "Specify the characters that delimit the source file", I see the following as defaults that I accept:

Row delimiter: {CR}{LF}   
Column delimiter: Comma {,}

The 500k rows and 158 columns come in just beautifully thanks to the wizard.

When scripting, I have tried all manner of \r\n variations and 0x0a etc and searched for help and reviewed similar posts. I can successfully script in a single line of real data, so I don’t think the column parsing is an issue. It fails when I try 2 lines in a dummy file. The least amount of errors I generate occurs with the following

bulk insert abc
from 'C:\TestDat\try2.dat'
with (FIRSTROW = 1, fieldterminator = ',', rowterminator = '\r\n')
go

Msg 4863, Level 16, State 1, Line 3
Bulk load data conversion error (truncation) for row 1, column 158 (Column 157).

(using 0x0a will create more errors on a 2 line file).

.dat contents:

xxx,20080501,xxx,xxx:175000,55008654,0178636,202307011001,001859915,OK,W,xx,1.0,00000100,48,202306290100,R0,2,202306290200,R0,0,202306290300,R0,0,202306290400,R0,0,202306290500,R0,1,202306290600,R0,1,202306290700,R0,0,202306290800,R0,0,202306290900,R0,0,202306291000,R0,0,202306291100,R0,0,202306291200,R0,1,202306291300,R0,0,202306291400,R0,0,202306291500,R0,2,202306291600,R0,1,202306291700,R0,0,202306291800,R0,0,202306291900,R0,0,202306292000,R0,0,202306292100,R0,0,202306292200,R0,0,202306292300,R0,2,202306300000,R0,3,202306300100,R0,0,202306300200,R0,0,202306300300,R0,2,202306300400,R0,0,202306300500,R0,1,202306300600,R0,1,202306300700,R0,3,202306300800,R0,0,202306300900,R0,0,202306301000,R0,0,202306301100,R0,0,202306301200,R0,3,202306301300,R0,1,202306301400,R0,0,202306301500,R0,1,202306301600,R0,1,202306301700,R0,0,202306301800,R0,0,202306301900,R0,1,202306302000,R0,0,202306302100,R0,1,202306302200,R0,0,202306302300,R0,1,202307010000,R0,0
xxx,20080501,xxx,xxx:175000,55008654,0178636,202307011001,001859915,OK,W,xx,1.0,00000100,4,202307010100,R0,1,202307010200,R0,0,202307010300,R0,1,202307010400,R0,0

Ok, I created the try2.dat file,

and i created a table abc using (I left out column3..column157, but they have same definition....);

CREATE TABLE abc 
(
    Column1 VARCHAR(200),
    Column2 VARCHAR(200),
    ...
    Column158 VARCHAR(200),
    Column159 VARCHAR(200)
)

Then started SQLCMD

D:\TEMP>sqlcmd
1> use test
2> go
Changed database context to 'TEST'.
1> delete from abc;
2>
3> go

(1 rows affected)
1>
2> bulk insert abc from 'd:\temp\try2.dat' with (FIRSTROW = 1, fieldterminator = ',', rowterminator = '\r\n')
3> go

(1 rows affected)

When you are getting an error, I see two possibilities:

• I you have one column less than me (I do have 159 columns)
• The line separator is not the same, see: How do I find out what the terminator of a line from a CSV file.

When selecting the table in SSMS, I do see:

[![start of the row][1]][1]

[![end of the row][2]][2]

The last column does not seem to be OK...... More investigation needed.... 🤔🤔😕

A workaround could be:

Create a table like this:

CREATE TABLE def
(
    Column1 VARCHAR(MAX)
);

and import using (since you do not have # characters in your file):

bulk insert def from 'd:\temp\try2.dat' 
with (FIRSTROW = 1, fieldterminator = '#', rowterminator = '\r\n')

After that, you can do:

SELECT * 
FROM STRING_SPLIT((SELECT Column1 FROM def), ',');

Resulting in:

NOTE: When your version of SQL is new enough, you can add the enable_ordinal parameter to STRING_SPLIT

NOTE2: Why is this enable_ordinal added to STRING_SPLIT?

Because in the old (or current 😢) way it works, STRING SPLIT does not "care" about ordinal position (https://stackoverflow.com/a/64092709/724039)

I created a DBFIDDLE with your data, this has a line number added to def

Part of the SELECT R, value FROM def d CROSS APPLY STRING_SPLIT((SELECT Column1 ), ','); (near the line change) is:

Unfortunately it is not possible (AFAIK) to add row number per line number in a reliable way...

ANOTHER ATTEMPT:

seeing: "The column count per row will equal 14+n*3 where n = the value in the 14th column."

Define your import table (here named with the clumsy name def2:

CREATE TABLE def2 (
  Column1 VARCHAR(200),
  Column2 VARCHAR(200),
  Column3 VARCHAR(200),
  Column4 VARCHAR(200),
  Column5 VARCHAR(200),
  Column6 VARCHAR(200),
  Column7 VARCHAR(200),
  Column8 VARCHAR(200),
  Column9 VARCHAR(200),
  Column10 VARCHAR(200),
  Column11 VARCHAR(200),
  Column12 VARCHAR(200),
  Column13 VARCHAR(200),
  Column14 VARCHAR(200),
  ColumnRest VARCHAR(MAX)
);

Import your data:

bulk insert def2 from 'D:\Temp\try2.dat' with (fieldterminator = ',');

Finally get the values, which can be used to be inserted in a (new) table:

WITH def2tmp AS (
   SELECT ROW_NUMBER() OVER (ORDER BY ColumnRest) as R, Column14, RTRIM(ColumnRest) + ',' as ColumnRest
   FROM def2
), PosComma AS (
   SELECT 
       R , 
       Column14, 
       CHARINDEX(',',ColumnRest,1) as R2, 
       SUBSTRING(ColumnRest,1,CHARINDEX(',',ColumnRest,1)-1) as value
       ,ColumnRest
   FROM def2tmp
   UNION ALL
   SELECT 
       R, 
       Column14, 
       CHARINDEX(',',ColumnRest,R2+1), 
       SUBSTRING(ColumnRest,R2+1,CASE WHEN (CHARINDEX(',',ColumnRest,R2+1)-R2)-1 < 0 THEN 100 ELSE (CHARINDEX(',',ColumnRest,R2+1)-R2)-1 END)
       ,ColumnRest
   FROM PosComma
   WHERE R2<LEN(ColumnRest) and r<=2
)
SELECt 
   R, 
   R2, 
   ROW_NUMBER() OVER (PARTITION BY R ORDER BY R2) as R3,
   value as value 
FROM PosComma
ORDER BY column14 DESC,R2 
OPTION (MAXRECURSION 20000)
;

output (where R is the row number from your import table, and R3 is the row number of the values (R2 is the position of the , in ColumnRest)):

P.S. SORRY for this messy output.... 😁

This solution gets you two tables, the first def2 with the first 14 columns, the second with the values in that row (but you have to define that table yourself!)