CODEWITHSUNDEEP
sqlsql-serverbulkinsert
Jake Evans
Jake Evans

Reputation: 1048

SQL Server Bulk Insert - 0 row(s) affected

I am trying to do a bulk insert of a .CSV from a remote location.

My SQL statement is:

BULK INSERT dbo.tblMaster 
FROM '\\ZAJOHVAPFL20\20ZA0004\E\EDData\testbcp.csv'
WITH (FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n')

My .CSV looks like this:

john,smith
jane,doe

The CSV is saved with UTF-8 encoding, and there is no blank line at the bottom of the file. The table that I am bulk inserting too is also empty.

The table has two columns; firstname (nvarchar(max)) and secondname (nvarchar(max)).

I have sysadmin rights on the server so have permission to perform bulk inserts.

When running the SQL, it runs without error, and simple shows -

0 row(s) affected

and doesn't insert any information.

Any help is greatly appreciated.

Upvotes: 14

Views: 16372

Answers (5)

naveed Ahmad
naveed Ahmad

Reputation: 61

When you specify \n as a row terminator for bulk export, or implicitly use the default row terminator, it outputs a carriage return-line feed combination (CRLF) as the row terminator. If you want to output a line feed character only (LF) as the row terminator - as is typical on Unix and Linux computers - use hexadecimal notation to specify the LF row terminator. For example: ROWTERMINATOR='0x0A'

no need to do the following: Notepad++>> Edit >> EOL Conversion >> Windows Format

Upvotes: 6

Russell
Russell

Reputation: 21

I opened the CSV with Excel and hit Control + S to resave it. That fixed the issue for me.

Upvotes: 2

skadam85
skadam85

Reputation: 543

I know this may be too late to answer but I thought this might help anyone looking for the fix. I had similar issue with Bulk Insert but didn't find any fix online. Most probably the flat/csv file was generated with non-windows format. If you can open the file in Notepad++ then go to edit tab and change the EOL Conversion to "Windows Format". This fixed the problem for me.

Notepad++>> Edit >> EOL Conversion >> Windows Format

Upvotes: 28

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

The problem is, at least in part, the UTF-8 encoding. That is not supported by default. If you are using SQL Server 2016 then you can specify Code Page 65001 (i.e. add , CODEPAGE = '65001' to the WITH clause). If using an earlier version of SQL Server, then you need to first convert the file encoding to UTF-16 Little Endian (known as "Unicode" in the Microsoft universe). That can be done either when saving the file or by some command line utility.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294177

Try inserting the file using bcp.exe first, see if you get any row or any error. The problem with 

BULK INSERT ...
FROM '\\REMOTE\SHARE\...'

is that you're now bringing impersonation and delegation security into picture and is more difficult to diagnose the issue. When you access a remote share like this you are actually doing a 'double-hop' Kerberos impersonation (aka. delegation) and you need special security set up. Read Bulk Insert and Kerberos for the details.

Upvotes: 1

Related Questions