CODEWITHSUNDEEP
sqlsql-servercsv
Faseeh Haris
Faseeh Haris

Reputation: 669

Import csv data in SQL, but cell data contains comma, how do I handle It in SQL

I am reading CSV file in SQL with below code. But a cell data which contains comma , is surrounded by " double quotes. for example csv file

Customer Address is header, value in CSV is "City name, Country". This is surroned by double quoates. Other columns are in plain text format.

I want to import data from csv but due to this my all data goes to next cell after seperating comma from this cell.

    BULK INSERT #TempTable
    FROM 'e:\\filetesting.csv'
    WITH
    (
        FIRSTROW = 2,
        FIELDTERMINATOR = ',',  --CSV field delimiter
        ROWTERMINATOR = '\n',   --Use to shift the control to next row
        TABLOCK
    )

All Extensions,123456,,Jhon Doe,,107,0,"City, Country<321>",197,James ,InBound

Upvotes: 0

Views: 1238

Answers (1)

Zhorov
Zhorov

Reputation: 29933

It's difficult without test data, but I'm able to reproduce the issue from the question and a possible solution is the FORMAT='CSV' option, available from SQL Server 2017:

CSV file:

Address, Customer
"City name, Country", Customer Name

Statement:

BULK INSERT #TempTable
FROM 'e:\filetesting.csv'
WITH
(
    FIRSTROW = 2,
    FORMAT = 'CSV',
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

Result:

Address              Customer
----------------------------------
City name, Country   Customer Name

Upvotes: 3

Related Questions