Import data from excel sheet data to Sql Server 2008 database

 Hello,

  I am doing an project in which i am going to get the data from the excel sheet and insert it into My SQL Server 2005 database.How can i do it,can any one help me out to get me out of this problem with an example.

0
sandynaidu
3/31/2009 11:28:25 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

4 Replies
1230 Views

Similar Articles

[PageSpeed] 17
Get it on Google Play
Get it on Apple App Store

You could use SQL Server Integration Services (SSIS) to do this.  If you need to repeat the process remember that you can host an SSIS package as a SQL Agent job and run it repeatedly, and you can kick off the job from code.

A walkthrough of this is here: http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm


Andrew
blog.andrewrivers.co.uk
0
wildmoose
3/31/2009 12:06:56 PM

sandynaidu:

 Hello,

  I am doing an project in which i am going to get the data from the excel sheet and insert it into My SQL Server 2005 database.How can i do it,can any one help me out to get me out of this problem with an example.

It's a good idea to use SSIS as suggested above. If you want to achieve it programmatically, please check this helpful article.

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
4/2/2009 5:19:13 AM

Here is a code snippet for a single table insert with some validation.

    Protected Sub lbtnUploadSites_Click(ByVal sender As Object, ByVal e As EventArgs) Handles lbtnUploadSites.Click
        Dim path As String = Server.MapPath(Me.filePath)
        If siteUpload.HasFile Then
            If Me.CheckFileType(siteUpload.FileName) Then
                Dim rand As Random = New Random()
                Dim randNum = rand.Next(1000)
                Dim fullFileName As String = path + randNum.ToString() + siteUpload.FileName
                siteUpload.PostedFile.SaveAs(fullFileName)

                Dim excelConnectionString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" _
                    & "Data Source=" & fullFileName _
                    & ";" & "Extended Properties=Excel 8.0;")
                Dim connection As OleDbConnection = New OleDbConnection(excelConnectionString)
                Dim command As OleDbCommand = New OleDbCommand("Select * FROM [SITE IMPORT$]", connection)
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(command)
                Dim ds As DataSet = New DataSet
                Try
                    connection.Open()
                    da.Fill(ds)
                    connection.Close()
                    command.Dispose()
                Catch ex As Exception
                    Me.SetAlert(ex.Message, "Error")
                Finally
                    If connection.State = ConnectionState.Open Then
                        connection.Close()
                    End If
                End Try

                Dim validationMessage As String = ValidateSiteData(ds)
                If validationMessage = "" Then
                    'Process file
                    Dim importResult As String = ProcessSiteImportFile(ds)
                    If importResult = "" Then
                        Me.SetAlert("File imported successfully.", "Success")
                        'Delete Excel file - this is where you'd do it
                    Else
                        Me.SetAlert(importResult, "Error")
                    End If
                Else
                    Me.SetAlert(validationMessage, "Error")
                    Return 'Bail out
                End If
            Else
                Me.SetAlert("This import process requires an Excel file type of .xls.  Please try again.", "Error")

            End If
        End If
    End Sub

    ''' <summary>
    ''' Sets the text, color and visibility of the alert message based on action.
    ''' </summary>
    ''' <param name="Message"></param>
    ''' <param name="Status">Success or Error or Clear</param>
    ''' <remarks></remarks>
    Protected Sub SetAlert(ByVal Message As String, ByVal Status As String)
        If Status.ToLower() = "success" Then
            lblAlert.Text = "<span style=""color:#009F00"">" + Message + "</span>"
            lblAlert.Visible = True
            lblAlert.Font.Bold = True
        ElseIf Status.ToLower() = "error" Then
            lblAlert.Text = "<span style=""color:#D41F00"">" + Message + "</span>"
            lblAlert.Visible = True
            lblAlert.Font.Bold = True
        ElseIf Status.ToLower() = "clear" Then
            lblAlert.Visible = False
        End If
    End Sub

    Protected Function ValidateSiteData(ByVal SiteData As DataSet) As String
        Try
            'Loop through DataSet and validate data
            'If data is bad, bail out, otherwise continue on with the bulk copy
            Dim sb As StringBuilder = New StringBuilder()
            For i As Integer = 0 To SiteData.Tables(0).Rows.Count - 1
                If SiteData.Tables(0).Rows(i).Item("Site Name").ToString() = "" Then 'Site Name is required
                    sb.Append("Site Name is required.<br />")
                End If
            Next
            Return sb.ToString()
        Catch ex As Exception
            Return ex.Message
        End Try
    End Function

    Protected Function ProcessSiteImportFile(ByVal SiteData As DataSet) As String
        Dim sqlConnectionString As String = connStr
        Dim connection As SqlConnection = New SqlConnection(sqlConnectionString)

        Dim command As SqlCommand = New SqlCommand("INSERT INTO dbo.iad_mstr (iad_addr1,iad_addr2,iad_addr3,iad_city,iad_pcode,iad_cell,iad_phone,iad_ext,iad_fax,iad_email,iad_created_by, iad_co_id) " _
            & "VALUES (@Address1,@Address2,@Address3,@City,@PostalCode,@Cell,@Phone,@Extension,@Fax,@Email,@CreatedBy,@CompanyID);SELECT SCOPE_IDENTITY()", connection)

        Dim newSiteID As String
        Dim companyID As Int64
        Try
            For i As Integer = 0 To SiteData.Tables(0).Rows.Count - 1
                connection.Open()
                command.Parameters.AddWithValue("@Address1", Left(SiteData.Tables(0).Rows(i).Item("Address 1").ToString(), 50))
                command.Parameters.AddWithValue("@Address2", Left(SiteData.Tables(0).Rows(i).Item("Address 2").ToString(), 50))
                command.Parameters.AddWithValue("@Address3", Left(SiteData.Tables(0).Rows(i).Item("Address 3").ToString(), 50))
                command.Parameters.AddWithValue("@City", Left(SiteData.Tables(0).Rows(i).Item("City").ToString(), 30))
                command.Parameters.AddWithValue("@PostalCode", Left(SiteData.Tables(0).Rows(i).Item("Postal Code").ToString(), 10))
                command.Parameters.AddWithValue("@Phone", Left(SiteData.Tables(0).Rows(i).Item("Phone").ToString(), 20))
                command.Parameters.AddWithValue("@Extension", Left(SiteData.Tables(0).Rows(i).Item("Extension").ToString(), 20))
                command.Parameters.AddWithValue("@Cell", Left(SiteData.Tables(0).Rows(i).Item("Mobile Phone").ToString(), 20))
                command.Parameters.AddWithValue("@Fax", Left(SiteData.Tables(0).Rows(i).Item("Fax").ToString(), 20))
                command.Parameters.AddWithValue("@Email", Left(SiteData.Tables(0).Rows(i).Item("E-Mail").ToString(), 50))
                command.Parameters.AddWithValue("@CreatedBy", Me.intUserID)
                command.Parameters.AddWithValue("@CompanyID", companyID)
                newSiteID = command.ExecuteScalar()
                command.Parameters.Clear()
                connection.Close()
            Next
            command.Dispose()
            Return ""
        Catch ex As Exception
            Return ex.Message + "<br />" + ex.StackTrace
        Finally
            If connection.State = ConnectionState.Open Then
                connection.Close()
            End If
        End Try
    End Function

