I need to read data from an excel sheet, process the data and add it to an access (old 2000) database. This will be used by me only. This is for Windows 10 and WPF .Net framework project.
Example data from excel:
PartNo Quantity Designator
1234567890 5 D4,D8,D1,D6,D9
2312598766 50 R112, R50, R1... etc. (50 entries total)
I'm reading the three columns one column at the time and saving each to a separate list:
public static List<String> excelPartNoList = new List<string>();
public static List<String> excelQuantityList = new List<String>();
public static List<String> excelDesignatorList = new List<string>();
I do some processing and save the new data in another three lists:
public static List<String> dbPartNoList = new List<string>();
public static List<float> dbQuantityList= new List<float>();
public static List<List<String>> dbDesignatorsList = new List<List<string>>();
The processing just copies the PartNo to the new list. It converts the quantities list to float. It splits and orders the designators into a List<List<String>> so I can check them against the quantity to verify the count. These types are what the access database expect when inserting the records.
I had everything (processing ) working fine until I realized that there are duplicate PartNo entries. I need to combined these entries into a single entry but as I do that I need to add the quantities and append the designators entries. I was using the index of the lists to keep track of each "row" of values but with the duplicate entries I don't think that works very well. Any sorting will mess up the index to the other lists so I need a better solution. I'm no expert so what you see is what it is easy for me. BTW the lists are in a class file I call GlobalData (probably messy for you gurus ...lol).
I read that I can make a List<Myclass> with Myclass having the lists so I can sort them without messing up the index but I've never done that. I also read that I can "group" entries using Linq. I'm using a Linq line of code to soft the designators but I don't know much about it. It may be easier to combined them once they're grouped.
I'm looking for better options to do this than what I have pls. Thanks.