CODEWITHSUNDEEP
c#compare
Dani
Dani

Reputation: 2550

The best way of comparing tables using only MS Office and C#

Working on a pet project concerning efficiency and data and this is the mission statement:

I have an Access DB with x amount of tables , each containing upwards of 2000 to a max of around 15000 entries in the format of [shipment][box][serial][name][region]. Now, I have another table(called Bluuur) with n amount of entries and I'd like to compare this table (contains serials) to all the tables in the Access DB and return the serial matches along with the name of the record which matched. So the output should be something like : Timmy 360 (for a compare in which Timmy had 360 matches to Bluuur)

note: I'm developing an application to do this

Upvotes: 0

Views: 78

Answers (2)

Denis Mazourick
Denis Mazourick

Reputation: 1455

I would use the OleDbConnection with connection string like:

OleDbConnection connToFile = new OleDbConnection(String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=""Excel 8.0;HDR=Yes"";", fileName));

Similar for MS Access. Then load both tables to the memory and compare.

Update Ok, sorry that I didn't got your question initially. The answer would more depend from requirements:

  1. Is the table in XLS static or dynamic? If it is static then import the XLS into MS Access and, if you just need to get values for yourself use, use Query Editor to select and join tables like: select a.Name, count(b.ID) from tableA a inner join tableB b on a.Name = b.Name group by a.Name
  2. If the table in XLS is dynamic, but once again you need to work with it for your own purposes, create the linked data source in MS Access to that XLS file and once again use query editor to perform the selection.
  3. If the purpose of all this is to have the web page/application, which will connect to both the Microsoft Access and XLS and will join data, and will do that regularly you have 2 potential solutions: do it in memory, or do it using saved query and then use OleDbConnection/OleDbDataAdapter to load data into the application and display them to user. The memory approach may not be the best by performance so write the MS Access query, which will join and group data as you need and use OleDbConnection to connect MS Access MDB file and execute the query. OR, if you need to do this for multiple tables in MS Access, write the query text yourself directly in the code, execute for each join and then summ results.

Upvotes: 1

pabdulin
pabdulin

Reputation: 35255

If I underastand correctly then one table (to which you need to compare) is not in MS Access DB. Quick solution seems as follows: import "Bluur" table to Access database (most probable it's possible with Access import data wizard). Now you can use simple JOIN queries to test agains all other table in DB.

Upvotes: 0

Related Questions