I'm trying to use BULK insert a flat file.
The file has a header row. In addition to the delimiter character which is
the header row contains text qualifiers:
The text file looks like this when opened in notepad:
I would like to use the bulk insert command with the format file option to import the data into a table in my database.
The table in my database looks like this:
CREATE TABLE [dbo].[BulkInsertedData](
[Column1Name] [nvarchar](4000) NULL,
[Column2Name] [nvarchar](4000) NULL,
[Column3Name] [nvarchar](4000) NULL
) ON [PRIMARY]
If I try to import from a flat file that is identical in all respects except that does not contain delimiters, I can import without any problems. Without delimiters, the format file looks 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="Column1Name" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4000"/> <FIELD ID="Column2Name" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4000"/> <FIELD ID="Column3Name" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="4000"/> </RECORD> <ROW> <COLUMN SOURCE="Column1Name" NAME="Column1Name" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="Column2Name" NAME="Column2Name" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="Column3Name" NAME="Column3Name" xsi:type="SQLVARYCHAR"/> </ROW></BCPFORMAT>
The bulk insert command I use to get the data in looks like this:
BULK INSERT BulkInsertedData
FORMATFILE = 'E:\FormatFile.txt',
FIRSTROW = 2, -- first row has column headings
My problem is - I can't figure out how to change the format file so that the bulk insert works when the header data is surrounded by text qualifiers as per the text file described above. I'm guessing the problem is escaping certain characters, but after hacking at it for a few hours, I haven't been able to figure out where to put the escape characters. Has anyone else successfully done this and if so, how did your XML format file differ from mine?