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": [
"types": [
{"type": "First Base"},
{"type":"Second Base"},
{"type":"Pitcher"}
],
"name": {
"name": "John Doe"
}
}
],
"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 json = JsonConvert.SerializeObject(query);
{
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();
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";
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().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 query = rdr.Cast
{
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();
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error {0}", ex.Message);
Console.ReadLine();
}
}
}
Sai Kumar KoonaPosted May 6, 2020, 9:11 AM