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

Dsp 1024 51 Reputation points
2021-10-14T23:21:27.937+00:00

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.

Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,671 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sam of Simple Samples 5,516 Reputation points
    2021-10-15T03:26:49.54+00:00

    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.

    0 comments