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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With