Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with BULK INSERT Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 5:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, August 23, 2014 9:16 PM
Points: 420, Visits: 293
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?



Post #1352529
Posted Thursday, September 6, 2012 3:27 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:55 AM
Points: 8, Visits: 79
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.
Post #1355148
Posted Thursday, September 6, 2012 10:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
In particular, make sure that your source text file is not in UTF-8 format.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1355463
Posted Wednesday, September 12, 2012 10:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, August 23, 2014 9:16 PM
Points: 420, Visits: 293
Yes!!! I just converted the txt file to UNICODE ant it worked fine... Thanks a lot!!!!
Post #1358330
Posted Thursday, September 13, 2012 11:25 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Glad we could help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1358734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse