Search code examples
excelmongodbcsvutf-8mongoexport

Excel not displaying Spanish accents

I'm having a bad time exporting data to a CSV file with MongoExport, this data have some Spanish accents that are displaying bad in Excel.

In example, if there's an user called Raúl in Excel it appears as Raúl.

I'm using this script for my export:

mongoexport --host my_host --db test --collection users --csv --fields name --out users.csv

I read about this error and I found that adding an UTF-8 BOM will make the trick, is there any way to tell mongoexport that I'm trying to export an CSV UTF8 BOM file?

PS: Just to clarify things, Excel is the only editor that I have problem with. Google Sheets displays those characters perfectly; but I must be able to open it with Excel due to business regulations.

Solution

  • Assuming that you're on Linux/OS X, using Bash:

    Create an empty file with a UTF-8 BOM, then append the output of mongoexport to it:

    $ printf '\xFF\xFE' > users.csv
$ mongoexport --host my_host --db test --collection users --csv --fields name >> users.csv

    or:

    After running mongoexport as you are already, use the following command to insert a UTF-8 BOM at the start of the file:

    sed -i '1s/^/\xef\xbb\xbf/' users.csv