←
→

Import Double Quotes Qualified CSV File With BULK INSERT

Posted: October 31, 2014 | Author: | Filed under: Uncategorized |4 Comments

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.

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

  1. Chris says:
    January 24, 2017 at 9:34 am

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

    Reply
  2. REGINALDO CALIXTO says:
    March 6, 2017 at 2:24 pm

    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.

    Reply
    • Jingyang Li says:
      March 7, 2017 at 10:32 am

      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″

      Reply

Leave a comment

←
→