Hi

I'm looking for a sample to importa data from a MS Excel file to SQL Server 2005 table, I'm working with an Inventory table (product, sku, price, cost...) the idea is importa the data directly to SQL Server table that of course is user in a SF application.

I already search in Google and MSDN forums but i can fin anything like this, just to open Excel files, and I need to load the data in a table. Hope someone have a small sample?

Kindest regards

Everything is possible, just keep trying...

Juan,

Probably the simplest thing to do is use the Sql Import Wizard to import excel data. I attached a screen shot.

Paul

For more detail, see http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

Hi

The SQL Wizard is great but not present in the Express edition, I' need the users can import the data. So I need to add some code to the application.

Thanks

Everything is possible, just keep trying...

Hey Juan.

I just did a search on "import excel into sql server vb.net" and got lots of samples. Maybe you could get an idea there.

Here is code I have in an app that allows user to select a spreadsheet and import, if it helps. It ended up being a little finicky depending on the OLE version installed on the PC but it worked fine. I had a good reason at the time to define the column names (forget what it was), you could just do a "*" in the select I am sure. In the end it just populates a datatable, I then loaded to a grid for user display and further processing.

I remover a lot of crud you wouldnt have cared about, hopefully nothing important

Private Sub loadSpreadsheet(ByVal millprefix As String)

        Dim connstr As String = String.Empty

        Dim selectStr As String = String.Empty

        Dim colnames As String = String.Empty

        Dim sheetname As String = String.Empty

        Dim millprices As DataTable = New DataTable

        millprices.TableName = "millprices"

                millprices.Columns.Add("STYLE", System.Type.GetType("System.String"))

                millprices.Columns.Add("STYDSC", System.Type.GetType("System.String"))

                millprices.Columns.Add("EFDT", System.Type.GetType("System.String"))

                millprices.Columns.Add("PTYPDSC", System.Type.GetType("System.String"))

                millprices.Columns.Add("UOM", System.Type.GetType("System.String"))

                millprices.Columns.Add("CUTPRY", System.Type.GetType("System.Decimal"))

                millprices.Columns.Add("ROLPRY", System.Type.GetType("System.Decimal"))

                colnames = "STYLE,STYDSC,EFDT,PTYPDSC,UOM,CUTPRY,ROLPRY"

         If UcGetFile1.SelectedFileExtension = ".csv" Then

            connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & UcGetFile1.SelectedFilePath & ";Extended Properties=Text;"

            selectStr = "SELECT " & colnames & " FROM " & UcGetFile1.SelectedFilenameWithoutPath

        Else

            'read this, registry changes required in some version of JET install

            'Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel

            'TypeGuessRows = 0

            'ImportMixedTypes = Text

             'http://blog.lab49.com/archives/196

            sheetname = UcGetFile1.SelectedFilenameWithoutPath

            Dim filelen As Integer

            filelen = sheetname.Trim.Length

            sheetname = sheetname.Substring(0, filelen - 4)

            'connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & UcGetFile1.SelectedFilenameWithPath & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"

             connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

                UcGetFile1.SelectedFilenameWithPath & _

                ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

            selectStr = "SELECT " & colnames & " FROM [" & sheetname & "$]"

        End If

         Dim sConnectionString As String = connstr

         Dim objConn As New OleDbConnection(sConnectionString)

        objConn.Open()

         Dim objCmdSelect As New OleDbCommand(selectStr, objConn)

         Dim objAdapter1 As New OleDbDataAdapter()

        objAdapter1.SelectCommand = objCmdSelect

         Dim objDataset1 As New DataSet()

        objDataset1.Tables.Add(millprices)

         objAdapter1.Fill(objDataset1, "millprices")

         dgvSpreadsheetImport.DataSource = objDataset1.Tables(0).DefaultView

        objConn.Close()

    End Sub

Keith Chisarik

Ken

Thanks for the sample, gives me some ideas, I notice that this routine creates a new table, I already have the table (wich includes key fiels, relationships) in which I want to import (should I say append the data) the data from Excel.

I think I have to learn something about "SQL Bulk Insert"

Thanks to all, maybe if something can be done could be a good adition to samples section. I think that many people uses Excel every day and when us as programmers create routines to import the information from Excel to our database and tables so they dont have to add every new record for start working it's a good help. Of course depends the type of application.

Regards.

Everything is possible, just keep trying...