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

• Download source - 67.5 KB

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:

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

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