View Brenden Kehren's profile on LinkedIn
Remember to mark as answer if this post answered your question.
0
b471code3
4/3/2009 5:27:06 PM

sandynaidu:

Hello,

  I am doing an project in which i am going to get the data from the excel sheet and insert it into My SQL Server 2005 database.How can i do it,can any one help me out to get me out of this problem with an example

You can do using by writing a stored proc

refer this article to read excel File using SQL Server

http://support.microsoft.com/kb/321686


MAKMark as Answer if this reply helps you
MVP ASP/ASP.Net
MVP ASP/ASP.Net
ASP.Net Hosting : Host DepotMy Site : ASPSnippets
0
mudassarkhan
4/3/2009 5:43:49 PM
Reply:

Similar Artilces:

Importing Data from Excel Sheets into SQL Server Database
Hi, Would like some help on how do I go about coverting an Excel File with columns of info into my SQL Server Database. The excel file will be uploaded from a user from my web application. I completely have no idea on where to start so any form of help is much appreciated thanks. bump bump Any help at all? Just to get me headed in the right direction....

How to Import data from excel sheet within SQL Client into SQL Server
 Hi All,   I need to  import data from excel sheet to SQL Database in SQL Server.I already done it in machine which SQL Server run.but I cant do it within SQL Client machine.while try to do it this error was occure Server: Msg 7302, Level 16, State 1, Line 2Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'.Please tell me How I do itThanks,Harsha Hi harsha  Can you post your code  If this post helps you, please mark it as Answer.Gnana Prakash SSoftware EngineerCoimbatorehttp://www.sgnanaprakash.com  Hi Gnana,this is My...

