tsql - Very simple bulk insert inserts NULLs for each row in CSV -

i can hear saying: ain't simple, it.

here's table:

create table [bulkimport].[test](     [id] [int] identity(1,1) not null,     [data] [nvarchar](50) null,  constraint [pk_test_1] primary key clustered  (     [id] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]  go 

here's tsql:

truncate table bulkimport.test  bulk insert bulkimport.test  'd:\referenzliste_vknr.csv'  (     formatfile='d:\vknrimport.xml',      codepage=28591,     errorfile='d:\vknrimporterror.txt' ) 

my config file looks this:

<?xml version="1.0"?> <bcpformat xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance">     <record>         <field id="1" xsi:type="charterm" terminator="\r\n" collation="sql_latin1_general_cp1_ci_as" />     </record>     <row>         <column source="1"  name="data" length="50" xsi:type="sqlnvarchar" />     </row> </bcpformat> 

and finally, excerpt data:

18 26 34 42 59 67 75 83 91 109 117 125 133 

there's carriage return line feed after each row, leaving 1 empty line @ end. null each entry in csv file, using sql server 2012.

your question isn't exact duplicate of this one (you didn't errors) it's same problem. documentation says:

with xml format file, cannot skip column when importing directly table using bcp command or bulk insert statement. however, can import last column of table. if have skip last column, must create view of target table contains columns contained in data file. then, can bulk import data file view.

in other words, can skip last column in table when using xml format file. trying skip id column, first column.

two solutions are:

• use non-xml format file
• use openrowset

when used non-xml format file, data loaded fine:

10.0 1 1       sqlchar             0       100     "\r\n"   2     data         latin1_general_ci_as 

but openrowset more flexible, because can use select re-order columns or otherwise manipulate data coming flat file.