We help IT Professionals succeed at work.

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

Gani tpt
Gani tpt asked
on
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...?
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
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.
Distinguished Expert 2017

Commented:
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 developer

Author

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.
Ryan ChongSoftware Team Lead

Commented:
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 developer

Author

Commented:
getting error : cannot implicitly convert type 'void' to 'system.data.dataset' for the below line..

 ds_combined = ds_combined.merge(ds);
Ryan ChongSoftware Team Lead

Commented:
ooops, try this instead:

ds_combined.merge(ds);

Open in new window


?
Gani tptTech Lead cum developer

Author

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 ChongSoftware Team Lead

Commented:
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 developer

Author

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 ChongSoftware Team Lead

Commented:
just found that you declared the DataSet ds_combined within the foreach loop, which is a wrong approach.
Ryan ChongSoftware Team Lead

Commented:
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 developer

Author

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..
Software Team Lead
Commented:
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

Gani tptTech Lead cum developer

Author

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 ChongSoftware Team Lead

Commented:
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 developer

Author

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 ChongSoftware Team Lead

Commented:
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 developer

Author

Commented:
Thanks and perfect...kudoos...

It's working....
Gani tptTech Lead cum developer

Author

Commented:
Perfect Solution..