Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel to sql

Posted on 2012-09-10
4
Medium Priority
?
409 Views
Last Modified: 2012-09-10
Hi,

Can someone please point me to some well-written asp/vb.net source code showing how to import 2 (of several) fields from an excel spreadsheet into an sql database?

I need the user to browse to and select the file.  I then need to import all of the rows of only two column and place them into a pre-existing sql table.  

I appreciate the help!
0
Comment
Question by:santaspores1
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 1000 total points
ID: 38382898
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 1000 total points
ID: 38383294
this code below will read all the data from spreadsheet into a DataTable. You can modify SELECT statement to read only two columns. Than you can import the dataTable into your database:

Function ReadDataFromExcel(ByVal path As String) As System.Data.DataTable
        Dim ds As New DataSet
        Dim da As OleDbDataAdapter
        Dim conn As OleDbConnection = Nothing
        Dim sheet As String
        Try
            conn = New OleDbConnection( _
                 "provider=Microsoft.ACE.OLEDB.12.0; " & _
                 "data source=" & path & "; " & _
                 "Extended Properties=Excel 12.0;")

            ''get sheet name
            sheet = GetSheetName(path)
              
            da = New OleDbDataAdapter("SELECT * FROM [" & sheet & "]", conn)

            conn.Open()

            da.Fill(ds)
           
        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
        ReadDataFromExcel = ds.Tables(0)
    End Function
    Function GetOldSheetName(ByVal path As String) As String
    
    Function GetSheetName(ByVal path As String) As String
        Dim dt As New DataTable
        Dim conn As OleDbConnection
        Dim sSheetName As String = ""
        Dim strConn As String
        Try
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & path & ";" & _
                "Extended Properties=""Excel 12.0;"""

            conn = New OleDbConnection(strConn)
            conn.Open()

            dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            sSheetName = dt.Rows(dt.Rows.Count - 1).Item("Table_Name")

        Catch ex As Exception
            Console.Write(ex.Message)
        End Try
        GetSheetName = sSheetName
    End Function

Open in new window

0
 
LVL 35

Expert Comment

by:YZlat
ID: 38383296
replace

"SELECT * FROM [" & sheet & "]"

with something like this:

"SELECT ColumnName1, ColumnName2 FROM [" & sheet & "]"
0
 

Author Closing Comment

by:santaspores1
ID: 38384272
Both excellent resources - thanks!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question