SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Unicode to UTF8
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AustraliaDBA
Starting Member

29 Posts

Posted - 11/30/2010 :  23:20:56  Show Profile  Reply with Quote
Hi All,
I got stuck in a bad situation which is getting worst day by day.
I have to export data from sql database to csv file(, delimited and " text qualifier) in UTF8 format. Spent so much time on internet and found a work around.. which involves following steps
1. Create connection to db
2. Use data conversion and convert all DT_STR,DT_TEXT to DT_WSTR,DT_NTEXT
3. Use data conversion and convert all DT_WSTR,DT_NTEXT
to DT_STR,DT_TEXT and in codepage select UTF-8
4.Create destination csv file and again in codepage select UTF-8
this is working fine for me but moment there are chinese characters in the data it lost them because of the conversions i use in the package.
Is there anyway to convert nvarchar to utf-8 format and store in csv file.. any help would be much appreciated....

Thanks

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/01/2010 :  04:58:47  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
MS in their wisdom removed utf-8 support from most things so now SSIS is really the only way to deal with it reliably.
Are you sure that you are losing chinese characters rather than having an issue with whatever you are using to view them - in UTF-8 they can be stored in up to 5 bytes per character.
I'm a bit surprised you need to go through all that - I'm pretty sure that I've done this by just exporting directly to a UTF-8 file - but my test was to make sure that I could import and export and end up with the same result - may not have had any non-unicode strings. As far as utf-8 is concerned ascii characters aren't affected. I'll see if I can find the packages I used and check.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

AustraliaDBA
Starting Member

29 Posts

Posted - 12/01/2010 :  19:08:25  Show Profile  Reply with Quote
Hi Nigel
yes i have to go through this long process... unfortunately couldn't figure out any otehr way around.... i tried to export the csv back into database abd loosing all the chinese characters. any help would be much appreciated.

Thanks
Go to Top of Page

AustraliaDBA
Starting Member

29 Posts

Posted - 12/06/2010 :  23:58:31  Show Profile  Reply with Quote
any update on this one.. anyone...?

Thanks
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/07/2010 :  06:01:37  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
I'm not finding it easy to find what I did - probably best to repost. A lot of people probably don't look at questions that already have posts.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 12/07/2010 :  08:07:56  Show Profile  Reply with Quote
Hm, just a thought...I have no idea if it will work for you. But at least using the Export Data wizard in SSMS you have an option of exporting the csv-file in a unicode format. Is it then possible for you to convert the unicoode file to UTF-8 using Powershell for example? Using the method specified here -> http://stackoverflow.com/questions/750449/converting-xml-from-utf-16-to-utf-8-using-powershell

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 12/07/2010 :  08:19:55  Show Profile  Reply with Quote
Hm, the powershell script wasn't very well suited. This might work better:
$file = get-content file.csv
set-content -encoding utf8 file2.csv


- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000