CSV File with Go — Support multiple writing systems in Excel

If you are a backend developer and have worked in a multilingual application or system, you might have come across this issue while working with CSV. When creating a CSV from the backend and opening the file in Excel, the cell with words of writing systems other than the Latin alphabet is gibberish or full of symbols instead of the word itself.

Image showing the unexpected data in Excel

In the above image, the data in column ‘B’ depicts the issue.

This can be solved by manipulating the file and the settings in the excel; however, doing this for every file might not be the appropriate solution.

After going through the similar situation, I ran into a solution which worked for me. And this article aims to deliver the same solution.

BOM | Byte Order Mark

A BOM is used to indicate how a processor places serialized text into a sequence of bytes.

Basically, BOM is used to indicate the byte order and encoding of a text file. It signals how the bytes in the file should be interpreted. There are byte-order marks for various encodings. We will be using UTF-8 encoding in this article.

Let’s look at how we can implement this in the Go programming language.

Case 1: CSV file with BOM encoding in Go

records := []Word{
   {"Nepal", "नमस्ते", "Hello"},
   {"Japan", "おはよう", "Good morning"},
   {"China", "你好", "Hi"},
}
file, err := os.Create("records.csv")
bomUtf8 := []byte{0xEF, 0xBB, 0xBF} // Initializing UTF-8 BOM encoding
file.Write(bomUtf8) // Writing UTF-8 BOM encoding to the file
defer file.Close()
if err != nil {
   log.Fatalln("failed to open file", err)
}
w := csv.NewWriter(file)
defer w.Flush()

// Using WriteAll
var data [][]string
for _, record := range records {
   row := []string{record.Country, record.Native, record.English}
   data = append(data, row)
}
w.WriteAll(data)

In this case, the UTF-8 BOM encoding is initialized and written to the created file before writing the data in it.

Case 2: CSV with BOM encoding in Gin

records := [][]string{
   {"country", "Native", "English"},
   {"Nepal", "नमस्ते", "Hello"},
   {"Japan", "おはよう", "Good morning"},
   {"China", "你好", "Hi"},
}

csvBuffer := new(bytes.Buffer)
bomUtf8 := []byte{0xEF, 0xBB, 0xBF}
csvBuffer.Write(bomUtf8)
writer := csv.NewWriter(csvBuffer) // Create a new CSV writer with UTF-8 BOM encoding
writer.WriteAll(records)

_, err := c.Writer.Write(csvBuffer.Bytes())
if err != nil {
   log.Fatalln("Error in writing with context: ", err.Error())
}

In this case, the UTF-8 BOM encoding is initialized and passed to the writer before writing the data. Since this case is for the server, the changes can be seen once this response is used in the front end to download CSV.

Result

If you open the created CSV file in Excel, you will see the result as expected.

Image showing the data in Excel with UTF-8 BOM encoding

Thank you for taking the time to read this article. I hope it was helpful.