Julie

Julie

  • 2k
  • 22
  • 12k

Extract from Excel to to JSON

May 4 2020 4:52 PM
I need to extract data from an Excel Spreadsheet and port it into a JSON file.  The code below extracts the rows I need.  But the format of the spreadsheet is such that type is actually a collection:
example Excel input 
Row   Name          Type
1        John Doe     First base 
2                            Second base
3                            Pitcher
4        Jack Smith  Third Base
 
 
 example output
{
   "types": [
      {"type": "First Base"},
      {"type":"Second Base"},
      {"type":"Pitcher"}
   ],
   "name": {
      "name": "John Doe"
   }
}
 
This code will extract the rows but because the types are on separate rows, how do I roill all types of a player into one JSON dcoument?
 
static void AlternateRead()
{
var pathToExcel = @"C:\temp\Players.xlsx";
var sheetName = "Sheet1";
var destinationPath = @"C:\Temp\Players.json";
var connectionString = $@"
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={pathToExcel};
Extended Properties=""Excel 12.0 Xml;HDR=YES""
";
try
{
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = $"SELECT * FROM [{sheetName}$] WHERE PlayerID IS NOT NULL OR Positiontype IS NOT NULL";
using (var rdr = cmd.ExecuteReader())
{
//LINQ query - when executed will create anonymous objects for each row
var query = rdr.Cast<DbDataRecord>().Select(row => new
{
id = Guid.NewGuid(),
PlayerName = row[3].ToString().Trim(),
Type = row[4].ToString().Trim()   //// <== there are 1 to many types per player
});

var json = JsonConvert.SerializeObject(query);
File.WriteAllText(destinationPath, json);
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error {0}", ex.Message);
Console.ReadLine();
}
}
 
 

Answers (1)