Import excel data from Microsoft excel to SQL Server usin C sharp .net
Hi Could anyone help me with the sample code in C sharp to import data from excel to SQL server 2005 using C sharp. I have a table called Accounting Calendar ACCT_DATE with 10 columns I have the excel file called example.xls with the same column name as in SQL Server.Please help me with the sample code in C sharp Thanks      Here's a simple way to do it without the need to have excel installedhttp://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx Hope this helpsJaime Don't forget to click "Mark as Answer" on the post that helped you. That wa...

How to Import a Excel Datas to SQL Server Database
How to Import a Excel Datas to SQL Server DatabaseSmileselva Open Enterprise Manager and right click on the database name into which you want to import the data. Go to All Tasks > Import Data > Click Next on the DTS Import/Export Screen. On the Choose a Data Source screen select Microsoft Excel 97-2000 in the Data Source dropdown. Browse to the excel file (browse button beside the File Name text box) click next and Select the destination (the database in which you want to import data) click next on the next dialog keep 'Copy table(s) and view(s) from the source database' > click...

Import data from excel sheet to SQL Server
How do I import the data from the excel sheel to the database table that has different fields compared to that of the excel sheet programatically or as a SysAd?I never lose, some people are just better than me at winning. INSERT INTO [dbo].[products]  ( [Stock Number], [Registration Number] )SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\stock.xls','SELECT * FROM [Expoted_Stock$]')    When i run this query it is giving error Msg 7357, Level 16, State 2, Line 2Cannot process the object "SELECT * FROM [Expoted_Stock...

importing data from excel sheet to SQl Sever database
Hi I need to select the data present in an excel sheet and insert it into SQL server 2005 database. please kindly help me with providing a code or links where i can find the code to solve this problemThanks & RegardsKishan Varma Hi,  You can go with the any of the following approaches:1. With the Bulk Copy approach. Please refer to the following code:                     http://www.codeproject.com/KB/database/SqlBulkCopy.aspx        &nbs...

Importing data from excel sheets to SQL server 2000
HiI need to transfer data frm a bunch of excel sheets to SQL server using ASP.net. Can u plz help me in doing tht?Thanx!  Hi, You can create a DTS on SQL Server that has an XML file as input that stores this information into tables. In your ASP.Net application you can configure the excel as parameter, exceute the DTS and know in which step is.Please mark the most helpful reply/replies as "Answer". My Blog Hi Thanx for the reply but I don't hv the slightest idea hw to do thtSo if u can help me wid some code samples i would be really greatful.Thanx.  This link should...

How to Data Transfer From Excel to Microsoft SQL Server Data base Using .Net.
Hai All, How to Data Transfer From Excel to Microsoft SQL Server Data base Using .Net. I have one Excel Sheet in that sheet 15 Fields (attributes) and 3000 records are there. In this 3000 records i want to store this (Excel) data into  5 related tables on Sql Server Database Using .Net.   How to store Excel Sheet data into Sql Server database. Please help me as soon as possible. Thank You Very Much...Have a Good Day... With RegardsSridhar...

