paradoxnow
asked on
Using ASPX & VB to import Excel file into a SQL Server table
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.Ole db" %>
<%@ Import Namespace="System.Data.Sql Client" %>
<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.ExecuteNonQu ery
con.close()
End Sub
</script>
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.Ole
<%@ Import Namespace="System.Data.Sql
<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
"'Excel 8.0;Database=d:\book1.xls'
"'SELECT id, name, desc FROM [sheet1$]')"
InsertCommand.CommandText = sql
con.open()
InsertCommand.ExecuteNonQu
con.close()
End Sub
</script>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Got it to work...
here is the code for
__________________________ __________ ________
<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Ole db" %>
<%@ Import Namespace="System.Data.Sql Client" %>
<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.OL EDB.4.0;Da ta 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:\filepat h\upload.x ls", "SELECT red, orange, yellow, greeen FROM [Sheet1$]")
Dim con As New System.Data.SqlClient.SqlC onnection
Dim cmd As New System.Data.SqlClient.SqlC ommand
con.ConnectionString = "Network Library=DBMSSOCN;Data Source=ipaddress,1433;Init ial 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>
here is the code for
__________________________
<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Ole
<%@ Import Namespace="System.Data.Sql
<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.OL
Dim adapter As New OleDbDataAdapter(strQuery,
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:\filepat
Dim con As New System.Data.SqlClient.SqlC
Dim cmd As New System.Data.SqlClient.SqlC
con.ConnectionString = "Network Library=DBMSSOCN;Data Source=ipaddress,1433;Init
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")
cmd.ExecuteNonQuery()
next
End Sub
</script>
<html>
<head>
<title>File Upload</title>
</head>
<body>
Successful File Upload
</body>
</html>
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???
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???
ASKER
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.