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...?
Ganesh STech Lead cum developerAsked:
Who is Participating?
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 ChongCommented:
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.
0
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.
0
Ganesh STech 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
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

0
Ganesh STech 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);
0
Ryan ChongCommented:
ooops, try this instead:

ds_combined.merge(ds);

Open in new window


?
0
Ganesh STech 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

0
Ryan ChongCommented:
how's your table in your Access databases look like? are these tables identical?

can you also post codes for method: ReadMdbData?
0
Ganesh STech 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
0
Ryan ChongCommented:
just found that you declared the DataSet ds_combined within the foreach loop, which is a wrong approach.
0
Ryan ChongCommented:
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
0
Ganesh STech 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..
0
Ryan ChongCommented:
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

0

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
Ganesh STech 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..
0
Ryan ChongCommented:
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

0
Ganesh STech 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....
0
Ryan ChongCommented:
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

0
Ganesh STech Lead cum developerAuthor Commented:
Thanks and perfect...kudoos...

It's working....
0
Ganesh STech Lead cum developerAuthor Commented:
Perfect Solution..
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.