question

koptastic69 avatar image
koptastic69 asked

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 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
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 Likes 0 ·
If the character columns vary in length, consider using varchar instead of char.
0 Likes 0 ·
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 Likes 0 ·
Tom Staab avatar image
Tom Staab answered

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 characters needed characters left characters exceeded

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

Halivud Estevez avatar image
Halivud Estevez answered
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 characters needed characters left characters exceeded

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

Nagaram123 avatar image
Nagaram123 answered
@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 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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