Click here to Skip to main content
15,920,632 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 ;)
 
Share this answer
 

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