How to get query result in .CSV file with UTF-8 format.

Question

How to get query result in .CSV file with UTF-8 format.

Joy Smith San

SSC-Insane

Points: 24877
More actions
November 9, 2011 at 8:21 am

#247661

Hi All,
I have a package which takes data and convert into .csv file.
Our customer wants the .csv file in UTF-8 format.
Can you pls guide me on this ? Where and what to change.?
Thanks in advance.
Smith.

Viewing 15 posts - 1 through 15 (of 17 total)

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

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 1

In the Flat File connection manager, you can set the code page to UTF-8.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Koen Verbeeck (11/9/2011)
In the Flat File connection manager, you can set the code page to UTF-8.

Thanks a lot for the response. Was trying with it.

2 more doubts.

1. What's that "Unicode" checkbox for.?

2. If I change code page to UTF-8, Do I need to change the data type for each and every output column in advance editor or anywhere else.? Pls guide.

Thanks again.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 3

Joy Smith San (11/10/2011)
1. What's that "Unicode" checkbox for.?

To convert the strings to unicode strings.

In SQL Server, this is varchar versus nvarchar. Unicode can handle much more special characters than normal code pages. Unicode does take up more space, as it needs more bytes for encoding.

More info:

http://en.wikipedia.org/wiki/Unicode

Joy Smith San (11/10/2011)
2. If I change code page to UTF-8, Do I need to change the data type for each and every output column in advance editor or anywhere else.? Pls guide.

I don't believe so. UTF-8 is an encoding of the text file, it has nothing to do with SSIS itself.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Thank you. I will try but just selecting UTF-8 and not changing the data type of any field.

Wel, regarding Unicode. I am aware about unicode datatype. Think my question was not clear. Actualy I wanted to ask, "Unicode" has nothing to to do with UTF-8 format.? If we 'check' unicode check box, then we are not able to select any partiicular format.

Thanks a lot and I think am more clear now. Will try one by one and see.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 5

Ah well, your question is indeed more clear now

If you have "regular" strings, aka non-unicode, you need to specify a code page that describes how the characters needs to be encoded. In the Western world, this is usually 1252 (ANSI - Latin I) and this is also the SSIS default I think. However, there are also other encodings possible, such as UTF-8, but also Korean code pages, Chinese ones, Russian ones et cetera. The 1252 code page can handle normal western characters, but it can't display Chinese ones.

Unicode however, is an encoding standard that can deal with almost any character, so there is no need to specify a code, as it can handle everything.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 6

Thanks for quick reply.

We need it exactly in UTF-8 format. So will uncheck "unicode" and select code page as "UTF-8". Hope it will work teh way we want.

Thanks,

Smith.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 7

Joy Smith San (11/10/2011)
Thanks for quick reply.
We need it exactly in UTF-8 format. So will uncheck "unicode" and select code page as "UTF-8". Hope it will work teh way we want.
Thanks,
Smith.

Allright. Post back if you encounter problems. Good luck!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

Just one more thing.. is there any way to verify the format of our newly created CSV file. ?

Thanks.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 9

You can easily check encoding with the free tool Notepad++.

If you cannot use this tool, open the CSV file in regular Notepad and click File > Save as.

In the Save As window, you should see the encoding at the bottom.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 10

Koen Verbeeck (11/10/2011)
You can easily check encoding with the free tool Notepad++.
If you cannot use this tool, open the CSV file in regular Notepad and click File > Save as.
In the Save As window, you should see the encoding at the bottom.

Hi,

When I opened in Notepad and clicked 'Save As' it was showing the encoding as "UTF-8" at the botttom.

But when I installed and opened the .CSV in Notepad++, in the 'Encoding' menu "Encode in Ansi" is selected. I mean it's having black dot there.

confused now. Is it in UTF-8 or ANSI..? Pls help.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 11

Strange. At my computer both tools indicate the same encoding if it is utf-8.

Try to get your hands on a hex editor. With that you can truly check the encoding.

http://codesnipers.com/?q=how-to-determine-text-file-encoding

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 12

Hi All,

Nothing worked fo rme. I tried all the options.

Hence am I asking the question once again.

Suppose I have a table with a single varchar column (eg. name varchar(10)).

and table is having 100 rows.

I want to create an SSIS project to get this data in .csv file with UTF-8 format .

Can anyone tell me the steps to do the same, one by one.

Thanks in advance.

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 13

Joy Smith San

SSC-Insane

Points: 24877

November 11, 2011 at 7:49 pm

#1406627

..Awaiting for responses.. Pls help.

Thanks.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 14

If you set the flat file connection manager as utf-8, it should be utf-8. So there is probably a misconfiguration somewhere.

Check your package and make sure the resulting flat file is utf-8 or not.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

How to get query result in .CSV file with UTF-8 format.

Cookies on SQLServerCentral