Home > SQL Server, SSIS > Import Double Quoted CSV Text File to SQL Server

Import Double Quoted CSV Text File to SQL Server


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.

Advertisements
  1. November 30, 2012 at 11:46 pm

    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!

  2. franklin
    November 4, 2013 at 1:57 pm

    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.

  3. November 12, 2013 at 12:02 am

    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

    • November 12, 2013 at 10:22 am

      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.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: