C# - How to merge to MDB table values and write those values in Excel File

Hi,

I am using multiple mdb and i want to read all the table values and those rows write into excel file.

For example.

I have three mdb.

every mdb will have one table and every table will have one record.

i want to combine all three records and write into excel file in particular range.

how to combine and write...?
Gani tptTech Lead cum developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
every mdb will have one table and every table will have one record.

i want to combine all three records and write into excel file in particular range.

you probably can load the records into a datatable, and merge them together as per your requirement. then you should able to output the datatable to Excel when necessary.
PatHartmanCommented:
Link all three database tables to the same FE.  Then you can use a Union query to get data from all three tables into a single recordset.  Then you can use TransferSpreadsheet to export the union query to the spreadsheet.

Alternatively, you can use OLE automation to export each table separately to a specific set of cells.
Gani tptTech Lead cum developerAuthor Commented:
Thanks for your reply..

we are selecting multiple db and reading one by one as per below code..


foreach (String file1 in openFileDialog1.FileNames)
{

   ds = mdbDataset.ReadMdbData(file1, false);
}

But, i waint to combine the dataset of all the databases and write into Excel File.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try use the :

DataSet.Merge Method (DataTable)
https://msdn.microsoft.com/en-us/library/803bh6bc(v=vs.110).aspx

hence, you could try this (not tested)

DataSet ds_combined = new DataSet();

foreach (String file1 in openFileDialog1.FileNames)
{
   ds = mdbDataset.ReadMdbData(file1, false);
   ds_combined = ds_combined.merge(ds);
}

Open in new window

then refer to ds_combined

or simply:

foreach (String file1 in openFileDialog1.FileNames)
{
   ds.merge(mdbDataset.ReadMdbData(file1, false));
}

Open in new window

Gani tptTech Lead cum developerAuthor Commented:
getting error : cannot implicitly convert type 'void' to 'system.data.dataset' for the below line..

 ds_combined = ds_combined.merge(ds);
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ooops, try this instead:

ds_combined.merge(ds);

Open in new window


?
Gani tptTech Lead cum developerAuthor Commented:
it will not return anything.

I have one row in every table. so it should return two rows.

see my complete code below..

foreach (String file1 in openFileDialog1.FileNames)
{
	   DataSet ds_combined = new DataSet();
           DataTable res = new DataTable();
	   ds = mdbDataset.ReadMdbData(file1, false);
           res = frmMain.MergeMultiplemdb(ds);
           ds_combined.Merge(ds); // Nothing will display
}

private static DataTable MergeMultiplemdb(DataSet ds1)
{
            DataTable retval = new DataTable();

            string tablename1 = "EMP_TABLE";
            DataTable DTRes1 = ds1.Tables[tablename1];
           

            var results = (from myRow in DTRes1.AsEnumerable()
                          
                           select myRow).CopyToDataTable();

            DataTable dtTemp = (DataTable)results;

            retval = dtTemp;

          
            return retval;

}

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
how's your table in your Access databases look like? are these tables identical?

can you also post codes for method: ReadMdbData?
Gani tptTech Lead cum developerAuthor Commented:
both table structure is same and also the table values are also same some time.

public static DataSet ReadMdbData(string mdbname, bool pass)
        {
            DataSet ds = new DataSet(mdbname);
            string connectionstring = string.Empty;
            if (pass == true)
            {
                connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + mdbname + ";" + "Jet OLEDB:Database Password=XXXXXXXXX";
            }
            else
            {
                connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbname + ";";
            }
           
            using (OleDbConnection cn = new OleDbConnection(connectionstring))
            {
                cn.Open();                
                DataTable Schema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null,null,null,"TABLE"});
                for (int i = 0; i <= Schema.Rows.Count - 1; i++)
                {
                    try
                    {
                        DataTable dt = new DataTable((Schema.Rows[i]["TABLE_NAME"]).ToString());
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + (Schema.Rows[i]["TABLE_NAME"]).ToString(), cn))
                        {
                            adapter.Fill(dt);
                        }
                        ds.Tables.Add(dt);
                    }
                    catch (Exception)
                    {

                    }                   
                }
                cn.Close();
            }            
            return ds;
        }

Open in new window



NOTE : it will return last table value in the loop
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
just found that you declared the DataSet ds_combined within the foreach loop, which is a wrong approach.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I tried to load the datatable that derived from ds_combined into a datagridview and it's working well.

openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            if (openFileDialog1.FileName != "")
            {
                DataSet ds_combined = new DataSet();
                foreach (String file1 in openFileDialog1.FileNames)
                {
                    DataTable res = new DataTable();
                    ds = mdbDataset.ReadMdbData(file1, false);
                    res = frmMain.MergeMultiplemdb(ds);
                    ds_combined.Merge(ds);
                }
                dataGridView1.AutoGenerateColumns= true;
                dataGridView1.DataSource = ds_combined.Tables[0];
            }

Open in new window

Untitled.png
Gani tptTech Lead cum developerAuthor Commented:
Thanks for your mentioning wrong place.

How you are getting the results..?

because i am using the above code. But,  res = frmMain.MergeMultiplemdb(ds);

"res" return Datatable.

How you are using "ds" in merge section. see the code below

ds_combined.Merge(ds);

I corrected and i used "res" instead of "ds"

ds_combined.Merge(res);.

eventhogu i am getting table1 and table2 both in seperate (for my case two databases.. not merging.

when i print below after for eachloop getting only one record.

Datatable ds_Final = ds_combined.Tables[0];

How it is possible. eventhough it is not merging and display table1 and table2,etc..
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if the purpose of:
res = frmMain.MergeMultiplemdb(ds);
is just to get the data table, you can directly use:
res = ds.Tables["EMP_TABLE"];

Open in new window

as well.
if you want to get a combined result of data table, try query it after the foreach loop.
openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            if (openFileDialog1.FileName != "")
            {
                DataSet ds_combined = new DataSet();
                foreach (String file1 in openFileDialog1.FileNames)
                {
                    ds = mdbDataset.ReadMdbData(file1, false);
                    //res = frmMain.MergeMultiplemdb(ds);                    
                    ds_combined.Merge(ds); // Nothing will display
                }
                DataTable res = ds_combined.Tables["EMP_TABLE"];
                dataGridView1.AutoGenerateColumns= true;
                dataGridView1.DataSource = res;
            }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gani tptTech Lead cum developerAuthor Commented:
Excellent. done a very good job.

the meaning of using using the procedure is, i want to use only specific columns in a table. (means it will not required to read all columns).

Note : How do we specify the particular columns in the table..

 DataTable res = ds_combined.Tables["EMP_TABLE"]; (instead of displaying all the columns, iwant to display only particular columns)..

How to specify the particular column in   the below code.

 DataTable res = ds_combined.Tables["EMP_TABLE"];

Thanks for your excellent and great help.

Kudoos..
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
How to specify the particular column in   the below code.

 DataTable res = ds_combined.Tables["EMP_TABLE"];

a quick fix would be remove the columns after that, like:

DataTable res = ds_combined.Tables["EMP_TABLE"];
                String fieldToKeep = "Field1";
                int recCnt = res.Columns.Count;
                for (int i = recCnt - 1; i >= 0; i--)
                {
                    if (res.Columns[i].ColumnName != fieldToKeep)
                    {
                        res.Columns.RemoveAt(i);
                    }
                }

Open in new window

Gani tptTech Lead cum developerAuthor Commented:
if more than two fields to maintain, shall is use below code..?

DataTable res = ds_combined.Tables["EMP_TABLE"];
                String fieldToKeep1 = "Field1";
                string fieldToKeep2 = "field2";
               string fieldToKeep3 = "field3:'
               //etc....
                int recCnt = res.Columns.Count;
                for (int i = recCnt - 1; i >= 0; i--)
                {
                    if (res.Columns[i].ColumnName != fieldToKeep1)
                    {
                        res.Columns.RemoveAt(i);
                    }
                     if (res.Columns[i].ColumnName != fieldToKeep2)
                    {
                        res.Columns.RemoveAt(i);
                    }
                      if (res.Columns[i].ColumnName != fieldToKeep3)
                    {
                        res.Columns.RemoveAt(i);
                    }
                }

Open in new window


Thanks....
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can try like this as well:

DataTable res = ds_combined.Tables["EMP_TABLE"];
                String[] fieldsToKeep = new string[] { "ID", "Field1" };
                int recCnt = res.Columns.Count;
                for (int i = recCnt - 1; i >= 0; i--)
                {
                    Boolean KeepIt = false;
                    for (int j = 0; j < fieldsToKeep.Length; j++)
                    {
                        if (res.Columns[i].ColumnName == fieldsToKeep[j])
                        {
                            KeepIt = true;
                            break;
                        }
                    }
                    if (KeepIt == false)
                    {
                        res.Columns.RemoveAt(i);
                    }
                }

Open in new window

Gani tptTech Lead cum developerAuthor Commented:
Thanks and perfect...kudoos...

It's working....
Gani tptTech Lead cum developerAuthor Commented:
Perfect Solution..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
mdb

From novice to tech pro — start learning today.