I have a Java app which reads CSV files which have been created in Excel (e.g. 2007). Does anyone know what charset MS Excel uses to save these files in?
I would have guessed either:
but I am unable to decode extended chars (e.g. french accentuated letters) using either of these charset types.
This question is related to
excel
encoding
character-encoding
Russian Edition
offers CSV
, CSV (Macintosh)
and CSV (DOS)
.
When saving in plain CSV
, it uses windows-1251
.
I just tried to save French word Résumé
along with the Russian text, it saved it in HEX
like 52 3F 73 75 6D 3F
, 3F
being the ASCII
code for question mark
.
When I opened the CSV
file, the word, of course, became unreadable (R?sum?
)
While it is true that exporting an excel file that contains special characters to csv can be a pain in the ass, there is however a simple work around: simply copy/paste the cells into a google docs and then save from there.
From memory, Excel uses the machine-specific ANSI encoding. So this would be Windows-1252 for a EN-US installation, 1251 for Russian, etc.
Russian Edition
offers CSV
, CSV (Macintosh)
and CSV (DOS)
.
When saving in plain CSV
, it uses windows-1251
.
I just tried to save French word Résumé
along with the Russian text, it saved it in HEX
like 52 3F 73 75 6D 3F
, 3F
being the ASCII
code for question mark
.
When I opened the CSV
file, the word, of course, became unreadable (R?sum?
)
Excel 2010 saves an UTF-16/UCS-2 TSV file, if you select File > Save As > Unicode Text (.txt)
. It's (force) suffixed ".txt", which you can change to ".tsv".
If you need CSV, you can then convert the TSV file in a text editor like Notepad++, Ultra Edit, Crimson Editor etc, replacing tabs by semi-colons, commas or the like. Note that e.g. for reading into a DB table, often TSV works fine already (and it is often easier to read manually).
If you need a different code page like UTF-8, use one of the above mentioned editors for converting.
cp1250 is used extensively in Microsoft Office documents, including Word and Excel 2003.
http://en.wikipedia.org/wiki/Windows-1250
A simple way to confirm this would be to:
Example perl script:
#!perl
use strict;
use Spreadsheet::ParseExcel::Simple;
use Encode qw( decode );
my $file = "my_spreadsheet.xls";
my $xls = Spreadsheet::ParseExcel::Simple->read( $file );
my $sheet = [ $xls->sheets ]->[0];
while ($sheet->has_data) {
my @data = $sheet->next_row;
for my $datum ( @data ) {
print decode( 'cp1250', $datum );
}
}
OOXML files like those that come from Excel 2007 are encoded in UTF-8, according to wikipedia. I don't know about CSV files, but it stands to reason it would use the same format...
I had a similar problem last week. I received a number of CSV files with varying encodings. Before importing into the database I then used the chardet libary to automatically sniff out the correct encoding.
Chardet is a port from Mozillas character detection engine and if the sample size is large enough (one accentuated character will not do) works really well.
You can create CSV file using encoding UTF8 + BOM (https://en.wikipedia.org/wiki/Byte_order_mark).
First three bytes are BOM (0xEF,0xBB,0xBF) and then UTF8 content.
Waking up this old thread... We are now in 2017. And still Excel is unable to save a simple spreadsheet into a CSV format while preserving the original encoding ... Just amazing.
Luckily Google Docs lives in the right century. The solution for me is just to open the spreadsheet using Google Docs, than download it back down as CSV. The result is a correctly encoded CSV file (with all strings encoded in UTF8).
Russian Edition
offers CSV
, CSV (Macintosh)
and CSV (DOS)
.
When saving in plain CSV
, it uses windows-1251
.
I just tried to save French word Résumé
along with the Russian text, it saved it in HEX
like 52 3F 73 75 6D 3F
, 3F
being the ASCII
code for question mark
.
When I opened the CSV
file, the word, of course, became unreadable (R?sum?
)
I had a similar problem last week. I received a number of CSV files with varying encodings. Before importing into the database I then used the chardet libary to automatically sniff out the correct encoding.
Chardet is a port from Mozillas character detection engine and if the sample size is large enough (one accentuated character will not do) works really well.
Excel 2010 saves an UTF-16/UCS-2 TSV file, if you select File > Save As > Unicode Text (.txt)
. It's (force) suffixed ".txt", which you can change to ".tsv".
If you need CSV, you can then convert the TSV file in a text editor like Notepad++, Ultra Edit, Crimson Editor etc, replacing tabs by semi-colons, commas or the like. Note that e.g. for reading into a DB table, often TSV works fine already (and it is often easier to read manually).
If you need a different code page like UTF-8, use one of the above mentioned editors for converting.
You could use this Visual Studio VB.Net code to get the encoding:
Dim strEncryptionType As String = String.Empty
Dim myStreamRdr As System.IO.StreamReader = New System.IO.StreamReader(myFileName, True)
Dim myString As String = myStreamRdr.ReadToEnd()
strEncryptionType = myStreamRdr.CurrentEncoding.EncodingName
OOXML files like those that come from Excel 2007 are encoded in UTF-8, according to wikipedia. I don't know about CSV files, but it stands to reason it would use the same format...
You could use this Visual Studio VB.Net code to get the encoding:
Dim strEncryptionType As String = String.Empty
Dim myStreamRdr As System.IO.StreamReader = New System.IO.StreamReader(myFileName, True)
Dim myString As String = myStreamRdr.ReadToEnd()
strEncryptionType = myStreamRdr.CurrentEncoding.EncodingName
From memory, Excel uses the machine-specific ANSI encoding. So this would be Windows-1252 for a EN-US installation, 1251 for Russian, etc.
While it is true that exporting an excel file that contains special characters to csv can be a pain in the ass, there is however a simple work around: simply copy/paste the cells into a google docs and then save from there.
I had a similar problem last week. I received a number of CSV files with varying encodings. Before importing into the database I then used the chardet libary to automatically sniff out the correct encoding.
Chardet is a port from Mozillas character detection engine and if the sample size is large enough (one accentuated character will not do) works really well.
Russian Edition
offers CSV
, CSV (Macintosh)
and CSV (DOS)
.
When saving in plain CSV
, it uses windows-1251
.
I just tried to save French word Résumé
along with the Russian text, it saved it in HEX
like 52 3F 73 75 6D 3F
, 3F
being the ASCII
code for question mark
.
When I opened the CSV
file, the word, of course, became unreadable (R?sum?
)
You can create CSV file using encoding UTF8 + BOM (https://en.wikipedia.org/wiki/Byte_order_mark).
First three bytes are BOM (0xEF,0xBB,0xBF) and then UTF8 content.
cp1250 is used extensively in Microsoft Office documents, including Word and Excel 2003.
http://en.wikipedia.org/wiki/Windows-1250
A simple way to confirm this would be to:
Example perl script:
#!perl
use strict;
use Spreadsheet::ParseExcel::Simple;
use Encode qw( decode );
my $file = "my_spreadsheet.xls";
my $xls = Spreadsheet::ParseExcel::Simple->read( $file );
my $sheet = [ $xls->sheets ]->[0];
while ($sheet->has_data) {
my @data = $sheet->next_row;
for my $datum ( @data ) {
print decode( 'cp1250', $datum );
}
}
Source: Stackoverflow.com