Parsing CSV file with JSON data in Golang

SaurabhVS
October 19, 2023
167 views
2 votes
3 Answers

I’ve a CSV File having data in below format.

Date,RestaurantId,ItemRatings
2023-10-08,232,[{"item_id":8215117,"item_name":"The Farmers Breakfast","current_day_count":0,"current_day_sum":0,"mtd_count":1,"mtd_sum":5,"wtd_count":0,"wtd_sum":0},{"item_id":8215132,"item_name":"The Great White","current_day_count":0,"current_day_sum":0,"mtd_count":1,"mtd_sum":5,"wtd_count":0,"wtd_sum":0}]

I want to parse the CSV file to store the data in a struct

type ItemRatings struct {
    RestaurantId          int     `json:“item_id”`
    Date       string `json:"date"`
    ItemData   []ItemData `json:“item_data”`
}

type ItemData struct {
    ItemID          int     `json:“item_id”`
    ItemName        string  `json:“item_name”`
    CurrentDayCount int     `json:“current_day_count”`
    CurrentDaySum   int     `json:“current_day_sum”`
    MTDCount        int     `json:“mtd_count”`
    MTDSum          int     `json:“mtd_sum”`
    WTDCount        int     `json:“wtd_count”`
    WTDSum          int     `json:“wtd_sum”`
}

POC code:

reader := csv.NewReader(file)
reader.LazyQuotes = true

for {
   record, err := reader.Read()
   if err != nil {
      t.Fatalf("%v", err)
   }
   itemDetailsJson := record[2]
   var itemDetails []ItemData

   err = json.Unmarshal([]byte(itemDetailsJson), &itemDetails)
   if err != nil {
      t.Fatalf("Error unmarshalling: %v", err)
   }
   fmt.Printf("Unmarshalled Array: %+vn", itemDetails)
}

Please suggest a way to do it in Golang. Facing issues due to double quotes and commas used in the json data list. Please suggest code changes or alternative CSV format which can be used to achieve the objective with data aggregated on restaurantId field in the CSV.

Tags: csv file-format go json

Answers

Is that what you want ?

data.csv

Date,RestaurantId,ItemRatings
2023-10-08,232,"[{""item_id"":8215117,""item_name"":""The Farmers Breakfast"",""current_day_count"":0,""current_day_sum"":0,""mtd_count"":1,""mtd_sum"":5,""wtd_count"":0,""wtd_sum"":0},{""item_id"":8215132,""item_name"":""The Great White"",""current_day_count"":0,""current_day_sum"":0,""mtd_count"":1,""mtd_sum"":5,""wtd_count"":0,""wtd_sum"":0}]"

script.go

package main

import (
    "encoding/csv"
    "encoding/json"
    "fmt"
    "os"
    "strconv"
)

type ItemRatings struct {
    RestaurantId int        `json:"RestaurantId"`
    Date         string     `json:"Date"`
    ItemData     []ItemData `json:"ItemRatings"`
}

type ItemData struct {
    ItemID          int    `json:"item_id"`
    ItemName        string `json:"item_name"`
    CurrentDayCount int    `json:"current_day_count"`
    CurrentDaySum   int    `json:"current_day_sum"`
    MTDCount        int    `json:"mtd_count"`
    MTDSum          int    `json:"mtd_sum"`
    WTDCount        int    `json:"wtd_count"`
    WTDSum          int    `json:"wtd_sum"`
}

func main() {
    file, err := os.Open("data.csv")
    if err != nil {
        fmt.Printf("Error opening file: %vn", err)
        return
    }
    defer file.Close()

    reader := csv.NewReader(file)
    reader.LazyQuotes = true

    // Skip the header row
    _, err = reader.Read()
    if err != nil {
        fmt.Printf("Error reading header: %vn", err)
        return
    }

    var itemRatings []ItemRatings

    for {
        record, err := reader.Read()
        if err != nil {
            break
        }

        restaurantID, err := strconv.Atoi(record[1])
        if err != nil {
            fmt.Printf("Error converting RestaurantId to int: %vn", err)
            return
        }

        itemDetailsJson := record[2]

        var itemDetails []ItemData
        err = json.Unmarshal([]byte(itemDetailsJson), &itemDetails)
        if err != nil {
            fmt.Printf("Error unmarshalling: %vn", err)
            return
        }

        itemRating := ItemRatings{
            RestaurantId: restaurantID,
            Date:         record[0],
            ItemData:     itemDetails,
        }

        itemRatings = append(itemRatings, itemRating)
    }

    fmt.Printf("Parsed data: %+vn", itemRatings)
}

