Menu
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.
885
Below are some good ways to improve BULK INSERT operations : Using TABLOCK as query hint. Dropping Indexes during Bulk Load operation and then once it is completed then recreating them. Changing the Recovery model of database to be BULK_LOGGED during the load operation.
Create a temporary table with a single wide column and insert the whole row into that column for the whole file, then use select+insert to select from your temporary table, separating fields via the separator character as you select, and exclude the last row.
BULK INSERT statement BULK INSERT loads data from a data file into a table. This functionality is similar to that provided by the in option of the bcp command; however, the data file is read by the SQL Server process. For a description of the BULK INSERT syntax, see BULK INSERT (Transact-SQL).
1 Answer. You can specify FIRSTROW=2 in your BULK INSERT command to skip the very first row. See the below example. WITH (FIELDTERMINATOR ='\t', ROWTERMINATOR = '\n',FIRSTROW = 2);
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
155
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
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
