Import Double Quoted CSV Text File to SQL Server

SQL Server, SSIS 1 Minute

We have plenty of way to import CSV data to SQL Server such as Power Shell, Log Parser, SSIS, TSQL (Bulk Insert), etc. In this article I am going to explain how to import Double Quoted CSV file to SQL Server using Bulk Insert.

Sample Rows for Double Quoted text in the CSV file.

“Ayyappan”, “SQL PASS”, “2012”

“Robert”, “KE”, “2012”

“Aadhi”, “SQLServerRider”, “2012”

Bulk Insert

This is a TSQL statement that helps us to import data to SQL Server from data files. Please learn more about this in MSDN.

Implementation:

TSQL to import CSV into a SQL table

BULK INSERT <<TableName>>
FROM  ‘<<FullyQualifiedFileName with Path>>’
with (FIELDTERMINATOR  = ‘,’, ROWTERMINATOR  = ‘\n’)
GO

We already know that the bulk insert does not remove the double quotes from the column data. So, I am going to introduce another SQL query that generates update statement for each column in the given table to remove double quotes from the column data.

SQL Query to Remove Double Quotes from the column Data

SELECT ‘Update ‘ + OBJECT_NAME(id) + ‘ Set ‘ +  name + ‘ = ‘ + ‘REPLACE(‘+ name +’,”””,’ + ””’)’  FROM syscolumns WHERE id = OBJECT_ID(‘<Table Name>’)

The above given SQL query will generate a set of Update statement. So, you have to execute those statements manually to remove the double quotes from data.

It is all you need to import Double quoted CSV file to SQL table. I believe this article is useful to you.

Thanks for reading.

Published by Robin

Using the technology to build products that helps the world to be a better place. It includes ML/AI and data processing.

Published

5 thoughts on “Import Double Quoted CSV Text File to SQL Server”

  1. Exceptional post but I was wondering if you could write a litte more
    on this subject? I’d be very grateful if you could elaborate a little bit further. Thanks!

    Reply

  2. To remove ‘single quotes’
    ——————————–
    SELECT
    ‘Update ‘ + OBJECT_NAME(id) + ‘ Set ‘ + name + ‘ = ‘ + ‘REPLACE(‘+ name + ‘,””””, ””)’
    FROM syscolumns WHERE id = OBJECT_ID(‘dbo.tmpName’)

    note: ‘id’ and ‘name’ are both columns/fieldnames in syscolumns
    Running the script will return a list of sql UPDATEs script that needs to be run to do the actual updates.

    Reply

  3. Hi Thangaraj,
    I have the data sample as

    Robert, 20, “No:10, 4th street″, Wr, 2900
    Melvin, 30, “13, London Mission″, Ca, 2900

    As stated above only the address part have double quotes with comma. Also the column delimiter is also comma. Please give your suggestion.

    Thanks
    Mohammed Mustafa

    Reply

    1. Hi Mohammed Mustafa

      In this scenario, we have to upload the data as normal CSV that is without text qualifier (“). After the data transfer is completed we have to remove the ” from the address column.

      you may use the staging table for upload and cleaning process.

      Reply

Leave a comment