Im working on a web application, I want to Know if there is a possibility to insert the data fields information of an Excel file into my SqlServer database after uploading it, using Asp.net(VB.net) ??
Thank's
Add a comment
|
4 Answers
Try this code in VB.NET:
Protected Sub btnExport(ByVal sender As Object, ByVal e As System.EventArgs)
Dim TheFile As FileInfo = New FileInfo(MapPath(".") & "\" & "filename.xls")
' Connection String to Excel Workbook 2010 (xlsx)
' Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~\directory\filename.xlsx") + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
' Connection String to Excel Workbook 2003 (xls)
Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~\directory\filename.xls") + ";Extended Properties=""Excel 8.0;HDR=YES;"""
' Create Connection to Excel Workbook
Using connection As New OleDbConnection(excelConnectionString)
Dim command As New OleDbCommand("Select * FROM [sheetname$] ", connection)
connection.Open()
' Create DbDataReader to Data Worksheet
Using dr As DbDataReader = command.ExecuteReader()
' SQL Server Connection String
Const sqlConnectionString As String = "Data Source=server; Initial Catalog=database; Persist Security Info=True;User ID=userid;Password=password"
' Bulk Copy to SQL Server
Using bulkCopy As New SqlBulkCopy(sqlConnectionString)
bulkCopy.DestinationTableName = "SqlServerTableName"
bulkCopy.WriteToServer(dr)
End Using
End Using
End Using
End Sub
Additional tip: try to set IIS to run 32-bit applications.
Comments
Yes it is possible. Here is the bird's view of possible basic steps:
- After uploading - save Excel file under temporary unique name
- Open OLEDB connection to the uploaded file
- Read it (either via OleDbDataReader - if it's a huge file, so you don't have to load it into memory at once, or into DataTable if it's small)
- Run your queries, Stored procedures to update SqlServer DB with acquired data
- Delete uploaded file
1 Comment
Comfortably Numb
It's a pretty common code, you can find many examples online. For example Google "Path.GetRandomFileName" or "VB.NET read Excel file OLEDB" or "DataTable TVP"
Try this
public void export(string excelfilepath)
{
string ssqltable = "tdatamigrationtable";
string myexceldataquery = "select student,rollno,course from [sheet1$]";
try
{
//create our connection strings
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath +
";extended properties=" + "\"excel 8.0;hdr=yes;\"";
string ssqlconnectionstring = "server=mydatabaseservername;user
id=dbuserid;password=dbuserpassword;database=databasename;connection reset=false";
//series of commands to bulk copy data from the excel file into our sql table
oledbconnection oledbconn = new oledbconnection(sexcelconnectionstring);
oledbcommand oledbcmd = new oledbcommand(myexceldataquery, oledbconn);
oledbconn.open();
oledbdatareader dr = oledbcmd.executereader();
sqlbulkcopy bulkcopy = new sqlbulkcopy(ssqlconnectionstring);
bulkcopy.destinationtablename = ssqltable;
while (dr.read())
{
bulkcopy.writetoserver(dr);
}
oledbconn.close();
}
catch (exception ex)
{
//handle exception
}
}
Comments
Imports System.Data.Common
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim fname As String
Using ofd As New OpenFileDialog
If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
fname = ofd.FileName
End If
End Using
Dim olecon As String = "Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=" & fname & ";Extended Properties=""Excel 12.0;IMEX=1;HDR=YES;"""
Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(olecon)
dBaseConnection.Open()
SSQL = "select [LOT],[IMAGE],[STYLENO],[VENDOR] from [Sheet1$]"
Dim cmd As New OleDbCommand(SSQL, dBaseConnection)
Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds)
Using dr As DbDataReader = cmd.ExecuteReader
If SHCONNECTION.State = ConnectionState.Closed Then
Call SHconn(MCONNECTIONSTRING)
End If
Using bulkCopy As New SqlBulkCopy(MCONNECTIONSTRING)
bulkCopy.DestinationTableName = "DBimage"
bulkCopy.WriteToServer(ds)
End Using
End Using
End Sub
End Class
