Dsp1024-8471 avatar image
0 Votes"
Dsp1024-8471 asked SimpleSamples edited

Better way than separate lists to store excel data for processing?

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

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SimpleSamples avatar image
0 Votes"
SimpleSamples answered SimpleSamples edited

What about creating an in-memory DataSet? The DataSet could be used to update Access. Note that a DataSet consists of a collection of DataTable objects. The documentation says that a DataSet is an in-memory cache of data retrieved from a data source but it can also be data to be written and can be processed in-memory; typically LINQ is used for that.

Also look at Entity Framework. You can use it to create the DataSet and DataTables. EF will take time to learn but it is worth learning.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.