sql - Bulk Insert - Row Terminator for UNIX file + "\l" row terminator -

so have been wrestling perplexing issue bulk insert time. files come linux box , when @ them in hex edit mode/notepad ++ appear have linefeed (0a) row terminator. store bulk insert statements in table later job selects , executes statement in table load data staging table.

the particular case perplexing me table has 7 columns. data file has first 4 columns, rest should left null.

typically this:

bulk insert staging_table  'file_location'       (    datafiletype = 'widechar' ,   fieldterminator = ',' ,   rowterminator = 'something_here'    ); 

the row terminator has been biggest source of issues.

when try use "\n" bulk insert fails on truncation error-- seems treat file 1 long string , delimits columns correctly until runs out of columns (hence truncation error).

when use "0x0a" bulk insert fails on "unexpected end of file" error. there blank line @ end of file when removed still threw same error i'm not sure wrong there.

the 1 far has worked getting data table "\l". know means? have searched far , wide there doesn't seem documentation on it. or have been looking in wrong place completely.

the weird thing \l rowterminator though load still doesn't respect rowterminator... rows loaded 7 columns , split on seemingly random intervals.

anyone have idea? should clarify more?

the issue having not due row terminator. suspect, along end of file error, saw similar following:

msg 4864, level 16, state 1, line 1
bulk load data conversion error (type mismatch or invalid character specified codepage) row 1, column 4 ({column_name}).

while said below line still valid regarding rowterminator, real issue indicated statement of:

[the] table has 7 columns. data file has first 4 columns, rest should left null.

this issue. when using bulk insert, data file has have same number of fields table being inserted into. if not case, have use formatfile ='format_file_path' option in case need create format file , specify location.

i thought away easier openrowset(bulk...) can following:

insert staging_table    select *      openrowset(bulk 'file_location' ...); 

but doesn't allow specify rowterminator without using format file. hence need format file in either case.

or, import different staging table has 4 columns, , either:

• dump current staging_table, or

• do alter table add 3 missing columns (it more efficient add 3 nullable fields transfer data 1 table :-).

or, mentioned @philipkelley in comment on answer, create view 4 fields , have destination/target. , if doing appropriate steps enable operation minimally logged, msdn page prerequisites minimal logging in bulk import not 1 way or other effect if use view.

---

most \l interpreted 2 literal characters, hence not respecting rowterminator when tried it.

the 0x0a work have tested , behaves expected. statement should following:

bulk insert staging_table 'file_location'   (           datafiletype = 'widechar',        fieldterminator = ',',        rowterminator = '0x0a' ); 

i tried both , without 0x0a character @ end of final line , both worked same.

i removed 1 of commas 1 of lines, leaving less full set of fields, , when got following error:

msg 4832, level 16, state 1, line 2    bulk load: unexpected end of file encountered in data file. msg 7399, level 16, state 1, line 2    ole db provider "bulk" linked server "(null)" reported error.                   provider did not give information error. msg 7330, level 16, state 2, line 2    cannot fetch row ole db provider "bulk" linked server "(null)". 

make sure of rows in data file have required number of field separators (, in case). mentioned having 4 columns in file should 3 commas per row.