BULK INSERT tab-delimited file - unescape \x09

sql-server, t-sql

Solution

Use temp table:

IF OBJECT_ID('tempdb..#test1') IS NOT NULL
DROP TABLE #test1;
GO

CREATE TABLE #test1
    (
    ID integer NOT NULL,
    Name varchar(30) NOT NULL,
    [Desc] varchar(50) NOT NULL,
    )


BULK
INSERT #test1 
FROM 'd:\111.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
);

SELECT ID, Name, REPLACE([Desc], '\x09', ''), '\x09' AS Delimeter
--INTO YourTable
FROM #test1

Problem

I'm using BULK INSERT to load a text file into SQL Server that was created from a SQL Anywhere database. The text file that we are receiving has fields that contain tab characters. They are in the text file escaped as "\x09". Can I get SQL Server to recognize this escape sequence? There are some rows that have enough of these escape sequences that they are causing a truncation error when I do the BULK INSERT. I'd much rather have SQL Server turn them back in to tab characters. Update (7/26): here's example file data ``` ID Name Desc 1 Value 1 Some text:\x09with tabs 2 Value 2 More Text:\x09with more\x09tabs ``` So, in this example, it takes 31 characters to express the value for the Desc field for the record with ID 2. However, it should be inserted into the database as 25 characters.

Original source