BULK INSERT with QUOTE DELIMITED fields

Question

question

koptastic69 asked Mar 24, '10

BULK INSERT with QUOTE DELIMITED fields

I have the following csv file layout.

"","","99999999","","10-10-01","GBP","22/12/09","Close",-80.12,"CHQ","725472","","",

I wish to do a BULK INSERT. The fields are comma delimited but also double quote delimited. Is there a way to 'remove' the double quotes during the bulk insert.

Table def;

CREATE TABLE [dbo].[StatementHold
    [Group] [char](30) NULL,
    [AccID] [char](30) NULL,
    [AccountNo] [char](20) NULL,
    [Type] [char](30) NULL,
    [BankCode] [char](15) NULL,
    [Curr] [char](20) NULL,
    [Date] [date] NULL,
    [AsAt] [char](10) NULL,
    [Amount] [money] NULL,
    [TLACode] [char](10) NULL,
    [ChequeNo] [char](20) NULL,
    [Status] [char](20) NULL,
    [Description] [char](100) NULL
) ON [PRIMARY]

bulk-insert

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab ♦ commented · Mar 29, 2010 at 02:23 PM

I just added an explanation of the terminator string and an example of the BULK INSERT statement. Please let me know if you need more.

0 ·

Tom Staab ♦ commented · Mar 30, 2010 at 11:17 PM

I corrected the XML and successfully tested it using your 1 sample data row and your table structure. I decided to take advantage of single and double quotes. It's easier that way. I'm not sure why I didn't think of that originally.

0 ·

Tom Staab ♦ commented · Mar 30, 2010 at 11:17 PM

If the character columns vary in length, consider using varchar instead of char.

0 ·

Tom Staab ♦ commented · Mar 31, 2010 at 10:39 AM

I just noticed you have a trailing comma at the end of the one sample line you posted here. If every line has a comma after the description, change your field terminator for that field to '",\r\n'

0 ·

Answer 1 · 2010-03-24T12:51:45Z

Tom Staab answered Mar 24, '10

Unfortunately, this is not easy. It can be done using a format file, but it's a little ugly.

I don't have time to test it now, but this should work or at least get you pretty close.

I'll explain more when I have time. Also, I recommend varchar instead of char for your character fields.

<?xml version="1.0"?>
<BCPFORMAT 
  xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RECORD>
        <FIELD  ID="1" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
        <FIELD  ID="2" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD  ID="3" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD  ID="4" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD  ID="5" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="15" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD  ID="6" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD  ID="7" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD  ID="8" xsi:type="CharTerm" TERMINATOR='",'    MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD  ID="9" xsi:type="CharTerm" TERMINATOR=',"'    MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD ID="10" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD ID="11" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD ID="12" xsi:type="CharTerm" TERMINATOR='","'   MAX_LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
        <FIELD ID="13" xsi:type="CharTerm" TERMINATOR='"\r\n' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    </RECORD>
    <ROW>
        <COLUMN SOURCE="1" NAME="Group" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="2" NAME="AccID" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="3" NAME="AccountNo" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="4" NAME="Type" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="5" NAME="BankCode" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="6" NAME="Curr" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="7" NAME="Date" xsi:type="SQLDATETIME"/>
        <COLUMN SOURCE="8" NAME="AsAt" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="9" NAME="Amount" xsi:type="SQLMONEY"/>
        <COLUMN SOURCE="10" NAME="TLACode" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="11" NAME="ChequeNo" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="12" NAME="Status" xsi:type="SQLVARYCHAR"/>
        <COLUMN SOURCE="13" NAME="Description" xsi:type="SQLVARYCHAR"/>
    </ROW>
</BCPFORMAT>

Sorry for the long delay without more details. Here's some more information regarding this:

First, let me quickly explain the terminator syntax. It's really quite simple.

terminators are enclosed in double-quotes
backslash () is the "escape" character. See this (search for "String Escape Sequences") for examples.
"\",\"" means that column ends with a double-quote followed by a comma and then another double-quote. Technically, that's the end of the first column, the column terminator and the beginning of the next column, but that is how we remove the quotes from the data itself.

Here's an example of an actually bulk insert command using an XML format file:

BULK INSERT dbo.big_table
FROM 'D:\incoming_data\big_file.txt'
WITH (FORMATFILE='D:\format_files\big_file_format.xml');

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2012-01-27T02:48:16Z

Halivud Estevez answered Jan 27, '12

Bulk insert does not work when I'm using TERMINATOR=',"' (terminator equals singleq, comma, doubleq, singleq) SQL 2008 R2, no error message just silently does not import anything. Removing doubleq it works. Strange...

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2015-12-09T20:08:24Z

Nagaram123 answered Dec 9, '15

@Tom still need a dummy entry with '"' in the first line to fix the first " for each record The below BCPFORMAT fixed for my case

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question