Import excel file to sql using bulkcopy
I have been able to import an excel file sql bulkcopy locally. But when I publish the code to the server I get the following error messages:
Exception Message: 'C:\MyTest.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Exception Source: Microsoft JET Database Engine
Here is code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Test.aspx.vb" Inherits="Test" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
&开发者_开发技巧lt;/head>
<body>
<form id="form1" runat="server">
<asp:FileUpload ID="txtFile" runat="server" />
<br /><br />
<asp:Button ID="Button1" runat="server" Text="Button" />
</form>
</body>
</html>
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtFile.PostedFile.FileName & ";Extended Properties=""Excel 8.0;HDR=YES;"""
Using connection As New System.Data.OleDb.OleDbConnection(excelConnectionString)
'List columns you need from the Excel file
Dim command As New System.Data.OleDb.OleDbCommand("Select [Name],[Location] FROM [Sheet1$]", connection)
connection.Open()
' Create DbDataReader to Data Worksheet
Using dr As System.Data.OleDb.OleDbDataReader = command.ExecuteReader()
' SQL Server Connection String
Dim con As SqlConnection = GetConnection()
Using bulkCopy As New System.Data.SqlClient.SqlBulkCopy(con)
bulkCopy.DestinationTableName = "tblExcel"
'Define ColumnMappings: source(Excel) --destination(DB Table column)
bulkCopy.ColumnMappings.Add("Name", "Name")
bulkCopy.ColumnMappings.Add("Location", "Location")
bulkCopy.WriteToServer(dr)
End Using
CloseConnection(con)
End Using
End Using
End Sub
You should first save the file (SaveAs(path), Use server.MapPath to map a relative dir) and then give the full path to the connection string.
You may have to install the Microsoft Office Access database engine on the server.
加载中,请稍侯......
精彩评论