« Back
in Excel CSV Google Sheets Characters not displaying correctly UTF-16 LE UTF-8 Byte order Mark read.

CSV Report Hell.

So recently, I had to make report in the CSV format. How hard could it be? After all it was just supposed to me a file with comma separated values, right? Wrong!

Microsoft Excel still belonging to the Stone Age refuses to read the CSV file properly.

One option is to data import the CSV file in to the Excel. It works! But unfortunately I wanted something cleaner.

So I stumbled on a trick on Stack Overflow, since I was using a comma separated file CSV I should write "sep=," on the first line of the file to make in work in Excel. It works.

But what is this, Google Sheet refuses to read this sort of CSV file now. Fuck Google Sheets. As long as Excel reads it. Thats enough.

Now lets try with weird alphabets from Swedish to see if Excel can read it or not. It cant!!

May be my regional settings in Excel are wrong. They were right. My string was not in UTF-8, okay making that change made sure that Excel can display those awful characters. I had done it!

My feeling of joy, merely lasted a few minutes. If I save the CSV file in Excel, it again loses the formatting of the CSV file. By this time the only sane thing to do was to pull my
hair out!!

After pulling and tugging at my hair. I came across another solution. If we write the CSV files in Lower Endian Order with UTF-16 string encoding and a byte order mark. Then Excel can read and save that file. Why Microsoft Why?

That does work actually and I did not have the need to add the "sep=," to the file anymore either. So it worked in Google Sheets too.

Note to Self. Just make the report in XLSX format next time!

comments powered by Disqus