Question Importing Excel to already established SQL DB visual basic

[droyce]

I have a program which needs excel data imported into a SQL DB table. I have done a lot of research into how to do it but have not found anything that is usefull. the SQL DB table is already set with data in it and the new data is supplied in an excel format and so i need to import it making sure the data is going into the right columns.

any help would be much appreciated.

 

[cjard]

Use the DW2 link in my signature to learn how to link to an SQLS database using datasets.
Follow a similar advice but using a connection string from ConnectionStrings.com to get your excel data read into another dataset (using a dataadapter is easier)

Programmaticaly move the data from the excel dataset / tables into the SQLS dataset / tables

Ask the relevant tableadapter to Update() the SQLS dataset/tables

If you dont understand any terms here (like tableadapter; which is NOT a dataadapter) read the DW2 link, section Creating a Simple Data App

You can cut out the programmatic transfer if you can code an SQL to read from the source file into the columns of the SQLS datatable. i.e. your excel reader query might look something like:
SELECT [column0] as PersonID, [column1] as PersonName FROM [Sheet1$]

If your SQLS datatable has columns PersonID, PersonName

...but you will need to use a dataadapter to do the read (you'll probably be doing so anyway)

 

[droyce]

Thanks i will give it go and let you know

 

[droyce]

I have added the table adaptor and dataset for the SQL table where the data is required to go. but i am unsure how to get data from the excel dataset/table to the sql dataset/table and do an update table adaptor comand.

Any help is most appreciated.


Dim plmExcelCon As System.Data.OleDb.OleDbConnection

Try
Dim ldExcelDS As System.Data.DataSet
Dim cmdLoadExcel As System.Data.OleDb.OleDbDataAdapter
Dim plmPathExcelFile As String

plmPathExcelFile = txtImportFileLocation.Text.ToString

plmExcelCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + plmPathExcelFile + ";Extended Properties=Excel 12.0;")

cmdLoadExcel = New System.Data.OleDb.OleDbDataAdapter("select * from [" + txtImportSheetName.Text + "$]", plmExcelCon)

ldExcelDS = New System.Data.DataSet

cmdLoadExcel.Fill(ldExcelDS)

plmExcelCon.Close()

Catch ex As Exception

MessageBox.Show("Invalid Excel Sheet Name")

End Try