save file as unicode using bcp

Question

I have a need to export a table into a text file and save it on my server.

For that I use the following query which works good:

EXEC master..xp_cmdshell 'bcp "SELECT Lines FROM ##tempsql" queryout "C:\textfile.txt" -T -S Server\instance -w'

Problem with this is that even though I use the "-w" parameter which saves the data as Unicode, the textfile itself saves as ANSI format!

This causes me problem when I use this file in another system later on.. I have to manually open up the file and "save as" and choose Unicode instead of ANSI. Is there a way to get it to save as UNICODE per default? I have tried to read through all parameters, but it confuses me..

Is there any other way of achieving it, perhaps some use of sqlcmd?

Thanks in advance

Answer 1

Hi Cloxy,

Regarding to your description, I did a test to bulk export data from the table into a new text Unicode  named data file. The steps and code as below:
1. I used sample database and created ##tempsql first. 

SELECT *into ##tempsql from AdventureWorksDW. dbo.AdventureWorksDWBuildVersion

2. 

EXEC master..xp_cmdshell 'bcp "SELECT * FROM ##tempsql" queryout "C:\textfile.txt" -T -S amber007 -w'

3. The result is I got a Unicode format testfile.tet, please check the screenshot I got as below:

 

For more information, please refer to these articles:
1. Using Unicode Character Format to Import or Export Data: http://msdn.microsoft.com/en-us/library/ms188289.aspx
2. bcp Utility:  http://msdn.microsoft.com/en-us/library/ms162802.aspx
3. ACC2000: Using SQL to Export to Unicode by Means of the Jet Provider and Text ISAM:  http://support.microsoft.com/kb/234201

---

Regards, Amber zhang

Answer 2

Thank you for your answer and tests. However, it looks exactly the same for me when I open the document and try to "save as". It seems as Unicode is standard enconding in windows for when saving new documents, that's why it's showing up as Unicode in the dropdown list. But actually the document is not already encoded as such when we bcp out of sql.

as I said, if I just open the document and save as (overwrite the old) without changing anything inside the textfile, my other program works like a charm..

There must be someway.. but I'm clueless.

Answer 3

Hi Cloxy,

According to my screenshot I provided, the format of text file is already unicode after exporting from SQL Server.  The screenshot shows this file format is unicode by default. I did not save as to change it to unicode format. Sorry for that.

---

Regards, Amber zhang

Answer 4

I don't think you understand. It says Unicode for me as well when I bcp out it from sql.

But it seems as it is NOT encoded as unicode. The option there only states which default encoding should be applied when re-saving the document (as a new one).

So once again. It looks exactly the same for me but the problem is still there.

Answer 5

Problem with this is that even though I use the "-w" parameter which saves the data as Unicode, the textfile itself saves as ANSI format!

UNICODE file size should be double than ANSI.

Also in UNICODE the Hungarian word "jegyző" will not change to "jegyzo" (assuming default US SS collation).

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

Answer 6

Thank you for the tip about double the size for unicode!
I did a test and opened the document and "save as" ANSI, and quite correct, the filesize minimized by 50%...

How this is possible I have no idea. It's so weird I don't know what to do now..
Because I have to "save as" with unicode and write over the old file to get it to work... Even though I don't change anything and the filesize stays the same.. sigh.

this sucks.

Guess I'll just have to find some way. Anyone know a command in windows command prompt that I could schedule to open a particular file and save as new? Or maybe I could save it some other directory and then via scheduled cmd prompt job move it to its right location. I'll have to investigate this further.

Thanks for the help anyway.