August 30, 2012 at 5:02 pm
I'm trying to use the BULK INSERT statement to populate some table from a TXT file.
In the TXT file field values are separated by the ^ character and the end of line is (new line)
Here goes an example of the the query used to import the data:
BULK INSERT dbo._example
FROM 'c:\temp\example.txt'
WITH
(
DATAFILETYPE='char',
FIELDTERMINATOR ='^',
ROWTERMINATOR = ''
)
The _example table has only one column (ENAME) of varchar datatype.
When all the lines of the TXT table contain text without accents all goes right but when the file contains some accented caracter (é, á for example) then I get the error:
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 3, column 1 (ENAME).
¿What am I missing there?
September 6, 2012 at 3:27 am
Why have '^' as a delimiter for a start?
Anyway, the problem is probably the encoding of the txt file, make sure it's Unicode. Make sure your type is 'varchar' and not 'nvarchar'. Make sure the collation of the column is Latin1_General_CI_AS.
September 6, 2012 at 10:20 am
In particular, make sure that your source text file is not in UTF-8 format.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 12, 2012 at 10:11 pm
Yes!!! I just converted the txt file to UNICODE ant it worked fine... Thanks a lot!!!!
September 13, 2012 at 11:25 am
Glad we could help. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply