December 8, 2008 at 6:17 pm
Hi all,
i would want to be able to import data from an excel file into a MS access database.. Pls check my codes to see what is wrong.
[font="Verdana"]1. I've created a Primary key - APCITID increment of 1 in MS Access table
2. The table name is called "APCITI"[/font]
THE CODING IS:
Private Sub startBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startBtn.Click
If TextBox1.Text = "" Then
Me.Close()
End If
Dim _filename As String = TextBox1.Text
Dim _conn As String
_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"
Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
Dim _command As OleDbCommand = New OleDbCommand()
_command.Connection = _connection
_command.CommandText = "SELECT * FROM [Sheet1$]"
da.SelectCommand = _command
Try
da.Fill(ds1, "sheet1")
MessageBox.Show("The import is complete!", "Import File", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch e1 As Exception
MessageBox.Show("Import Failed!", "Import File", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Dim daA As SqlDataAdapter
Dim conn As SqlConnection
Dim cb As SqlCommandBuilder
conn = New SqlConnection("Data Source=Servername;Initial Catalog=mydb;Integrated Security=True")
Dim sel As String = "SELECT * FROM APCITID"
daA = New SqlDataAdapter(sel, conn)
cb = New SqlCommandBuilder(daA)
daA.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds2, "APCITID")
For Each dr As DataRow In ds1.Tables(0).Rows
Dim expression As String
expression = "PMCO =" + CType(dr.Item(0), Integer).ToString
Dim drs() As DataRow = ds2.Tables(0).Select(expression)
If (drs.Length = 1) Then
For i As Integer = 1 To ds2.Tables(0).Columns.Count - 1
drs(0).Item(i) = dr.Item(i)
Next
Else
Dim drnew As DataRow = ds2.Tables(0).NewRow
For i As Integer = 0 To ds2.Tables(0).Columns.Count - 1
drnew.Item(i) = dr.Item(i)
Next
ds2.Tables(0).Rows.Add(drnew)
End If
Next
Form2.Show()
End Sub
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font]
December 8, 2008 at 9:15 pm
You need to post in the Access forum. I've moved it.
Second you need to give more information. What doesn't work, what error occurs, etc.
December 8, 2008 at 10:23 pm
The coding shows no error, however, the excel data does not get imported into the Microsoft Access database.
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font]
December 9, 2008 at 3:05 pm
Hello,
I haven't coded in .Net since long.
Just some checks to narrow the problem area
Excel file imported:
Does the MessageBox.Show("The import is complete!", "Import File", MessageBoxButtons.OK shows up? (Excel file imported)
da.Fill(ds2, "APCITID") is daA.Fill(ds2, "APCITID")?
If the record "PMCO =" + CType(dr.Item(0), Integer).ToString is found in ds2
update the row in ds2 with info of ds1
If the record "PMCO =" + CType(dr.Item(0), Integer).ToString is not found in ds2
add a new row in ds2 with info of ds1
Does ds2 have to flush it dirty records back to the database APCITID?
December 9, 2008 at 6:57 pm
the Message box shows: The file is imported!
however, when i check the Microsoft Access database, nothing gets imported.
da is - Dim da As OleDbDataAdapter = New OleDbDataAdapter()
daA is - Dim daA As SqlDataAdapter
Not very sure what the rest means too.
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font]
December 10, 2008 at 2:54 am
Is there any Error table generated in Access?
Have you checked NTFS permissions?
Have you run in a step-by-step debug mode to check what is the code doing?
Regards
Ramon
Regards Ramon
December 10, 2008 at 3:07 am
reubenfoo89 (12/8/2008)
Hi all,i would want to be able to import data from an excel file into a MS access database.. Pls check my codes to see what is wrong.
[font="Verdana"]1. I've created a Primary key - APCITID increment of 1 in MS Access table
2. The table name is called "APCITI"[/font]
THE CODING IS:
Private Sub startBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startBtn.Click
If TextBox1.Text = "" Then
Me.Close()
End If
Dim _filename As String = TextBox1.Text
Dim _conn As String
_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"
Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
Dim _command As OleDbCommand = New OleDbCommand()
_command.Connection = _connection
_command.CommandText = "SELECT * FROM [Sheet1$]"
da.SelectCommand = _command
Try
da.Fill(ds1, "sheet1")
MessageBox.Show("The import is complete!", "Import File", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch e1 As Exception
MessageBox.Show("Import Failed!", "Import File", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Dim daA As SqlDataAdapter
Dim conn As SqlConnection
Dim cb As SqlCommandBuilder
conn = New SqlConnection("Data Source=Servername;Initial Catalog=mydb;Integrated Security=True")
Dim sel As String = "SELECT * FROM APCITID"
daA = New SqlDataAdapter(sel, conn)
cb = New SqlCommandBuilder(daA)
daA.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds2, "APCITID")
For Each dr As DataRow In ds1.Tables(0).Rows
Dim expression As String
expression = "PMCO =" + CType(dr.Item(0), Integer).ToString
Dim drs() As DataRow = ds2.Tables(0).Select(expression)
If (drs.Length = 1) Then
For i As Integer = 1 To ds2.Tables(0).Columns.Count - 1
drs(0).Item(i) = dr.Item(i)
Next
Else
Dim drnew As DataRow = ds2.Tables(0).NewRow
For i As Integer = 0 To ds2.Tables(0).Columns.Count - 1
drnew.Item(i) = dr.Item(i)
Next
ds2.Tables(0).Rows.Add(drnew)
End If
Next
Form2.Show()
End Sub
This is an example only
Excel file : excelfile
userid username
database table : dbTable
userid username
1. create a sql db connection. [con]
2. create a recordset. [rs]
3. create a excel file connection [xlcon] ['Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Source\Addresses.xls']
4. create excel file recordset [xlrs]
5. open the excel file as
xlrs.open "'SELECT * FROM [Sheet1$]", xlcon
6. create a while or for loop to insert the data
rs!userid = xlrs!userid
rs!username = xlrs!username
rs.update
use this method if you wanna apply some formatting on the data. [A very slow process. but quite accurate]
7. for speed, use the query. [Insert into dbTable select * from excelfile]
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 10, 2008 at 5:02 am
Seems a bit extra work, considering that you can link to an Excel file as an external data source in Access. Have you rejected that approach?
December 10, 2008 at 6:18 pm
i've changed the connection string to:
conn = New SqlConnection("Data Source=C:\mydatabase.mdb;User Id=admin;Password=;")
There is no error but the excel data does not get inserted into the access database table.
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font]
December 11, 2008 at 2:57 am
Have you run your program in a step-by-step basis in order to debug?
Are the values correct for the fiels you are trying to insert? eg. you are trying to put a text value into a integer field.
Is there any error table created in Access?
Regards Ramon
December 11, 2008 at 6:28 pm
hey all.. thanks for all your help,
i managed to solve it using Data tables.!
😀
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font]
December 12, 2008 at 12:51 am
What do you mean "using Data tables"?
I see in your code
Dim drs() As DataRow = ds2.Tables(0).Select(expression)
What's DataRow? Even if I add a reference to Excel(11) Object Library I don't see DataRow as data type. And where do you declare ds2?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply