Importing Excel data to Access

[Totto]

Hi,
What is the best practice when importing Excel data to an access database?

Totto

 

[Cor Ligthert]

Totto,

Do you have MS Access or only an Access database. In the first case use the
import functions.

Cor

 

[Totto]

Ohh, maybe I should mention that it's from a C# windows app.

 

[Cor Ligthert]

Ohh, maybe I should mention that it's from a C# windows app.Use the OleDB fill to read that access file.

This is a VBNet sample, I did not directly see a C# however the difference
is so slight that this should go in my opinion

http://msdn.microsoft.com/library/d...tskcodeexamplereadingexceldataintodataset.asp

I hope this helps,

Cor

 

[Sambathraj]

Hi,
I think for a large amount of data, and if you want programming support you
can use DTS packages of SQl server. You can also use the SQL server import
and export data wizard to achieve this. At the end you will have an option
to save to an DTS package which you can modify as you wish.
Regards
Sambath

 

[Totto]

Hi Cor,
This looks nice!
Tanks

Use the OleDB fill to read that access file.

This is a VBNet sample, I did not directly see a C# however the difference
is so slight that this should go in my opinion

http://msdn.microsoft.com/library/d...tskcodeexamplereadingexceldataintodataset.asp

I hope this helps,

Cor

 

[Paul Clement]

¤ Hi,
¤ What is the best practice when importing Excel data to an access database?
¤
¤ Totto
¤

You should be able to perform the import directly:

Public Sub ImportExcelToAccess()

Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

Try
AccessConn.Open()

'New table
'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Data] FROM
[Excel 8.0;DATABASE=e:\My Documents\Book10.xls;HDR=NO;IMEX=1].[Sheet1$]", AccessConn)
'Existing table
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Data] (Col1, Col2,
Col3, Col4) SELECT F1, F2, F3, F4 from [Excel 8.0;DATABASE=E:\My
Documents\Book10.xls;HDR=No;IMEX=1].[Sheet1$];", AccessConn)
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
AccessConn.Close()
End Try

End Sub


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Totto]

Hi Paul,
This looks great!

Tanks