Import Double Quotes Qualified CSV File With BULK INSERT


I found a quick way to import a double quoted column CSV file into SQL Server table with BULK INSERT without using FORMAT file.
The text file should include header double quoted as well.




 BULK INSERT  [test1].[dbo].ncoabycountynew
   FROM 'C:\temp\myfile_doublequotes4.txt'
   WITH
      (
         FIELDTERMINATOR ='","',
         ROWTERMINATOR ='"\n"',
		 FirstRow=2
      );


 

If the text file has no header, the code will change to the following with one extra double quote residue in the first cell of the first row.




 BULK INSERT  [test1].[dbo].ncoabycountynew
   FROM 'C:\temp\myfile_doublequotes4.txt'
   WITH
      (
         FIELDTERMINATOR ='","',
         ROWTERMINATOR ='"\n"',
		 FirstRow=1
      );


 

The BULK INSERT is loading data very fast. It took only 3 seconds to load a million rows (160 M size).

P.S.
There is a catch for this code: you need to get rid of an extra double quote at the end of the last column.

Advertisements

4 Comments on “Import Double Quotes Qualified CSV File With BULK INSERT”

  1. Chris says:

    Doesn’t your first column end up with a starting quote and your last column end with an end quote?

  2. REGINALDO CALIXTO says:

    Hello,
    My Name is Reginaldo and I’m having trouble to run bulk insert with a double quoted csv file. Could you please help me out on this one.

    The sintax I am using is:
    BULK INSERT [RoManSys_YouTube].[dbo].[YOUTUBE_UPLOAD_tbl]
    FROM ‘Y:\DATABASE\Upload\YOUTUBE\YOUTUBE_UPLOAD_BI.csv’
    WITH
    (
    FIELDTERMINATOR ='”,”‘,
    ROWTERMINATOR ='”\n”‘,
    FirstRow=2
    );
    And the csv file has the following:

    “videoId”,”isrc”,”day”
    “-0JH5DLZTS4″,””,”20160701″
    “-0JH5DLZTS4″,””,”20160701″
    “-0JH5DLZTS4″,””,”20160701″
    “-0JH5DLZTS4″,””,”20160701″
    “-0JH5DLZTS4″,””,”20160701″

    Every time I run the command I got (0 row(s) affected) message.

    I don’t know what to do. Please answer me on my email.

    Thanks in advance for your help.

    • Jingyang Li says:

      Check your double quotes. When you copy and paste code from WordPress, you need to check the quotations to make sure it is what you are expecting.
      Here is the one I tested:

      –create table [dbo].[YOUTUBE_UPLOAD_tbl] (videoId varchar(50),isrc varchar(50),[day] varchar(50))

      BULK INSERT [DB1].[dbo].[YOUTUBE_UPLOAD_tbl]
      FROM ‘C:\temp\YOUTUBE_UPLOAD_BI.csv’
      WITH
      (
      FIELDTERMINATOR ='”,”‘,
      ROWTERMINATOR ='”\n”‘,
      FirstRow=2
      );

      –“videoId”,”isrc”,”day”
      –“-0JH5DLZTS4″,””,”20160701″
      –“-0JH5DLZTS4″,””,”20160701″
      –“-0JH5DLZTS4″,””,”20160701″
      –“-0JH5DLZTS4″,””,”20160701″
      –“-0JH5DLZTS4″,””,”20160701″


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