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:
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();
}
}