You can use the encoding/csv and enconding/json packages in Go to parse the CSV data and unmarshal it into your struct. you’ll need parse the JSON data from the CSV and then unmarshal it into your ItemData struct.

package main

import (
    "encoding/csv"
    "encoding/json"
    "fmt"
    "log"
    "os"
    "strings"
)

type ItemRatings struct {
    RestaurantId int       `json:"item_id"`
    Date         string    `json:"date"`
    ItemData     []ItemData `json:"item_data"`
}

type ItemData struct {
    ItemID          int    `json:"item_id"`
    ItemName        string `json:"item_name"`
    CurrentDayCount int    `json:"current_day_count"`
    CurrentDaySum   int    `json:"current_day_sum"`
    MTDCount        int    `json:"mtd_count"`
    MTDSum          int    `json:"mtd_sum"`
    WTDCount        int    `json:"wtd_count"`
    WTDSum          int    `json:"wtd_sum"`
}

func main() {    
    file, err := os.Open("data.csv")
    if err != nil {
        log.Fatal(err)
    }
    defer file.Close()

    reader := csv.NewReader(file)

    var itemRatingsList []ItemRatings

    for {
        record, err := reader.Read()
        if err != nil {
            break
        }

        jsonStr := record[2]

        var itemDataList []ItemData
        if err := json.Unmarshal([]byte(jsonStr), &itemDataList); err != nil {
            log.Fatalf("Error unmarshalling JSON: %v", err)
        }

        itemRating := ItemRatings{
            RestaurantId: 232, // Assuming a constant RestaurantId for this example
            Date:         record[0],
            ItemData:     itemDataList,
        }

        itemRatingsList = append(itemRatingsList, itemRating)
    }

    for _, itemRating := range itemRatingsList {
        fmt.Printf("RestaurantId: %d, Date: %sn", itemRating.RestaurantId, itemRating.Date)
        for _, itemData := range itemRating.ItemData {
            fmt.Printf("ItemID: %d, ItemName: %sn", itemData.ItemID, itemData.ItemName)
        }
    }
}

Because of the encoding/csv, csv file was updated with a double quote.
I spent some time, so I added it here as an alternative way.

CSV (test.csv):

Date,RestaurantId,ItemRatings
2023-10-08,232,"[{""item_id"":8215117,""item_name"":""The Farmers Breakfast"",""current_day_count"":0,""current_day_sum"":0,""mtd_count"":1,""mtd_sum"":5,""wtd_count"":0,""wtd_sum"":0},{""item_id"":8215132,""item_name"":""The Great White"",""current_day_count"":0,""current_day_sum"":0,""mtd_count"":1,""mtd_sum"":5,""wtd_count"":0,""wtd_sum"":0}]"

Code:

package main

import (
    "encoding/csv"
    "fmt"
    "os"
    "strings"
)

func main() {
    file, err := os.Open("test.csv")
    if err != nil {
        fmt.Println("Error:", err)
        return
    }
    defer file.Close()

    csvReader := csv.NewReader(file)

    for {
        record, err := csvReader.Read()
        if err != nil {
            break
        }

        if len(record) != 3 {
            fmt.Println("Invalid:", record)
            continue
        }

        date := record[0]
        restaurantID := record[1]
        itemRatingsCSV := record[2]

        fmt.Println("Date:", date)
        fmt.Println("Restaurant ID:", restaurantID)
        fmt.Println("Item Ratings (CSV):")

        itemRatingsJSON := strings.Trim(itemRatingsCSV, "[]")
        itemRatings := strings.Split(itemRatingsJSON, "},{")
        for _, item := range itemRatings {
            item = strings.Trim(item, "{}")
            fmt.Println(item)
        }

        fmt.Println()
    }
}

Output:

Please signup or login to give your own answer.

Click here to cancel reply.