Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

BULK INSERT tab-delimited file - unescape \x09

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.

like image 903
Dingels35 Avatar asked Jul 18 '13 03:07

Dingels35


1 Answers

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
like image 56
Konstantin Taranov Avatar answered Sep 28 '22 00:09

Konstantin Taranov