For this particular example, I have an Excel Workbook with one Worksheet, which is named by default, Sheet1. In it, I have a header row, with FirstName, SecondName and Age as the column headings. Thereafter is a selection of data:
I also have an Access database called contacts.mdb, with a table called Persons. The schema of the table matches the Excel sheet, with the addition of an Autonumber column:
Both of these files are in the App_Data folder of a web site, so the only thing to do is to read the data out of Excel and into Access. As I mentioned in my previous article, the Jet 4.0 driver can connect to a variety of file types, including Access and Excel. The only difference each time is in the connection string. For Excel, a typical connection string will look like this:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0";
Making sure I have referenced System.Data.OleDb, here is the code that transfers the records across from Excel to Access:
[C#]
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[Persons] SELECT * FROM [Sheet1$]";
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim Access As String = Server.MapPath("App_Data/contacts.mdb")
Dim Excel As String = Server.MapPath("App_Data/Book1.xls")
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & _
";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
Using cmd As New OleDbCommand()
cmd.Connection = conn
cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].[Persons] SELECT * FROM [Sheet1$]"
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
Notice that the INSERT INTO statement points to the Access database by stating the type of application that is to be used (MS Access;), and the full path to the actual mdb file. Following that is the dot separator and the name of the target table. As with all references to worksheets in Excel, the name of the sheet in the FROM clause has a $ sign appended to it. The above statement will result in the records from Excel being appended to the relevant table in Access.
Creating a new Table
A slight alteration to the SQL will result in a new table being created in the Access database, into which the records will be copied, along with the header row as field names:
[C#]
string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel +";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT * INTO [MS Access;Database=" + Access + "].[New Table] FROM [Sheet1$]";
conn.Open();
cmd.ExecuteNonQuery();
}
}
[VB]
Dim Access As String = Server.MapPath("App_Data/contacts.mdb")
Dim Excel As String = Server.MapPath("App_Data/Book1.xls")
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & _
";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
Using cmd As New OleDbCommand()
cmd.Connection = conn
cmd.CommandText = "SELECT * INTO [MS Access;Database=" & Access & "].[New Table] FROM [Sheet1$]"
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using
And the result:
