I have inherited a project using Visual Studio (.NET Framework 4.7.2, I think). I'm receiving a response from an API endpoint with multiple entries which looks like so:
[
{
"transaction_id": "00000352",
"transaction_type": "New",
"transaction_date": "2018-08-23T00:00:00",
"sold_to_id": "00026",
"customer_po_number": "34567",
"po_date": "2018-08-23T00:00:00",
"notes": null,
"batch_code": "######",
"location_id": "MN",
"req_ship_date": "2018-08-28T00:00:00",
"fiscal_period": 8,
"fiscal_year": 2018,
"currency_id": "USD",
"original_invoice_number": null,
"bill_to_id": "00026",
"customer_level": null,
"terms_code": "30-2",
"distribution_code": "01",
"invoice_number": null,
"invoice_date": "2018-08-23T00:00:00",
"sales_rep_id_1": null,
"sales_rep_id_1_percent": 100,
"sales_rep_id_1_rate": 0,
"sales_rep_id_2_id": "SA",
"sales_rep_id_2_percent": 100,
"sales_rep_id_2_rate": 10,
"ship_to_id": null,
"ship_via": null,
"ship_method": null,
"ship_number": null,
"ship_to_name": "",
"ship_to_attention": null,
"ship_to_address_1": null,
"ship_to_address_2": null,
"ship_to_city": null,
"ship_to_region": null,
"ship_to_country": "USA",
"ship_to_postal_code": null,
"actual_ship_date": null,
"order_line_list": [
{
"entry_number": 1,
"item_id": "5' Glass/Wood Table",
"description": "Glass/ Wood Combo Coffee Table",
"customer_part_no": null,
"additional_description": null,
"location_id": "MN",
"quantity_ordered": 11,
"units": "EA",
"quantity_shipped": 0,
"quantity_backordered": 0,
"req_ship_date": null,
"unit_price": 599.99,
"extended_price": 6599.89,
"reason_code": null,
"account_code": null,
"inventory_gl_account": "120000",
"sales_gl_account": "400000",
"cogs_gl_account": "500000",
"sales_category": null,
"tax_class": 0,
"promo_id": null,
"price_id": null,
"discount_type": 0,
"discount_percentage": 0,
"discount_amount": 0,
"sales_rep_id_1": null,
"sales_rep1_percent": null,
"sales_rep1_rate": null,
"sales_rep_id_2": "SA",
"sales_rep2_percent": 100,
"sales_rep2_rate": 5,
"unit_cost": 241.4467,
"extended_cost": 2655.91,
"status": "Open",
"extended_list": [],
"serial_list": []
}
],
"tax_group_id": "ATL",
"taxable": false,
"tax_class_adjustment": 0,
"freight": 0,
"tax_class_freight": 0,
"tax_location_adjustment": null,
"misc": 0,
"tax_class_misc": 0,
"sales_tax": 0,
"taxable_sales": 0,
"non_taxable_sales": 6599.89,
"payment_list": []
}
]
I would like to select individual key-value pairs from this response and create a data table. I'm extremely new (and somewhat allergic) to C# but I have tried doing this:
var doc = JsonDocument.Parse(response.Content);
JsonElement root = doc.RootElement;
//Console.WriteLine(doc.RootElement);
Console.WriteLine(String.Format("Root Elem: {0}", doc.RootElement));
//creat data table
var jsonObject = JObject.Parse(response.Content);
Console.WriteLine(String.Format("jsonObject col: {0}", jsonObject));
DataTable jsdt = jsonObject[doc.RootElement].ToObject<DataTable>();
int totalRows = jsdt.Rows.Count;
Console.WriteLine(String.Format("jsonObject col: {0}", jsonObject));
Console.WriteLine(String.Format("totalRows col: {0}", totalRows));
Console.WriteLine(String.Format("jsdt col: {0}", jsdt.Rows.Count));
I can't seem to get the DataTable to log to the console and have no idea if I'm actually creating the DataTable at all. I can loop through the JSON response to find individual key-value pairs but writing things to a DataTable is where I'm stuck. This is how I'm looping through the JSON:
var users = root.EnumerateArray();
while (users.MoveNext())
{
var user = users.Current;
//System.Console.WriteLine(user);
var props = user.EnumerateObject();
while (props.MoveNext())
{
var prop = props.Current;
//Console.WriteLine($"{prop.Name}: {prop.Value}");
if (prop.Name == "transaction_date") {
//Console.WriteLine(String.Format("key: {0}", $"{prop.Name}: {prop.Value}"));
}
}
}
How would one go about creating a DataTable in this way?
Update:
My ultimate purpose is to create a CSV and ship it to another server.
I recognize that a CSV file is basically a 2d array of primitive values but that my JSON objects contain nested arrays of complex objects (e.g. [*].order_line_list[*]) which will need to be flattened somehow. For this purpose flattening into multiple rows would be fine, e.g. parent name, parent info, nested item info 1; parent name, parent info, nested item info 2;
CodePudding user response：
Use Regex :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
namespace ConsoleApplication37
{
class Program
{
const string FILENAME = @"c:\temp\test.txt";
static void Main(string[] args)
{
string input = File.ReadAllText(FILENAME);
string pattern = "\"(?'key'[^\"] )\":\\s (?'value'[^,] )";
MatchCollection matches = Regex.Matches(input, pattern, RegexOptions.Multiline);
Dictionary<string, string> dict = matches.Cast<Match>()
.GroupBy(x => x.Groups["key"].Value, y => y.Groups["value"].Value.Trim())
.ToDictionary(x => x.Key, y => y.FirstOrDefault());
}
}
}
CodePudding user response：
Your basic problem here is that you have a JSON array containing JSON objects with possibly non-primitive object and array values, and you would like to eventually export that to CSV. However, a CSV file is essentially a 2d array of primitive values, so you will need to flatten the non-primitives into primitives. In order to do this, you may either
- Extend the CSV horizontally by adding extra columns for nested values.
- Extend the CSV vertically by adding extra rows for nested values.
You chose option 2, Multiple rows would be fine, eg parent name, parent info, nested item info 1; parent name, parent info, nested item info 2;
To do that, we're going to need two third-party tools, the LINQ to JSON from Json.NET to parse your arbitrary JSON objects, and CsvWriter from CsvHelper to write a CSV file.
First, introduce the following extension method to flatten a LINQ to JSON JObject into an enumerable of enumerable of key/value pairs of primitive JSON values:
public static partial class JsonExtensions
{
public static IEnumerable<IEnumerable<KeyValuePair<string, JValue>>> FlattenChildrenIntoRows(this JObject obj)
{
bool anyReturned = false;
var simpleChildren = obj.Properties().Where(p => p.Value is JValue).Select(p => new KeyValuePair<string, JValue>(p.Name, (JValue)p.Value)).ToList();
var complexChildren = obj.Properties().Where(p => !(p.Value is JValue)).Select(p => new KeyValuePair<string, JToken>(p.Name, p.Value));
foreach (var property in complexChildren)
{
foreach (var flattenedItems in FlattenChildrenIntoRows(property))
{
yield return simpleChildren.Concat(flattenedItems);
anyReturned = true;
}
}
if (!anyReturned)
yield return simpleChildren;
}
static IEnumerable<IEnumerable<KeyValuePair<string, JValue>>> FlattenChildrenIntoRows(KeyValuePair<string, JToken> property)
{
if (property.Value is JValue value)
{
yield return new[] { new KeyValuePair<string, JValue>(property.Key, value) };
}
else if (property.Value is JArray array)
{
foreach (var item in array)
foreach (var flattenedItem in FlattenChildrenIntoRows(new KeyValuePair<string, JToken>(property.Key, item)))
yield return flattenedItem;
}
else if (property.Value is JObject obj)
{
foreach (var flattenedItems in FlattenChildrenIntoRows(obj))
yield return flattenedItems.Select(p => new KeyValuePair<string, JValue>(property.Key "." p.Key, p.Value));
}
else // JRaw, JContainer
{
throw new NotImplementedException();
}
}
}
Next, introduce the following method to write a list of Dictionary<string, string> objects to a CSV file, making the dictionary keys be the CSV column names:
public static partial class CsvExtensions
{
public static void WriteCsv(TextWriter writer, IReadOnlyList<IDictionary<string, string>> objects, CsvConfiguration config)
{
var headers = objects.SelectMany(o => o.Keys).Distinct();
using (var csv = new CsvWriter(writer, config))
{
foreach (var header in headers)
csv.WriteField(header);
csv.NextRecord();
foreach (var obj in objects)
{
foreach (var header in headers)
if (obj.TryGetValue(header, out var value))
csv.WriteField(value);
else
csv.WriteField(null);
csv.NextRecord();
}
}
}
}
And now we can combine these two methods to read a JSON array, flatten it, and write to CSV as follows:
public static partial class CsvExtensions
{
/// Convert a JSON file to a CSV file
public static void ConvertJsonToCsv(string inputPath, string outputPath)
{
using var reader = new StreamReader(inputPath);
using var writer = new StreamWriter(outputPath);
ConvertJsonToCsv(reader, writer);
}
/// Read JSON from the incoming TextReader, convert to CSV and write to the incoming TextWriter
public static void ConvertJsonToCsv(TextReader reader, TextWriter writer)
{
// Disable reformatting of dates
var settings = new JsonSerializerSettings
{
DateParseHandling = DateParseHandling.None,
};
using var jsonReader = new JsonTextReader(reader) { CloseInput = false };
// Deserialize to an array of objects
var array = JsonSerializer.CreateDefault(settings).Deserialize<List<JObject>>(jsonReader);
// Flatten and convert to a List<Dictionary<string, string>>
var list = array
// Flatten each child into an enumerable of enumerable of primitive key/value pairs
.Select(o => o.FlattenChildrenIntoRows())
// Project and flatten to an overall enumerable of enumerable of pairs
.SelectMany(pairs => pairs)
// Convert to an overall enumerable of dictionaries
.Select(pairs => pairs.ToDictionary(p => p.Key, p => p.Value.Value == null ? null : (string)Convert.ChangeType(p.Value.Value, typeof(string), CultureInfo.InvariantCulture)))
// Materialize as a list of dictionaries
.ToList();
// Write to CSV
var config = new CsvConfiguration(CultureInfo.InvariantCulture);
CsvExtensions.WriteCsv(writer, list, config);
}
}
Which you might call like:
using var reader = new StringReader(response.Content);
using var writer = new StringWriter();
CsvExtensions.ConvertJsonToCsv(reader, writer);
var csvText = writer.ToString();
Console.WriteLine(csvText);
Demo fiddle here.