BULK INSERT with latin/chinese/japanese characters

Question

Post reply

BULK INSERT with latin/chinese/japanese characters

vmgarzon

Old Hand

Points: 371
More actions
March 17, 2010 at 5:55 pm

#220008

Hi all,
I have an issue with a bulk insert that I did for loading a text file into a MS SQL table.
The T-SQL script is summarizes to:
DECLARE @OppFilePath varchar(64),
@report nvarchar(32),
@SQL_Ins varchar(2000)
-- Path to the directory where the files should be loaded
SET @OppFilePath = 'E:\Solution\XYZ\' '
SET @report = (SELECT repWWW FROM tbl_WWW)
SET @SQL_Ins = 'BULK INSERT dbo.tbl_OpABC
FROM ' + CHAR(39) + @OppFilePath + @report + CHAR(39) + ' WITH (
DATAFILETYPE = ' + CHAR(39) + 'widechar' + CHAR(39) + ',
FIELDTERMINATOR = ' + CHAR(39) + '","' + CHAR(39) + ',
ROWTERMINATOR = ' + CHAR(39) + '' + CHAR(39) + ',
FIRSTROW = 2
)'
-- Bulk insert the file into the Details table
EXEC sp_sqlexec @SQL_Ins
The txt file can have, in the second column, names in english, chinese, japanese, etc. So I need my table to write the record as it should (latin characters for wertern languages, other character sets for eastern languages)
The field that stores that information is nvarchar(max). I've tryes to save te text files as unicode, utf-8 and unicode big endian.
I don't know what I'm missing or if I need to retrieve the stored data with a special parameter on the select query to view the chinese and japanese kanjis and the latin letters. I can only see the latin letters right (english, spanish, portuguese, etc).
Any ideas?
Thanks in advance,
Vic

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Can you post a CREATE TABLE definition for the destination table and a small amount of sample data to bulk insert?

You should not use sp_sqlexec; use sp_executesql instead.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vmgarzon Old Hand Points: 371 More actions · Answer 2

Hi,

Thanks for taking the time to examine this.

The table was created this way:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tbl_OpABC](

[Opportunity Id] [nvarchar](255) NULL,

[Name] [nvarchar](255) NULL,

[Managed By] [nvarchar](255) NULL,

[Type Code] [nvarchar](255) NULL

)

"Opportunity Id","Name","Managed By","Opportunity Type Code"

"FGT1-3-3ED6JEA","US T&M 02042010","HFK","New Business"

"FGT1-3-3EDNLX5","CIBC Contingency Test","HFK","Cross Sell / Up Sell"

"FGT1-2-1A79A2H","JT - Dis- Q2","HFK","A"

"FGT1-2-1A79A2H","JT - Dis- Q2","HFK","B"

"FGT1-2-1A79A2H","JT - Dis- Q2","HFK","X"

"FGT1-2-UORLL3","PG-PPU: OPS","HFK","New Business"

"FGT1-3-PR5TT0","Supplies-Aftermarket Legacy","HFK","Terms"

"FGT1-3-PR5TT0","Supplies-Aftermarket Legacy","HFK","New Business"

"FGT1-3-PR5TT0","Supplies-Aftermarket Legacy","HFK","EX"

"FGT1-3-RXO7TW","Ray B. TC","HFK","New Business"

"FGT1-3-RXO7TW","Ray B. TC","HFK","Marked"

"FGT1-6-1AIAORN","?????","HFK",""

"FGT1-6-1DE8769","x86 Program Server","HFK",""

"FGT1-6-1DYNGK9","Feb10 supplies","HFK","Project"

"FGT1-6-1DZT8K1","NB??3?","HFK",""

"FGT1-6-ZHWAOX","Time lodge - Turntables","HFK","Terms"

...

and so on

Actually, something happened... when trying to copy the data on the txt on my computer, I couldn't see the kanjis (chinese/japanese characters), so I had to open the txt on the server to copy the data. Could it be that my computer, and the computers that are querying the database need to have some configuration to see the characters and the characters are actually being stored in the right way?

Thaks a lot,

Vic

Paul White SSC Guru Points: 150467 More actions · Answer 3

Thanks, that is great. It is 4am here now, so I will take a fresh look in the morning.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vmgarzon Old Hand Points: 371 More actions · Answer 4

Hey,

The issue was solved!.... It was the simplest, most ridiculous solution but when you're stuck you don't see it....

It was just to install the files for eastern languages on Windows... (Control Panel/Regional/Languages tab)

That solved the issue.

Paul White SSC Guru Points: 150467 More actions · Answer 5

vmgarzon (3/18/2010)
Hey,
The issue was solved!.... It was the simplest, most ridiculous solution but when you're stuck you don't see it....
It was just to install the files for eastern languages on Windows... (Control Panel/Regional/Languages tab)
That solved the issue.

Oh cool! That's really very interesting - thanks for letting me know 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

itzseema SSC Journeyman Points: 83 More actions · Answer 6

Hi,

I am new to bulk import and I am facing the same issue. Which language files i need to install?

CapnHector SSCoach Points: 16143 More actions · Answer 7

itzseema (5/14/2012)
Hi,
I am new to bulk import and I am facing the same issue. Which language files i need to install?

this thread is over 2 years old. you will most likely get the best help by starting a new thread. to answer your question the files you need to install depend on which languages you want to display. and its a display issue not a data issue. the data is there its just that the local machine does not know what to do with the data.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]