Click here to Skip to main content
14,573,754 members

Migrating Legacy Microsoft Access Database to Excel (or any other database) - Complete Application and Code

Rate this:
4.45 (3 votes)
Please Sign up or sign in to vote.
4.45 (3 votes)
1 Jun 2020CPOL
Helps migrate your Microsoft Access database to Excel
This application helps you to migrate any legacy Microsoft Access database to Excel or any other database, use the application as is or learn how to customize as per your need.

Introduction

Recently, I had a client who was using a legacy application created in Visual Basic with Microsoft Access and wanted to migrate the database to support newer Cloud based web applications.

The whole idea is to use the new application with the original data.

Background

The initial requirement was to have all the data exported to a format which can then be exported to any database. It was a one time process and the old system was meant to be deprecated once the new Cloud based web application is ready. We proposed a console application which will export the data to Excel/CSV which can then be used to import data into any database.

Using the Code

It's a small code where it follows the below steps to migrate the data:

  1. Connect to Access database and get list of tables.
  2. Loop through the tables and export each of the tables to a separate worksheet.
  3. Transforms data cell by cell, here you could add validations or transform the data.


Libraries used:

  1. Microsoft.Office.Interop.Excel: To simplify access to Office API objects
  2. System.Data.OleDb: .NET Framework Data Provider for OLE DB to connect to Access DB, but you can legacy database using this
     

 

//Get all data from the Source database 
DataSet ds = GetAllDataFromSource();

//Export all data to Excel
ExportDataSetToExcel(ds);

Step 1

Get all data from the source database:

private static DataSet GetAllDataFromSource()
{
    //Declare
    System.Data.DataTable userTables = null;
    OleDbDataAdapter oledbAdapter;
    DataSet ds = new DataSet();
    List<string> tableNames = new List<string>();
    using (OleDbConnection myConnection = new OleDbConnection())
    {
        myConnection.ConnectionString = ConfigurationManager.ConnectionStrings
                                        ["SourceDatabaseConnectionString"].ConnectionString;
        //Connect to Source database
        myConnection.Open();

        //Restrict the GetSchema() to return "Tables" schema information only.
        string[] restrictions = new string[4];
        restrictions[3] = "Table";
        userTables = myConnection.GetSchema("Tables", restrictions);

        for (int i = 0; i < userTables.Rows.Count; i++)
        {
            var tableName = userTables.Rows[i][2].ToString();
            oledbAdapter = new OleDbDataAdapter($"select * from {tableName}", myConnection);
            oledbAdapter.Fill(ds, $"{tableName}");

            if (ds.Tables[$"{tableName}"].Rows.Count > 0)
            {
                Console.WriteLine("Rows: " + ds.Tables[$"{tableName}"].Rows.Count);
            }
            oledbAdapter.Dispose();

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

Step 2

Export the data to Excel:

private static void ExportDataSetToExcel(DataSet ds)
        {
            //Create an Excel application instance
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook excelWorkBook = 
                      excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            //Create an Excel workbook instance and open it from the predefined location

            foreach (System.Data.DataTable table in ds.Tables)
            {
                //Add a new worksheet to workbook with the Datatable name
                Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
                excelWorkSheet.Name = table.TableName;

                //Columns
                for (int i = 1; i < table.Columns.Count + 1; i++)
                {
                    excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
                }

                //Rows
                for (int j = 0; j < table.Rows.Count; j++)
                {
                    for (int k = 0; k < table.Columns.Count; k++)
                    {
                        try
                        {
                            excelWorkSheet.Cells[j + 2, k + 1] = 
                                                 table.Rows[j].ItemArray[k].ToString();
                        }
                        catch(Exception ex)
                        {
                            Console.WriteLine($"Error in table: 
                                    {excelWorkSheet.Name} - Cells - j: {j}, 
                                    k:{k}, data: {table.Rows[j].ItemArray[k].ToString()}");
                            Console.WriteLine(ex);                            
                        }                        
                    }
                }
            }
            string fileName = System.IO.Path.Combine
                              (System.Configuration.ConfigurationManager.AppSettings
                              ["TargetDirectory"], $@"test-{DateTime.Now.ToString
                              ("yyyyMMddHHmmss")}.xls");

            excelWorkBook.SaveAs(fileName);
            excelWorkBook.Close();
            excelApp.Quit();
        }

Results:

Image 1

Click to enlarge image

Feel free to modify/extend this code on Github at https://github.com/rohitsies/DataExportFromMSAccess/blob/master/README.md.

Points of Interest

Microsoft.Office.Interop is a great tool to interact with Excel and other Office applications. It provides seamless integration, saves time and efforts.

History

  • 1st June, 2020: Initial article posted
  • 2nd June, 2020: Formatting corrected

License

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

Share

About the Author

rohitsies
Software Developer (Senior)
Australia Australia
No Biography provided

Comments and Discussions

 
QuestionWhy to Excel Pin
david4life3-Jun-20 11:39
Memberdavid4life3-Jun-20 11:39 
AnswerRe: Why to Excel Pin
rohitsies17-Jun-20 13:20
Memberrohitsies17-Jun-20 13:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 1 Jun 2020

Stats

2.6K views
101 downloads
2 bookmarked