Import excel spreadsheets into access db.

I fixed the issues with the Try/catch and the exception it was throwing, but I am still only getting one row (header) in each table inserted. I have a datagridview attached to the incoming data and it shows its there, but why only one row is being insert is I can't figure out.

Help, any please tell me why?

I updated the code with specifics for my project and the code works great, but it is not transfering all the data, it is just trying to create the tables inside the database but crashes if the tables exist, so I added the Try/Catch to catch the except and it still throwing an exception. If I comment out the table creation portion of the code, it only imports the first row of data which is the column names, but there are additional rows and they are not transfered.

private void button5_Click(object sender, EventArgs e)
        {
            // Excel connection string
                string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/temp/TestFolder/Test.xlsx;Extended Properties='Excel 12.0;HDR=YES;'";
            // Access connection string
                string accessConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/temp/TestFolder/TestDatabase.mdb;Persist Security Info=False;";

                using (OleDbConnection excelConn = new OleDbConnection(excelConnStr))
                using (OleDbConnection accessConn = new OleDbConnection(accessConnStr))
                {
                    excelConn.Open();
                    accessConn.Open();
                 
                    // Get the list of sheet names from Excel
                    DataTable excelSchema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

           

                    foreach (DataRow row in excelSchema.Rows)
                    {
                        string sheetName = row["TABLE_NAME"].ToString();

                        // Remove the trailing $ sign from the sheet name
                        if (sheetName.EndsWith("$"))
                        {
                            // Import data from the sheet to Access table
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter($"SELECT * FROM [{sheetName}]", excelConn))
                            {
                                DataTable excelData = new DataTable();
                                adapter.Fill(excelData);

                                // Create the corresponding table in Access (assuming the table doesn't exist)
                                string createTableQuery = $"CREATE TABLE [{sheetName.Replace("$", "")}] (";

                                foreach (DataColumn column in excelData.Columns)
                                {
                                    createTableQuery += $"[{column.ColumnName}] TEXT, ";
                                }


                            try // Catch if the table name exist already in database
                            {
                                createTableQuery = createTableQuery.TrimEnd(',', ' ') + ")";
                                OleDbCommand createTableCommand = new OleDbCommand(createTableQuery, accessConn);
                                createTableCommand.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                // Insert data into Access table
                                using (OleDbDataAdapter accessAdapter = new OleDbDataAdapter($"SELECT * FROM [{sheetName.Replace("$", "")}]", accessConn))
                                using (OleDbCommandBuilder builder = new OleDbCommandBuilder(accessAdapter))
                                {
                                    accessAdapter.InsertCommand = builder.GetInsertCommand();
                                    accessAdapter.Update(excelData);
                                }
                            }


                            }
                        }
                    }
                }

                Console.WriteLine("Data imported successfully.");
            }

Here's a basic outline of how you can achieve this:

1. Connect to the Excel spreadsheet.
2. Get the list of sheet names from the Excel spreadsheet.
3. Connect to the Access database.
4. For each sheet in the Excel spreadsheet:
   • Read the data from the sheet.
   • Create a corresponding table in the Access database (if it doesn't exist).
   • Insert the data into the table in the Access database.

using System;
using System.Data;
using System.Data.OleDb;

namespace ExcelToAccessImport
{
    class Program
    {
        static void Main(string[] args)
        {
            // Excel connection string
            string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path_to_excel_file.xlsx;Extended Properties='Excel 12.0;HDR=YES;'";

            // Access connection string
            string accessConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=path_to_access_database.accdb;Persist Security Info=False;";

            using (OleDbConnection excelConn = new OleDbConnection(excelConnStr))
            using (OleDbConnection accessConn = new OleDbConnection(accessConnStr))
            {
                excelConn.Open();
                accessConn.Open();

                // Get the list of sheet names from Excel
                DataTable excelSchema = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                foreach (DataRow row in excelSchema.Rows)
                {
                    string sheetName = row["TABLE_NAME"].ToString();

                    // Remove the trailing $ sign from the sheet name
                    if (sheetName.EndsWith("$"))
                    {
                        // Import data from the sheet to Access table
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter($"SELECT * FROM [{sheetName}]", excelConn))
                        {
                            DataTable excelData = new DataTable();
                            adapter.Fill(excelData);

                            // Create the corresponding table in Access (assuming the table doesn't exist)
                            string createTableQuery = $"CREATE TABLE [{sheetName.Replace("$", "")}] (";

                            foreach (DataColumn column in excelData.Columns)
                            {
                                createTableQuery += $"[{column.ColumnName}] TEXT, ";
                            }

                            createTableQuery = createTableQuery.TrimEnd(',', ' ') + ")";
                            OleDbCommand createTableCommand = new OleDbCommand(createTableQuery, accessConn);
                            createTableCommand.ExecuteNonQuery();

                            // Insert data into Access table
                            using (OleDbDataAdapter accessAdapter = new OleDbDataAdapter($"SELECT * FROM [{sheetName.Replace("$", "")}]", accessConn))
                            using (OleDbCommandBuilder builder = new OleDbCommandBuilder(accessAdapter))
                            {
                                accessAdapter.InsertCommand = builder.GetInsertCommand();
                                accessAdapter.Update(excelData);
                            }
                        }
                    }
                }
            }

            Console.WriteLine("Data imported successfully.");
        }
    }
}

Hope this will help.

Thanks