How to parse a csv column that contains JSON?

user_16315656 0
Viewed 482

Say I have a csv file, example.csv, that looks like this (double quotes added by excel):

Id,Name,requestJson
12345,Albert,"{
    ""latitude"": -43.518703,
    ""longitude"": -71.69634,
    ""tags"": [
      ""aliqua"",
      ""ad"",
      ""dolor"",
      ""culpa"",
      ""sunt"",
      ""consequat"",
      ""irure""
    ],
    ""friends"": [
      {
        ""id"": 0,
        ""name"": ""Bryan Montoya""
      },
      {
        ""id"": 1,
        ""name"": ""Marcella Tillman""
      },
      {
        ""id"": 2,
        ""name"": ""Leola Calderon""
      }
    ],
    ""greeting"": ""Hello, undefined! You have 7 unread messages."",
    ""favoriteFruit"": ""strawberry""
  }"

The RequestJson would deserialize into the below objects:

public class Friend
{
    public int id { get; set; }
    public string name { get; set; }
}

public class Request
{
    public double latitude { get; set; }
    public double longitude { get; set; }
    public List<string> tags { get; set; }
    public List<Friend> friends { get; set; }
    public string greeting { get; set; }
    public string favoriteFruit { get; set; }
}

My attempt starts with reading example.csv, skipping the headers, then passing the string array values to FromCsv to split it into the 3 attributes within the Request object.

public static List<Request> LoadFiles()
{
    List<Request> requests = File.ReadAllLines("./example.csv")
                                    .Skip(1)
                                    .Select(v => FromCsv(v))
                                    .ToList();
    return requests;
}

Here I am using the array indices because I know where the first two elements are. The problem is, when I try to retrieve values[2], the split delimiter has failed as there are escape characters and commas in the json.

public static Request FromCsv(string csvLine)
{
    string[] values = csvLine.Split(',');
    Request request = new Request
    {
        Id = values[0],
        Name = values[1],
        Request = JsonConvert.DeserializeObject<Request>(values[2])
    };
    return request;
}

How can I parse the RequestJson column into my desired Request Object?

json.net csv json c#
1 Answers

Thanks @luuk. Got a working solution.

The solution was to provide a max number of splits. Then remove all the double quotes that csv's input (CsvColumnToJson)

Solution:

public static string GetJson (string csvLine)
{
    string[] values = csvLine.Split(',', 3);
    return CsvColumnToJson(values[2]);       
}

/* Csv json formatting escapes double quotes with more double quotes and adds double quotes to the beginnning and end of the Json */
public static string CsvColumnToJson(string csvColumn)
{
    var duplicatedDoubleQuotesRemoved = csvColumn.Replace("\"\"", "\"");
    return duplicatedDoubleQuotesRemoved.Substring(0, duplicatedDoubleQuotesRemoved .Length - 1).Substring(1);
}
user_163156560
Related