1

I receive a set number of CSV files each day from a third party vendor. Their developers wrap the values within the fields in double quotes. There are times where the values contain commas (For example: "In memory of John Doe, may this donation..."). Even though the comma is within a set of double quotes, when attempting to bulk insert the files into SQL Server by way of a bulk insert command, everything after the comma is placed in the next field. This isn't a desired output.

My bulk insert script looks something like:

BULK INSERT dbo.Transactions_Raw_Temp
FROM 'C:\DataDumps\FileDelivery\Transactions.csv'
WITH (FORMATFILE = 'C:\DataDumps\Bulk_Insert\TransactionsImport.fmt', 
BATCHSIZE = 5000, 
ROWTERMINATOR = '\n',
MAXERRORS = 1,
FIRSTROW = 2);

I've asked the third party vendor to send pipe delimited text files instead but they cannot accommodate that request. The daily csv files are in excess of 600,000 rows, and growing, so manually processing each file is a bit of an exercise.

Braiam
  • 1
  • 11
  • 47
  • 78
Corey
  • 19
  • 3
  • 1
    Are to on a recent version of SQL Server? if so there are parameters to tell SQL Server it's a CSV with quote identifies; how to use it is detailed well in the documentation. If not, don't use `BULK INSERT` or upgrade to a recent version. – Thom A Jan 22 '21 at 20:46
  • SQL Server 2016. This is a company-owned server, not my own. I don’t have admin rights to upgrade it. I thought the feature you’re referring to came about in SQL Server 2017? – Corey Jan 22 '21 at 21:03
  • @Corey [correct](https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2016#input-file-format-options): _FORMAT = 'CSV' Applies to: SQL Server 2017 (14.x) CTP 1.1. Specifies a comma separated values file compliant to the RFC 4180 standard._ So you need to find another way to import the data while encouraging your company to upgrade their SQL Server. – AlwaysLearning Jan 22 '21 at 23:54
  • @AlwaysLearning Thank you for the confirmation. Much appreciated. – Corey Jan 23 '21 at 02:44

0 Answers0