Solved

Using ASPX & VB to import Excel file into a SQL Server table

Posted on 2004-04-05
5
1,776 Views
Last Modified: 2008-01-09
I have an Excel file with a worksheet called sheet1$ that looks like:

      A      B         C
==============
1 |   ID     Name   Desc
2 |   1      Red      Hot
3 |   2      Blue     Cool
4 |   3      Black    Dark

Row 1 is the header row

I have a SQL Server table called colors with the following fields

ID     Name   Desc

I want to import  the excel data into the SQL Server tables.  I have seen many examples on this site but I cannot get any to work.  Please provide detailed code... A full aspx page using VB would be ideal.

Below is an example of one of the methods I have trying...  I am not married to this example... I am just looking for something that works.

Alternatively, I have been able to import the Excel data into a dataset-datatable...  but I do not know how to insert this datatable into a SQL Server table.

500 points to the best and most detailed answer!
-------------------------------------------------------
<%@ Page Language="VB" Debug="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)

Dim con As New SQLConnection("Network Library=DBMSSOCN;Data Source=ip address,1433;Initial Catalog=db;User ID=;Password=")
Dim InsertCommand As SqlCommand = New SqlCommand()
InsertCommand.Connection = con
Dim sql As String
  sql = "SELECT id, name, desc into colors from " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=d:\book1.xls', " & _
        "'SELECT id, name, desc FROM [sheet1$]')"

InsertCommand.CommandText = sql
con.open()
InsertCommand.ExecuteNonQuery
con.close()
End Sub
</script>
0
Comment
Question by:paradoxnow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 5

Accepted Solution

by:
fahimnxb earned 500 total points
ID: 10763353
Dear ParaDoxNow!

Following is some sample code add function to you .vb class or make another class for it.

'THIS FUNCTION READS EXCEL SHEET AND FILLS A DATA TABLE WITH RECORDS
Public Function readExcelSheet(ByVal strExcelFileName As String, ByVal strQuery As String) As DataTable
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strExcelFileName & ";Extended Properties=""Excel 8.0;"""
        Dim da As New OleDbDataAdapter(strQuery, strConn)
        Dim tbl As DataTable = New DataTable
        Try
            da.Fill(tbl)
            da.Dispose()
        Catch Xcp As Exception
            MsgBox(Xcp.Message.ToString)
        End Try

        Return tbl

    End Function

'FUNCTION CALL
tbl = dbObj.readExcelSheet(txtSubTemplate.Text, "SELECT * FROM $Sheet1")
'LOOPING THROUGH COLS
For Each col In tbl.Columns
   'col.ColumnName
   'col.DataType
Next

'LOOPING THROUGH ROWS
For i = 0 To tbl.Rows.Count() - 1
               For j = 0 To tbl.Columns.Count - 1
                    'tbl.Rows(i).Item(j)
                Next j
NEXt
Next

Reuse this code as needed

Ask if there is any confusion.
FaHiM
0
 

Author Comment

by:paradoxnow
ID: 10764264
fahimnxb

I've actually already been able to read an excel file into a datatable ( I indicated so somewhere in my original text) ...
But I have not been able to figure out how to insert into a SQL Server table.  I am new to programming VB.net and I usually learn from example code.  

Maybe my question should have been .... Once I create a datatable from an OLE connection to Excel... How do I create a SQL Server connection to a table and easily insert the records from the "excel" datatable?

I appreciate your help and any additional assistance in providing additional detail code would be wonderful.


0
 
LVL 7

Expert Comment

by:ScrptMasta
ID: 10766213
I would suggest that you save your Excel spreadsheets into XML format first and then go from the XML directly into SQL.

This article explains how to put Excel into XML format.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffpro01/html/XMLOfficeXPPartI.asp

Once you have the data into XML format you simply use SQLXML to send it to a database.

Here is a good site to introduce you to SQLXML if you are not familiar with it. I would highly recommend using XML for parsing data to and from SQL especially if you are going to render any of that data back to HTML in order for display on any ASP.NET forms. It looks intimidating at first however once you get in there and play a little bit you'll see it’s not that bad.

http://www.vbxml.com/sql/default.asp
0
 

Author Comment

by:paradoxnow
ID: 10776899
Got it to work...

here is the code for

____________________________________________

<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Public Function readExcelSheet(ByVal strExcelFileName As String, ByVal strQuery As String) As DataTable
      Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""" & strExcelFileName & """;Extended Properties=""Excel 8.0;HDR=YES;"""
      Dim adapter As New OleDbDataAdapter(strQuery, strCon)
      Dim tbl As DataTable = New DataTable
      adapter.Fill(tbl)
      adapter.Dispose()
      Return tbl
End Function
Sub Page_Load(sender As Object, e As EventArgs)
      Dim tbl As New DataTable()
      tbl = readExcelSheet("c:\filepath\upload.xls", "SELECT red, orange, yellow, greeen FROM [Sheet1$]")
      Dim con As New System.Data.SqlClient.SqlConnection
      Dim cmd As New System.Data.SqlClient.SqlCommand
      con.ConnectionString = "Network Library=DBMSSOCN;Data Source=ipaddress,1433;Initial Catalog=db;User ID=;Password="
      con.Open()
      cmd = con.CreateCommand
      cmd.CommandType = CommandType.Text
      dim i
      for i = 0 To tbl.Rows.Count() - 1
            cmd.CommandText = "insert db..table (red, orange, yellow, greeen ) values (" & tbl.Rows(i).Item("red") & "," & tbl.Rows(i).Item("orange") & "," & tbl.Rows(i).Item("yellow") & "," & tbl.Rows(i).Item("green") & ")"
            cmd.ExecuteNonQuery()
      next
End Sub
</script>
<html>
<head>
      <title>File Upload</title>
</head>
<body>
Successful File Upload
</body>
</html>
0
 

Expert Comment

by:michael090
ID: 12187865
hi guys,

 I've an error with the code when the cell in the excel file is blank... Can you tell me how I could prevent that kind of scenario???
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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