Home‎ > ‎

The multilingual (UTF-8) data, multi-line fields, CSV file and Excel import - Ha!!!

I have used CSV and Excel for so long in my career but it so happened that I never faced a problem where I had to import multi-lingual csv with long text fields into Excel.

Yesterday, the time came. My colleague told me that the French and Spanish csv files were showing weird characters and our consultant reminded me using UTF-8 to import would be a good option to Excel use proper Spanish and French characters instead of those weird characters. Aha, and then the Excel started to misbehave while importing the data. It would show multi-line strings on separate lines in Excel as the next row of the data. How did I get the solution? Well, searching the Google for the keywords in title will only give me the standard answers - "use UTF-8 format while importing" Well, I know that already and IT IS NOT WORKING!!!. 

Or somebody would even say, Excel doesn't recognise utf-8 on its own so, you can't do it. *sigh* (Forgive me, if this sigh doesn't sound that fancy. Google site: Do you provide a nice smiley icons for that?) 

After two hours, I accidentally, hit into another keyword which is very important for CSVs - The BOM. Try searching for it alone and you will get all kind of results except for the real BOM. Well, the BOM is a special set of characters that you can give at start of csv so that Excel recognises it as UTF-8. But you can't type those characters directly *another sigh*. 

So what do we do? Thanks to this inter-connected world, we can get support even if our privacy is not that much valued by the rulers.

There is a program called notepad++ (most of the times used by programmers but this serves our purpose very well here). You can download it for free at http://notepad-plus-plus.org/download/v6.3.3.html. This has a very beautiful option which adds those special BOM characters at start of any CSV and voila! Excel can recognise any csv now.