The Mystery of Missing Records: Debugging a JSON-to-CSV Transformation in Go

During my work on building a utility for data transformation on one of my side projects, I needed to convert a JSON-formatted file into CSV format. I ran into a tricky issue that took nearly an hour to debug before identifying the root cause.

The process should have been simple, consisting of three main steps:

1. Open the JSON file
2. Parse that JSON file into a specific struct
3. Write the data to a CSV file First, to give you an idea, the JSON is an array with 65,342 elements.

func JsonToCSV(data *SrcSheet) {
    // Create file name in a format like "email_241030172647.csv" (email_yymmddhhmmss.csv)
    fName := fileName()

    // Create file
    f, err := os.Create(fName)
    if err != nil {
        log.Println("Unable to create file", err)
        return
    }
    defer f.Close() // Closing to release resources
    w := csv.NewWriter(f) // Initializing CSV writer

    // Add header
    header := []string{"email", "provider", "added_on"}
    if err = w.Write(header); err != nil {
        log.Println("Unable to write header", err)
        return
    }

    count := 0
    for domain, elm := range data.Email {
        if err := w.Write(newRecord(domain, elm)); err != nil {
            log.Println("Unable to add new record", domain, err)
            return
        } else {
            count++
        }
    }

    log.Println("Number of records written =", count)
}

func newRecord(email string, e *SrcElements) []string {
    if e == nil {
        return nil
    }

    DBFormat := "2006-01-02 15:04:05.000"
    addedOn := time.Now().UTC().Format(DBFormat)

    r := []string{email, e.Provider, addedOn}
    return r
}

The code is straightforward: create a new file with a specific name format, defer its closing, initialize the CSV writer, and start writing to the file. Super simple, right?

Steps 1 and 2 worked well, so omitted them. Let’s shift focus to step 3, where something unexpected happened: the CSV output contained only 65,032 records, meaning 310 records were missing.

To troubleshoot, I tried the code with just 7 JSON elements instead of 65,032. Surprisingly, nothing was written to the CSV file at all!

I double-checked for simple mistakes, like missing file closure, but everything looked fine. I then retried with the full 65,032 elements, hoping to get more clues. That’s when I noticed that not only were 310 records missing, but the last record was incomplete as well.

65030 adam@gmail.com, gmail, 2023-03-17 15:04:05.000
65031 jac@hotmail.com, hotmail, 2023-03-17 15:04:05.000
65032 nancy@xyz.com, hotmail, 2023-03-

This was progress—I could now narrow down the issue and focus on w.Write(newRecord(domain, elm)), specifically the w.Write(...) method. I checked the documentation and found the reason:

... Writes are buffered, so [Writer.Flush] must eventually be called to ensure that the record is written to the underlying io.Writer ...

I had forgotten to call w.Flush(). This made sense since, from a performance perspective, the CSV writer buffers writes instead of executing I/O operations every time w.Write() is called. By buffering data, it reduces the I/O load, and calling w.Flush() at the end ensures any remaining data in the buffer is written to the file.

Here’s the corrected code:

...
f, err := os.Create(fName)
    if err != nil {
        log.Println("Unable to create file", err)
        return
    }
    defer f.Close()
    w := csv.NewWriter(f)
    defer w.Flush()

    // Add header
    header := []string{"email", "provider", "added_on"}
...

To confirm, I checked the bufio.go source code and found that the default buffer size is 4K. In the WriteRune(...) method, you’ll see that it calls Flush whenever the buffer reaches its limit.

That’s all! I hope you enjoyed reading. I tend to learn a lot from mistakes—whether mine or others’. Even if there’s no immediate fix, discovering a wrong approach helps me avoid similar pitfalls in the future. That’s why I wanted to share this experience!