SQL Server MVP Madhivanan has shown us good trick of importing data with comma using OpenRowSet in this post.

After reading this post, I run given demo and I have done some experimenting with comma(,) as data in text based columns.

When I moved text based column to end, I am able to load the data successfully.

Let’s take an example,

Create  table

IF OBJECT_ID('dbo.BulkInsertTest','U') IS NOT NULL
	DROP TABLE dbo.BulkInsertTest
GO	
CREATE TABLE dbo.BulkInsertTest (Number INT,Name VARCHAR(100))
GO

Copy following lines as CSV, row three contains comma 

1,"Sachin Tendulker"
2,"Rahul Dravid"
4,"Graeme Smith"
3,"Kallis,Jacques"

Now, let’s execute following statement.

BULK INSERT dbo.BulkInsertTest 
	FROM 'C:\cri.csv' 
    WITH
     (
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
      )

We are able to import data with comma into SQL server using bulk insert in case where comma containing column is at the end.

I think, the reason behind this is, even if we specify comma(,) as a FieldTerminator, in case of last column the FieldTerminator would be ‘\n’.
The same thing happens when we use SQL server Import-export wizard, column1(second column) has ColumnDelimitier as {CR}{LF}.

This can only be used when there is one text column in CSV, which can contain comma. If the text based column is not at end in table, we can use the Format file in bulk insert. This is just done for experimenting, I don’t see this  useful trick in most of real life scenarios.