From Excel To MS SQL

Hello,

I have some problem with automatically exporting data from excel to MS Sql server.

To get data from excel I use this function:

VB Code:

Private Function getDataFromXLS(ByVal strFilePath As String) As DataTable
        Try
            Dim strConnectionString As String = String.Empty
 
 
            strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
            Dim cnCSV As OleDbConnection = New OleDbConnection(strConnectionString)
            cnCSV.Open()
            Dim cmdSelect As OleDbCommand = New OleDbCommand("SELECT * FROM [Book1$]", cnCSV)
            Dim daCSV As OleDbDataAdapter = New OleDbDataAdapter
            daCSV.SelectCommand = cmdSelect
            Dim dtCSV As DataTable = New DataTable
            daCSV.Fill(dtCSV)
 
            cnCSV.Close()
            daCSV = Nothing
            Return dtCSV
        Catch ex As Exception
            Return Nothing
        Finally
        End Try
    End Function
 
 
End Class

How automatically create and fill table in MS Sql with data from this datatable?

why don't you use MS SQL DTS to do it. It will allow you to choose specific colomuns and filtering and then it will create tables automatically for you.
Good luck

The idea is that not skilled users may import excel files to DB.

The interface would be very simple. They'll choose excel file and press Submit.

Then program must create new table depending on excel columns and import data from it.


Hope understood what I need.