Copy Excel Data into Access Database Table.
-
Hi All, I have one Excel sheet "Student.xls", which has different worksheet as "Sheet1","Sheet2"..like that. Consider now "Sheet1"; it has 3 columns - "Name", "Age", "Sex". All these columns has row entries. Now I have one access database, "alldata.mdb". It has One table "Record" which has 3 fields as same as column name of Excel sheet-- "Name", "Age", "Sex". I want to populate this access database with the entries of Excel sheet from "Sheet1". Can someone let me know how to do this? I tried this: // first connected xl datasheet: Dim filename As String = "C:\Student.xls" Dim con As String con = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 8.0;" Dim connection As OleDbConnection = New OleDbConnection(con) connection.Open() The above Excel connection was succesful, but how to import the data from this excel sheet into the access database. Please suggest. Thanks, R.S.
-
Hi All, I have one Excel sheet "Student.xls", which has different worksheet as "Sheet1","Sheet2"..like that. Consider now "Sheet1"; it has 3 columns - "Name", "Age", "Sex". All these columns has row entries. Now I have one access database, "alldata.mdb". It has One table "Record" which has 3 fields as same as column name of Excel sheet-- "Name", "Age", "Sex". I want to populate this access database with the entries of Excel sheet from "Sheet1". Can someone let me know how to do this? I tried this: // first connected xl datasheet: Dim filename As String = "C:\Student.xls" Dim con As String con = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 8.0;" Dim connection As OleDbConnection = New OleDbConnection(con) connection.Open() The above Excel connection was succesful, but how to import the data from this excel sheet into the access database. Please suggest. Thanks, R.S.
-
Good Article
------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
-
I have gone through this, and I tried to implement somehow. The implementation is as follows: // first connected xl datasheet: Dim filename As String = "C:\Student.xls" Dim con As String con = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 8.0;" Dim connection As OleDbConnection = New OleDbConnection(con) connection.Open() // now copying the excel column into access database table columns: Dim cmd As New OleDbCommand cmd.Connection = connection Try cmd.CommandText = "SELECT * INTO [MS Access;Database=C:\alldata.mdb].[Record] FROM [Sheet1$]" cmd.ExecuteNonQuery() connection.Close() MsgBox("The Import is Compelte") Catch ex As Exception MsgBox("Import Failed, correct Column name in the sheet!") End Try While executing I am getting following error: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. I am using Microsoft office 2007 with Access 2007 database. Please tell me how to solve this? Regards, R.S.
-
I have gone through this, and I tried to implement somehow. The implementation is as follows: // first connected xl datasheet: Dim filename As String = "C:\Student.xls" Dim con As String con = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 8.0;" Dim connection As OleDbConnection = New OleDbConnection(con) connection.Open() // now copying the excel column into access database table columns: Dim cmd As New OleDbCommand cmd.Connection = connection Try cmd.CommandText = "SELECT * INTO [MS Access;Database=C:\alldata.mdb].[Record] FROM [Sheet1$]" cmd.ExecuteNonQuery() connection.Close() MsgBox("The Import is Compelte") Catch ex As Exception MsgBox("Import Failed, correct Column name in the sheet!") End Try While executing I am getting following error: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. I am using Microsoft office 2007 with Access 2007 database. Please tell me how to solve this? Regards, R.S.
The first thing to do would be to try and populate a dataset to prove the data is first getting read from Excel. If you succeed with that then start trying to populate the access table. I think for experimenting with new code, you are trying to do too much in one step. Start small, bite sized chunks, or you could end up getting confused by it all.
Dave Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com -
The first thing to do would be to try and populate a dataset to prove the data is first getting read from Excel. If you succeed with that then start trying to populate the access table. I think for experimenting with new code, you are trying to do too much in one step. Start small, bite sized chunks, or you could end up getting confused by it all.
Dave Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.comI am trying this: Dim ds1 As New DataSet Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection) da.Fill(ds1) But getting same error: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
-
I am trying this: Dim ds1 As New DataSet Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection) da.Fill(ds1) But getting same error: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
I created a Student.XLSX file with the 3 columns as you stated in your OP. The code below successfully ran and imported the data to a dataset which was then bound to a DataGridView;
'Build the connectionstring Dim conBuilder As New OleDbConnectionStringBuilder conBuilder.Provider = "Microsoft.Jet.OLEDB.4.0" conBuilder.DataSource = "C:\\Users\\Dave Auld\\Desktop\\Student.xlsx" conBuilder.PersistSecurityInfo = False conBuilder.Add("Extended Properties", "Excel 8.0;HDR=YES;") 'Create the connection Dim con As New OleDbConnection con.ConnectionString = conBuilder.ConnectionString 'create the data command Dim com As New OleDbCommand("Select \* from \[Sheet1$\]") com.Connection = con 'create the data adapter Dim da As New OleDbDataAdapter(com) 'Create the data set Dim ds As New DataSet Dim rowcount As Integer = da.Fill(ds) 'Attach to the data grid view DataGridView1.DataSource = ds DataGridView1.DataMember = ds.Tables(0).TableNameDave Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.com -
I created a Student.XLSX file with the 3 columns as you stated in your OP. The code below successfully ran and imported the data to a dataset which was then bound to a DataGridView;
'Build the connectionstring Dim conBuilder As New OleDbConnectionStringBuilder conBuilder.Provider = "Microsoft.Jet.OLEDB.4.0" conBuilder.DataSource = "C:\\Users\\Dave Auld\\Desktop\\Student.xlsx" conBuilder.PersistSecurityInfo = False conBuilder.Add("Extended Properties", "Excel 8.0;HDR=YES;") 'Create the connection Dim con As New OleDbConnection con.ConnectionString = conBuilder.ConnectionString 'create the data command Dim com As New OleDbCommand("Select \* from \[Sheet1$\]") com.Connection = con 'create the data adapter Dim da As New OleDbDataAdapter(com) 'Create the data set Dim ds As New DataSet Dim rowcount As Integer = da.Fill(ds) 'Attach to the data grid view DataGridView1.DataSource = ds DataGridView1.DataMember = ds.Tables(0).TableNameDave Don't forget to rate messages!
Find Me On: Web|Facebook|Twitter|LinkedIn
Waving? dave.m.auld[at]googlewave.comHi, I have copied the data in a new dataset "ds1". Please tell me how to copy this "ds1" contents into Access Database. I am trying with following: Dim da2 As New OleDbDataAdapter Dim conn As OleDbConnection Dim cmd As OleDbCommand = New OleDbCommand() conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\alldata.mdb;") cmd = New OleDbCommand("SELECT * FROM Denver", con) da2 = New OleDbDataAdapter(cmd) da1.Fill(ds1, "Denver") But not able to populate the Access database. Can You guide me how to do this?? Thanks, R.S.