StrataFrame Forum » .NET Forums » General .NET Discussion » Import data from Excel to SQL Server 2005 table

Import data from Excel to SQL Server 2005 table

Juan Carlos Pazos — Wed, 21 Jan 2009 00:20:25 GMT

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

RE: Import data from Excel to SQL Server 2005 table

Juan Carlos Pazos — Wed, 21 Jan 2009 00:20:25 GMT

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.

RE: Import data from Excel to SQL Server 2005 table

Keith Chisarik — Tue, 20 Jan 2009 22:06:48 GMT

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

RE: Import data from Excel to SQL Server 2005 table

Ivan George Borges — Tue, 20 Jan 2009 16:08:57 GMT

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.

RE: Import data from Excel to SQL Server 2005 table

Juan Carlos Pazos — Tue, 20 Jan 2009 12:36:35 GMT

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

RE: Import data from Excel to SQL Server 2005 table

Bill Cunnien — Tue, 20 Jan 2009 08:51:43 GMT

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

RE: Import data from Excel to SQL Server 2005 table

Paul Chase — Tue, 20 Jan 2009 08:44:48 GMT

Juan,

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

Paul