Importing from excel into sql server

[Chill3002]

How do you import an excel file into a Sql Server database in vb.net? I am new to vb.net and I am creating a program that users need to have the ability to import from an excel file on a regular basis but there are hundreds of columns in this excel file and they only need 3 columns of data (with the 3rd column changing on a daily basis). How would I go about this? If i use an opendialog to browse the file would they be able to specify which columns to import?

[Shaggy Hiker]

OpenDialog would be a good way to start if the actual Excel file changes each time. If the Excel file is predictable, you might even skip that step. However, that won't help with choosing columns. What you might do is that once you have the Excel file, read the whole spreadsheet into a datatable using ADO.NET (there should be plenty of examples of doing this, though I don't have one). This may not be a good idea if there are LOTS of rows, and it isn't necessarily ideal anyways, since you say there are hundreds of columns, but it would be easier to work with a datatable rather than a spreadsheet, so you might try this approach first. After all, the question is whether or not the performance is acceptable, and it probably will be unless the total amount of data is HUGE. Hundreds of columns would be annoying, but not really an issue if the number of rows is only in the hudreds, or less.

If you can bring all the data into a datatable, then you will have an easier time asking the user which column to use in the datatable. Furthermore, once you have the three columns, updating that to SQL Server would be pretty simple.

If the performance of importing the whole thing into a datatable is too slow, then you will have to identify just the columns you need, and only import those. That would be more efficient...except that identifying the columns would be harder to do (maybe). Actually, identifying the columns could be simple: You could require the user to say something like "5", so you would import the two fixed columns and column 5. That would be fairly simple for you, but it would require the user to know the right answer, which may be MUCH harder for them. The alternative would be to come up with a list of all the column names that are potential candidates, and let the user pick from them, but building that list of names wouldn't be all that easy. The easiest way, in that case, may be to import the whole spreadsheet into a datatable, but do so with a WHERE 1 = 0 stuck onto the end of the SQL to get a datatable with no rows (all the columns, but no rows). You may also be able to query on just the schema, which could be even faster (though I don't know whether it is even possible to do this against a spreadsheet).

Those are some thoughts on the subject.

[dunfiddlin]

Imports System.Data.OleDb
 
Public Class Form1
    Dim c As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls; Extended Properties=""Excel 8.0; HDR=No; IMEX=1;""")
' Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Book1.xlsx; Extended Properties='Excel 12.0; HDR=No; IMEX=1;' ..... for xlsx format
 
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim cmd As String = "SELECT F1 As One, F3 As Three FROM [Sheet1$]" ' specific columns referred to by Fx
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd, c)
        Dim dt As New DataTable
        c.Open()
        da.Fill(dt)
        c.Close()
        DataGridView1.DataSource = dt
    End Sub
End Class