Click here to Skip to main content
14,875,867 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
See more:
Hi,

I have code to read excel file and save them into dataset.
Now I need to read 3 excel files like this and I need to compare the message ID in the 3 excel files,for the common message ID.

I am attaching a image to show how my data in dataset looks like

Is it better to convert the table of message ID to list<t> before doing it?

C#
using System;

using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;


namespace Excelcalling1
{
	class Program
	{
		static void Main(string[] args)
		{
			string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
					Data Source=E:\SheetForTest.xls;Integrated Security=SSPI;
					Extended Properties=Microsoft Excel 11.0 Object Library";

			//Create the connection

			System.Data.OleDb.OleDbConnection ExcelConnection =
			new System.Data.OleDb.OleDbConnection
							 (ConnectionString);

			//create a string for the query

			string ExcelQuery;

			//Sheet1 is the sheet name
			//create the query:
			//read column with heading A from the Excel file

			ExcelQuery = "Select A from [Sheet1$]"; // from Sheet1";

			//use "Select * ... " to select the entire sheet
			//create the command

			System.Data.OleDb.OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);

			//Open the connection

			//ExcelConnection.Open();
			try
			{
				ExcelConnection.Open();
			}
			catch (OleDbException e)
			{
				string errorMessages = "";

				for (int i = 0; i < e.Errors.Count; i++)
				{
					errorMessages += "Index #" + i + "\n" +
									 "Message: " + e.Errors[i].Message + "\n" +
									 "NativeError: " + e.Errors[i].NativeError + "\n" +
									 "Source: " + e.Errors[i].Source + "\n" +
									 "SQLState: " + e.Errors[i].SQLState + "\n";
				}

				System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
				log.Source = "My Application";
				log.WriteEntry(errorMessages);
				Console.WriteLine("An exception occurred. Please contact your system administrator.");
			}

			//Create a reader

			System.Data.OleDb.OleDbDataReader ExcelReader;
			ExcelReader = ExcelCommand.ExecuteReader();

			//For each row after the first
			//Message box the values in the first column i.e. column 0

			while (ExcelReader.Read())
			{
				//MessageBox.Show((ExcelReader.GetValue(0)).ToString());
				Console.WriteLine("value retrieved!!");
			}
			ExcelConnection.Close();

			
		}
	}
}


Thanks
John
Posted
Comments
ZurdoDev 26-Nov-13 7:44am
   
Where are you stuck?
Member 10408451 26-Nov-13 7:48am
   
This is the code to read a excel file and save the data of one excel file into data table.
I need to read multiple excel files and compare particular data in the excel files.
How can I do that?
Richard MacCutchan 26-Nov-13 9:02am
   
Read each sheet in turn, extract the value(s) you are interested in and then compare the different sets.

1 solution

Steps to do:

  1. define the collection of files
  2. loop through the collection of files
  3. create destination DataTable[^] object

    • inside a loop create temporary DataTable and use Load[^] method to fullfill DataTable object with data
    • finally, use Merge[^] method to merge temporary DataTable with the destination DataTable

  4. compare data


That's all ;)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900