Importing data from .xls excel file to a SQL Server 2005 Database
Hi guys. I have a question for you. I have a web application and I need to import some data from an .xls (microsoft excel) file to a sql server 2005 table. So, I been searching for it and I found a couple of articles, but I would like to know How can I do this when the .xls file is in a remote web server??. Another thing is that I don't want to download the .xls file to the local machine where the application is running, I mean, I want to do the importing of the data by completely remote access, I want to access the file and run the process without to make a local cop...

How to import excel sheet into SQL Database in SQL Server from Remote SQL Client
 Hi all, I need to import Excel sheet into SQL Database in SQL Server machine.this excel sheet can be kept in any where.it may client or server machines.but I need to import it using any SQL client(Remote Client in web) machine.I can do it using  SQL Sever Machine.but it doesn't work using SQL Client  machine.I need to do it using  SQL query.it imported using Import/Export wizard in SQL through SQL Client machine.but it can't using SQL quary.Please tel me how I do this.Following are my code.---------------------------------------------------------------------...

How can I get the 'entire' database, structure and data, from SQL server 2008 to SQL server 2005?
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either. The only way I know that works is to script the creation of all tables then export and import. This does work. How can I get the 'entire' database, structure and data, from 2008 to 2005? ThanksSQL newbie. Visual Studio 2008 supports a type of project called a database project. You might try importing the schema of t...

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
  Hi, I'm a Student, and since a few months ago I'm learning JAVA. I'm creating an application to call and compare times. For this I create in Excel a time table which is quite big and it would be a lot of typing work to input one by one the data in each cell in SQL Server, considering that I have to create 8 more tables. I was able to retreive the data from excel usin the JXL API of JAVA but it doesn't give all the funtions to perform math operations as JDBC. That's why I need to move the tables from Excel to SQL. I found this site http://davidhayden.com/blog/dave...

Urgent: i m working for a web page (C#) that use for importing excel sheet data to database tables.....i wanna validation for each column data......
i m working for a web page (C#) that use for importing excel sheet data to database tables.....i wanna validation for each column data...... want suggstion/links/code....plz help me! I didn't understand your question properly but If you want validation on Excel sheet than add text box and write the validation code in the VBA for the click event. Hi,  you haven't specified what kinda database you are using and kinda validations you want to perform, still here are some of the links which do the work for you, check them out those might be helpful. http://www.vbdotnetheav...

Import data from Excel to sql server 2005 not working on server
I am trying to import data from an Excel file into SQL Server 2005 in a webform.  The code below works on my local machine, but on the Production server it throws an unspecified OLEDB error at OleDbConn.Open when attempting to open the connection.  I cannot figure out what is required on the server to make this work, as the error gives no details.  I do not have Excel installed on the webserver and do have it on my local, but don't know if that is the problem, because I get the same error when I change the connectionstring to open a CSV file.   Any help wo...

Web resources about - Import data from excel sheet data to Sql Server 2008 database - asp.net.object-datasource

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Ben Carson calls for database of all foreigners in US
Boston Herald Ben Carson calls for database of all foreigners in US Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

GOP rivals condemn Donald Trump for backing Muslim database
COLUMBIA, S.C. (AP) — Retired neurosurgeon Ben Carson said Saturday that he wants to expand the government's surveillance operations aimed at ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump Wants A Database For All Syrian Refugees Who Enter America
Trump Wants A Database For All Syrian Refugees Who Enter America

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

MapR And Big Data In The World's Largest Biometric Database Project
India?s Aadhaar project is one of the world?s most ambitious big data projects, aiming to collect, store, and utilize biometric information from ...

Trump calls for surveillance of some mosques, attempts to clarify remarks on Syrian database
Trump calls for surveillance of some mosques, attempts to clarify remarks on Syrian database

Presidential Candidates Condemn Donald Trump's 'Database' Comment
... and Democratic presidential candidates have condemned Donald Trump for his recent statements suggesting the United States should start a database ...

Resources last updated: 11/29/2015 9:01:05 AM