Search code examples
sqlsql-serverxmlimportbcp

How to import semicolon delimited text file into SQL Server

I'm trying to import a semicolon delimited file into SQL Server. I used bcp to try to create an XML file, but I'm getting errors.

The text file (data) looks like this:

customer_id;remed_date;assumed_closed;exempt_ind;refresh_date;Target_date;due_date
2;06/06/2015;True;False;06/13/2015;06/13/2020;
3;08/02/2014;False;False;;08/02/2019;

The XML file came out like this:

<?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="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="11"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="1"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="1"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="11"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="11"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="0x0A" MAX_LENGTH="11"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Customer_ID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="Remed_Date" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Assumed_Closed" xsi:type="SQLBIT"/>
  <COLUMN SOURCE="4" NAME="Exempt_Ind" xsi:type="SQLBIT"/>
  <COLUMN SOURCE="5" NAME="Refresh_Date" xsi:type="SQLDATE"/>
  <COLUMN SOURCE="6" NAME="Target_Date" xsi:type="SQLDATE"/>
  <COLUMN SOURCE="7" NAME="Due_Date" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

The table looks like this:

[ODS_Customer_ID] [int] NOT NULL,
[Remed_Date] [date] NOT NULL,
[Assumed_Closed] [bit] NOT NULL,
[Exempt_Ind] [bit] NOT NULL,
[Refresh_Date] [date] NOT NULL,
[Target_Date] [date] 
[Due_Date] [date]

When I try to run a BULK INSERT:

BULK INSERT MXB.dbo.RefreshSuppression
FROM '\\SRVR1\Data\MXB\Automated\BSA_AML_Suppression.txt'
(FORMATFILE = '\\SRVR1\Scripts\MXB\Weekly\MXBRefreshSupp.xml');

I get this error:

Msg 4864, Level 16, State 1, Line 26
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ODS_Customer_ID).
Msg 4866, Level 16, State 8, Line 26
The bulk load failed. The column is too long in the data file for row 1, column 7. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 26
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 26
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Any ideas?

Solution

  • For message 4864, you do not need to convert your file to an XML to perform a bulk insert. Try:

    BULK INSERT MXB.dbo.RefreshSuppression
FROM '\\SRVR1\Data\MXB\Automated\BSA_AML_Suppression.txt'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n'
    )
GO

    More options on bulk import can be found here.

    For message 4866, you are missing your 7th column in your file.

    Messages 7330 & 7399 appear to be related to your linked server, not your bulk upload. You can find the microsoft support article for them here.