in reply to XLSX to CSV with high ASCII characters

Hi, I found a little more time to look at your problem. I was recently dealing with a related issue.

There's a well-known bug in MS Excel's handling of character encodings in CSV files. You can read all about in on StackOverflow and elsewhere. The short answer is that you need to encode your CSV file not as UTF-8 but as UTF-16LE in order for Excel to recognize it properly.

Here is a cleaned-up version of your script. For input I used an .xlsx file containing the data you showed below. This creates a CSV file encoded in UTF-16, so you won't be able to read it as a text file in your terminal if the terminal is set to UTF-8. But Excel will read the file and display the contents correctly. 

use strict; use warnings; use autodie;
use Spreadsheet::Read qw/ ReadData rows /;
use Text::CSV;

my $outputFile = '1198074.csv';
my $inputFile  = '1198074.xlsx';
my $book = ReadData( $inputFile );
my @writeRows;

for my $row ( rows( $book->[1] ) ) {
    my ( $id, $last, $first, $pref ) = @{ $row };
    push @writeRows, [ $pref || $first, $last, 'Static', 'Text' ];
}

open my $fh, '>:encoding(UTF-16LE)', $outputFile;
my $csv = Text::CSV->new( { binary => 1, eol => "\n", auto_diag => 1 }
+ );

$csv->print( $fh, $_ ) for @writeRows;

close $fh;

__END__

[download]

Hope this helps!


The way forward always starts with a minimal test.

Replies are listed 'Best First'.
Re^2: XLSX to CSV with high ASCII characters
by apu (Sexton) on Aug 27, 2017 at 21:22 UTC

    Thanks, 1nickt. Thinking this through to see if I can use that new info to my benefit. If not, its definitely useful info for the future. That said, technically Excel is not involved. The source XLSX file comes from one website. I have to process it into a CSV file for upload to a different website. So the UTF-8 CSV is fine. I just can't figure out why the encoding in my output file gets munged when the same Perl modules produce good UTF-8 output in another use case.

    If I have to, I'll just pre-process all the XLSX files with xls2csv and then use the CSV files as the source data from my script. I was just trying to avoid that extra step. Since xls2csv is just a Perl script itself, I should be able to do all the work inside one script.

[reply]
      Just glancing at xls2csv, I see that it uses Spreadsheet::ParseExcel. Your script uses Spreadsheet::Read, which uses Spreadsheet::ParseXLSX, which is a front end for Spreadsheet::ParseExcel. Have you tried using ParseExcel directly without all the middlemen, like xls2csv does?
[reply]

        I have not tried the other modules but I'm willing to give it a try. For what it's worth, though, the xls2csv I have does use Spreadsheet::Read. I believe it was installed when Spreadsheet::Read was installed via CPAN. (It uses Text::CSV_XS instead of Text::CSV and \r\n instead of \n but I did try those already with no change in my output.)

        # xls2csv: Convert Microsoft Excel spreadsheet to CSV
#          (m)'17 [07-06-2017] Copyright H.M.Brand 2008-2017
[...]
our $VERSION = "3.3";
[...]
use Text::CSV_XS;
use Spreadsheet::Read qw( ReadData row );

        [download]
[reply]
[d/l]

      Hi again, I'm a bit lost now, or maybe you are.

      When you say "the UTF-8 CSV is fine" you mean, I assume, that you can open the CSV file in a text editor and the characters display correctly. But as I understood it your goal was to open the CSV file in Excel and that was failing to display the characters correctly. The script I provided does that -- takes input in UTF-8, converts it to Perl internal, works on it, and outputs it in UTF-16 so Excel will display it right.

      If you want something else, I've missed it.

      Of course, you could always write out an .xls or .xlsx file in UTF-8 instead of CSV in UTF-16, if it's going to be used in Excel anyway, and avoid the encoding issue.


      The way forward always starts with a minimal test.
[reply]
        Sorry for the misunderstandings.
        When you say "the UTF-8 CSV is fine" you mean, I assume, that you can open the CSV file in a text editor and the characters display correctly.

        The CSV file from xls2csv looks fine, whether I just "cat" it in a terminal window, open it in a text editor or open it in Excel. The CSV from my script comes out with the wrong encodings in all three situations. The reference to opening the output in Excel, in reply to poj's sample script, was only to see if another program (besides 'cat' or a text editor) could make sense of the data.

        The source data is an Excel workbook or, more precisely, a Microsoft Excel Open XML Format Spreadsheet (XLSX) file created by a third-party website. I doubt an actual instance of Excel is being used since the file is dynamically created by a database query. After I process it, my output CSV is going to be uploaded to a different third-party website. There is no Microsoft Excel processing the output. The goal is to eliminate humans opening the spreadsheet in Excel and manipulating it at all.

        Aside: I unzip'ed the .xlsx file and looked at the raw XML. Everything in the source file is listed as UTF-8 encoding. So, I'm starting with UTF-8 and trying to end with UTF-8. (Or utf8 -- I tried both for my output as there is a difference.)

[reply]

In Section Seekers of Perl Wisdom