Posting the developed code for people who might need it or might be stuck in a similar problem.
This code below works for the most part as it makes the dictionary structure. However it needs to be improved with introduction of Flexible nested dictionaries that can take any types of values(including Dictionaries). This one generates the Survey_level Dictionary atleast: The nested borehole dictionary structure can only be added when we have a class that combines Boreholes Dictionary, SurveyParameter Dictionary & SurveySetting Dictionary
...
public void ExcelReader(string path)
{
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
var reader = ExcelReaderFactory.CreateReader(stream);
var result = reader.AsDataSet();
var tables = result.Tables.Cast<DataTable>();
//If you declared nested dict as object you couldn't do any operations with it that are provided by Dictionary, only the ones valid for all objects.
Dictionary<string, Dictionary<string, Dictionary<(string, string), object>>> dict_coll = new Dictionary<string, Dictionary<string, Dictionary<(string, string), object>>>();
Dictionary<string, Dictionary<(string, string), object>> rows = new Dictionary<string, Dictionary<(string, string), object>>();
Dictionary<(string, string), object> row_usable = new Dictionary<(string, string), object>();
foreach (DataTable table in tables)
{
Dictionary<(string, string), object> row;
foreach (DataRow dr in table.Rows)
{
row = new Dictionary<(string, string), object>();
foreach (DataColumn col in table.Columns)
{
string FirstcolName = table.Rows[0][col.ColumnName].ToString(); //index the first row with the column name
string SecondcolName = table.Rows[1][col.ColumnName].ToString(); //index the 2nd row with the column name
row.Add((FirstcolName, SecondcolName), dr[col]); //add the column names as keys and data as values in the dicttionary.using a tuple of two column headers as key
}
int index = table.Rows.IndexOf(dr); //indexing eachrow to cnvert the value in each row to string
rows.Add(index.ToString(), row); //converting to string
rows.Remove("0");
rows.Remove("1");
rows.Remove("3");
List<(string, string)> row_Keys = new List<(string, string)>(row.Keys);
List<object> row_vals = new List<object>(row.Values);
row_usable = row_Keys.Zip(row_vals, (k, v) => new { k, v }).ToDictionary(x => x.k, x => x.v);
}
dict_coll.Add(table.TableName.ToString(), rows);
}
var guid_data = rows.ElementAt(0).Value; //or use trygetvalue()
var SURVEY_ID = ("SurveyParameter", "SurveyId");
var BOREHOLE_ID = ("BoreholeParameter", "BoreholeId");
var LAYER_ID = ("Parameter", "LayerId");
Dictionary<string, object> guid_dict = new Dictionary<string, object>();
Dictionary<string, Dictionary<string,Dictionary<string,object>>> data_dict =
new Dictionary<string, Dictionary<string, Dictionary<string, object>>>();
//Dictionary<string,object?> data_dict =
// new Dictionary<string,object?>();
//Hashtable data_dict = new Hashtable;
Hashtable restructured = new Hashtable();
restructured.Add("ParameterGuid", guid_dict);
restructured.Add("Data", data_dict);
List<string> guid_dict_keys = guid_dict.Keys.OfType<string>().ToList();
List<string> data_dict_keys = data_dict.Keys.OfType<string>().ToList();
foreach (Dictionary<string, Dictionary<(string, string), object>> datasets in dict_coll.Values) // datasets= Dictionary<string, Dictionary<(string, string), object>>>no need for specified types with hashtable but have to cast the value KeyValuePair<string, Dictionary<string, Dictionary<(string, string), object>>>
{
foreach (KeyValuePair<(string, string), object> i in guid_data)
{
if (i.Key.Item1.EndsWith("Parameter") & !guid_dict_keys.Contains(i.Key.Item2))//string.IsNullOrEmpty()//convert guidict to single string //guid_dict.Keys i.Key.Item2))
{
guid_dict[i.Key.Item2] = i.Value; //convert guid
}
}
foreach (Dictionary<(string, string), object> dataset in datasets.Values) //NULlMaybe/get dict(tuple,value) /KeyValuePair<(string, string), object>datasets.Value as Hashtable, KeyValuePair<(string, string), object>
{
// get survey/borehole/layer Ids
string target_layer_id = dataset[LAYER_ID].ToString();
string target_survey_id = dataset[SURVEY_ID].ToString(); //
string target_borehole_id = dataset[BOREHOLE_ID].ToString();//
//NULL
var finished = false;
while (!finished)
{
if (data_dict.ContainsKey(target_survey_id))
{
var survey_set = data_dict[target_survey_id];
var boreholes = survey_set["Boreholes"] as boreholes_dict;
if (boreholes.ContainsKey(target_borehole_id))
{
var borehole_set = boreholes[target_borehole_id] as borehole_id_dict;
layers_dict layers = new layers_dict();
layers = borehole_set["Layers"] as layers_dict;
if (layers.ContainsKey(target_layer_id))
{
{ }
finished = true;
}
else
{
finished = true;
}
}
else
{
boreholes.Add(target_borehole_id, new Dictionary<string, object>());
boreholes[target_borehole_id].Add("BoreholeParameter", new Dictionary<string, object>());
boreholes[target_borehole_id].Add("BoreholeSetting", new Dictionary<string, object>());
boreholes[target_borehole_id].Add("Layers", new Dictionary<string, object>());
//boreholes = new Dictionary<string,Dictionary<string,Dictionary<string,object>>
}
}
else
{
//Dictionary<string, object> survey_nested_dict = new Dictionary<string, object>();
data_dict.Add(target_survey_id, new Dictionary<string, Dictionary<string,object>>());
data_dict[target_survey_id].Add("SurveyParameter", new Dictionary<string, object>());
data_dict[target_survey_id].Add("SurveySetting",new Dictionary<string, object>());
data_dict[target_survey_id].Add("Boreholes", new Dictionary<string, object>());
foreach (KeyValuePair<(string, string), object> cat_nam_val in dataset) // this is a hashtable version of dataset which is inside datasets.Values
{
var val_dict = new Dictionary<string, object>();
try
{
val_dict = data_dict[target_survey_id][cat_nam_val.Key.Item1];
}
catch
{
}
if (IsDictionary(val_dict)) ;
{
val_dict.Add(cat_nam_val.Key.Item2, cat_nam_val.Value);
}
}
}
}
}
}
string json_guid = JsonConvert.SerializeObject(guid_dict, Formatting.Indented);
File.WriteAllText(@"D:\Data\testjsons\test_guid_dict.json", json_guid);
string json = JsonConvert.SerializeObject(dict_coll, Formatting.Indented);
File.WriteAllText(@"D:\Data\testjsons\test2.json", json);
string json_data = JsonConvert.SerializeObject(data_dict, Formatting.Indented);
File.WriteAllText(@"D:\Data\testjsons\test_data_dict.json", json_guid);
}
}
}
EDIT:
The following code has achieved the structure shown above in the question description. I hope it helps anyone who is stuck in this problem.
public void ExcelReader(string path)
{
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
var reader = ExcelReaderFactory.CreateReader(stream);
var result = reader.AsDataSet();
var tables = result.Tables.Cast<DataTable>();
//If you declared nested dict as object you couldn't do any operations with it that are provided by Dictionary, only the ones valid for all objects.
Dictionary<string, Dictionary<string, Dictionary<(string, string), object>>> dict_coll = new Dictionary<string, Dictionary<string, Dictionary<(string, string), object>>>();
Dictionary<string, Dictionary<(string, string), object>> rows = new Dictionary<string, Dictionary<(string, string), object>>();
Dictionary<(string, string), object> row_usable = new Dictionary<(string, string), object>();
foreach (DataTable table in tables)
{
Dictionary<(string, string), object> row;
foreach (DataRow dr in table.Rows)
{
row = new Dictionary<(string, string), object>();
foreach (DataColumn col in table.Columns)
{
string FirstcolName = table.Rows[0][col.ColumnName].ToString(); //index the first row with the column name
string SecondcolName = table.Rows[1][col.ColumnName].ToString(); //index the 2nd row with the column name
row.Add((FirstcolName, SecondcolName), dr[col]); //add the column names as keys and data as values in the dicttionary.using a tuple of two column headers as key
}
int index = table.Rows.IndexOf(dr); //indexing eachrow to cnvert the value in each row to string
rows.Add(index.ToString(), row); //converting to string
rows.Remove("0");
rows.Remove("1");
rows.Remove("3");
List<(string, string)> row_Keys = new List<(string, string)>(row.Keys);
List<object> row_vals = new List<object>(row.Values);
row_usable = row_Keys.Zip(row_vals, (k, v) => new { k, v }).ToDictionary(x => x.k, x => x.v);
}
dict_coll.Add(table.TableName.ToString(), rows);
}
var guid_data = rows.ElementAt(0).Value; //or use trygetvalue()
var SURVEY_ID = ("SurveyParameter", "SurveyId");
var BOREHOLE_ID = ("BoreholeParameter", "BoreholeId");
var LAYER_ID = ("Parameter", "LayerId");
Dictionary<string, object> guid_dict = new Dictionary<string, object>();
Dictionary<string,Dictionary<string, Dictionary<object, object>>> data_dict =
new Dictionary<string, Dictionary<string, Dictionary<object, object>>> ();
//Dictionary<string,object?> data_dict =
// new Dictionary<string,object?>();
//Hashtable data_dict = new Hashtable;
Hashtable restructured = new Hashtable();
restructured.Add("ParameterGuid", guid_dict);
restructured.Add("Data", data_dict);
List<string> guid_dict_keys = guid_dict.Keys.OfType<string>().ToList();
List<string> data_dict_keys = data_dict.Keys.OfType<string>().ToList();
foreach (Dictionary<string, Dictionary<(string, string), object>> datasets in dict_coll.Values) // datasets= Dictionary<string, Dictionary<(string, string), object>>>no need for specified types with hashtable but have to cast the value KeyValuePair<string, Dictionary<string, Dictionary<(string, string), object>>>
{
foreach (KeyValuePair<(string, string), object> i in guid_data)
{
if (i.Key.Item1.EndsWith("Parameter") & !guid_dict_keys.Contains(i.Key.Item2))//string.IsNullOrEmpty()//convert guidict to single string //guid_dict.Keys i.Key.Item2))
{
guid_dict[i.Key.Item2] = i.Value; //convert guid
}
}
foreach (Dictionary<(string, string), object> dataset in datasets.Values) //NULlMaybe/get dict(tuple,value) /KeyValuePair<(string, string), object>datasets.Value as Hashtable, KeyValuePair<(string, string), object>
{
// get survey/borehole/layer Ids
string target_layer_id = dataset[LAYER_ID].ToString();
string target_survey_id = dataset[SURVEY_ID].ToString(); //
string target_borehole_id = dataset[BOREHOLE_ID].ToString();//
//NULL
var finished = false;
while (!finished)
{
if (data_dict.ContainsKey(target_survey_id))
{
var survey_set = data_dict[target_survey_id];
var boreholes = survey_set["Boreholes"];
if (boreholes.ContainsKey(target_borehole_id))
{
var borehole_set = boreholes[target_borehole_id] as Dictionary<string, Dictionary<object, object>>;
//Hashtable layers = new Hashtable();
var layers = borehole_set["Layers"] as Dictionary<object, object>;
if (layers.ContainsKey(target_layer_id))
{
{ }
finished = true;
}
else
{
layers.Add(target_layer_id, new Dictionary<string, Dictionary<object, object>>
{
{"Parameter", new Dictionary<object, object>() },
{"LayerSetting", new Dictionary<object, object>()}
});
foreach (KeyValuePair<(string, string), object> cat_nam_val in dataset) // this is a hashtable version of dataset which is inside datasets.Values
{
var val_dict = new Dictionary<object, object>(); // Dictionary<string, object>();
try
{
var val_dicttt = layers[target_layer_id] as Dictionary<string, Dictionary<object, object>>; // extra variable for casting
val_dict = val_dicttt[cat_nam_val.Key.Item1];
}
catch
{
}
if (IsDictionary(val_dict)) ;
{
val_dict.Add(cat_nam_val.Key.Item2, cat_nam_val.Value);
}
}
finished = true;
}
}
else
{
boreholes.Add(target_borehole_id, new Dictionary<string, Dictionary<object, object>>
{
{ "BoreholeParameter", new Dictionary<object, object>() },
{ "BoreholeSetting", new Dictionary<object, object>()},
{ "Layers", new Dictionary<object, object>() }
});
foreach (KeyValuePair<(string, string), object> cat_nam_val in dataset) // this is a hashtable version of dataset which is inside datasets.Values
{
var val_dict = new Dictionary<object, object>();
try
{
var val_dictt = boreholes[target_borehole_id] as Dictionary<string, Dictionary<object, object>>;
val_dict = val_dictt[cat_nam_val.Key.Item1];
}
catch
{
}
if (IsDictionary(val_dict)) ;
{
val_dict.Add(cat_nam_val.Key.Item2, cat_nam_val.Value);
}
}
}
}
else
{
//1.use hashtable
//Dictionary<string, object> survey_nested_dict = new Dictionary<string, object>();
data_dict.Add(target_survey_id, new Dictionary<string, Dictionary<object, object>>());
data_dict[target_survey_id].Add("SurveyParameter", new Dictionary<object, object>()); //new Dictionary<object, object>()
data_dict[target_survey_id].Add("SurveySetting", new Dictionary<object, object>());
data_dict[target_survey_id].Add("Boreholes", new Dictionary<object, object>());
foreach (KeyValuePair<(string, string), object> cat_nam_val in dataset) // this is a hashtable version of dataset which is inside datasets.Values
{
var val_dict = new Dictionary<object, object>();// Dictionary<string, object>();
try
{
val_dict = data_dict[target_survey_id][cat_nam_val.Key.Item1];
}
catch
{
}
if (IsDictionary(val_dict)) ;
{
val_dict.Add(cat_nam_val.Key.Item2, cat_nam_val.Value);
}
}
}
}
}
}
string json_guid = JsonConvert.SerializeObject(guid_dict, Formatting.Indented);
File.WriteAllText(@"D:\Data\test_guid_dict.json", json_guid);
string json = JsonConvert.SerializeObject(dict_coll, Formatting.Indented);
File.WriteAllText(@"D:\Data\test2.json", json);
string json_data = JsonConvert.SerializeObject(data_dict, Formatting.Indented);
File.WriteAllText(@"D:\Data\test_data_dict.json", json_data);
string json_final_data = JsonConvert.SerializeObject(restructured, Formatting.Indented);
File.WriteAllText(@"D:\Data\test_restructured_data_dict.json", json_final_data);
}
}
}
