Below is the code to read from an excel file to a dataset. After retrieving into the datset you can read from it and insert into database :
Dim connectionString As String
connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=<<Path>>;Extended Properties=Excel 12.0;"
Dim objConn As New OleDbConnection(connectionString)
objConn.Open()
Dim strConString As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As New OleDbCommand(strConString, objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter()
' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ExcelData")
' Clean up objects.
objConn.Close()
You need to upload your excel file on the server, so that It will be convinant for you to read that file contents. pls follow following steps.
speps 1.
'Beginnig of Steps 1**********************************************************************************************************************
'create a Class file with nama ExcelFile.vb
Imports System.Data.OleDb
Imports System.IO
Imports System.Data
Imports Microsoft.VisualBasic
Public Class ExcelFile
'''''''''''''''''''''''''''''''''''''''''''''''''
' LOCAL VARIABLES
'''''''''''''''''''''''''''''''''''''''''''''''''
#Region " local vars "
Private _ConnectionString As String = Nothing
Private _ErrorMessage As String = Nothing
Private _ExcelWorkSheets As WorkSheets = Nothing
Private _FileName As String = Nothing
Private _FileType As String = Nothing
Private _Path As String = Nothing
#End Region
'''''''''''''''''''''''''''''''''''''''''''''''''
' PROPERTIES
'''''''''''''''''''''''''''''''''''''''''''''''''
#Region " Properties "
Private ReadOnly Property ConnectionString() As String
Get
_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & FileName & ";" & _
"Extended Properties=Excel 8.0;"
Return _ConnectionString
End Get
End Property
Public Property ErrorMessage() As String
Get
Return _ErrorMessage
End Get
Set(ByVal value As String)
_ErrorMessage = value
End Set
End Property
Public Property ExcelWorkSheets() As WorkSheets
Get
Return _ExcelWorkSheets
End Get
Set(ByVal value As WorkSheets)
value = _ExcelWorkSheets
End Set
End Property
Public Property FileName() As String
Get
Return _FileName
End Get
Set(ByVal value As String)
_FileName = value
End Set
End Property
Public ReadOnly Property FileType() As String
Get
'sets the file type based on the file name
'If _FileType Is Nothing Or _FileType <> "" Then
GetFileType()
'End If
Return _FileType
End Get
End Property
Public Property Path() As String
Get
Return _Path
End Get
Set(ByVal value As String)
_Path = value
End Set
End Property
#End Region
'''''''''''''''''''''''''''''''''''''''''''''''''
' CONSTRUCTORS
'''''''''''''''''''''''''''''''''''''''''''''''''
#Region " Constructors "
''' <summary>Creates the object baised on the file you already uploaded. Populates the WorkSheet Property with worksheet names</summary>
''' <param name="path">The Pshyical path that could / should be baised on the web config Server.MapPath("excelreading")</param>
''' <param name="fileName">this is the name that you saved the file as not necassarly the original file name</param>
''' <remarks>Contains error checking incase the wrong file type is uploaded</remarks>
Public Sub New(ByVal path As String, ByVal fileName As String)
_Path = path
_FileName = fileName
'Check the file type before you get really excited.
If UCase(FileType) <> "XLS" Then
ErrorMessage = "Wrong File Type"
Else
LoadWorkSheetNames()
End If
End Sub
#End Region
'''''''''''''''''''''''''''''''''''''''''''''''''
' METHODS
'''''''''''''''''''''''''''''''''''''''''''''''''
#Region " methods "
''' <summary>Populates the WorkSheet property with a collection of worksheet names</summary>
''' <remarks>more field could be added but are not really relative. The time stamp is accessable
''' but it is the time stamp of when the file was coppied</remarks>
Private Sub LoadWorkSheetNames()
'counter for loop
Dim i As Int16 = Nothing
Dim dt As DataTable = Nothing
Try
Dim objConn As New OleDbConnection(ConnectionString)
objConn.Open()
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
'always close Jerky
objConn.Close()
Dim tmpExcelWorkSheets As New WorkSheets
'if there are rows show them
If dt.Rows.Count > 0 Then
'loop through all the transactions and add aall the items
For i = 0 To dt.Rows.Count - 1
tmpExcelWorkSheets.add(New WorkSheet(dt.Rows(i)("TABLE_NAME")))
Next
_ExcelWorkSheets = tmpExcelWorkSheets
End If
Catch ex As Exception
ErrorMessage = ex.Message
End Try
End Sub
#End Region
'''''''''''''''''''''''''''''''''''''''''''''''''
' FUNCTIONS
'''''''''''''''''''''''''''''''''''''''''''''''''
#Region " functions "
''' <summary>Gets data from select workbook</summary>
''' <param name="workSheetName">Should ab a name from the excellWorkSheets property</param>
''' <returns>dataset</returns>
''' <remarks>The worksheet name does not require a $ appended to the name</remarks>
Public Function GetWorkSheetByName(ByVal workSheetName As String) As DataSet
Dim ds As New DataSet
Try
Dim myData As New OleDbDataAdapter("SELECT * FROM [" & workSheetName & "]", ConnectionString)
myData.TableMappings.Add("Table", workSheetName)
myData.Fill(ds)
Catch ex As Exception
_ErrorMessage = ex.Message
End Try
Return ds
End Function
''' <summary>Strips the file type out of the file name property</summary>
''' <returns>file type as string</returns>
Public Function GetFileType() As String
_FileType = Right(FileName, Len(FileName) - InStr(FileName, "."))
Return _FileType
End Function
#End Region
End Class
'''''''''''''''''''''''''''''''''''''''''''''''''
' WORKSHEET CLASS
'''''''''''''''''''''''''''''''''''''''''''''''''
''' <summary>Simple class to hold info about the worksheets in the workbook</summary>
Public Class WorkSheet
Private _TableName As String = Nothing
''' <summary>"TABLE_NAME" property loaded from OleDbSchemaGuid.Tables method</summary>
Public Property TableName() As String
Get
Return _TableName
End Get
Set(ByVal value As String)
_TableName = value
End Set
End Property
''' <summary>Constructor used to add new worksheets to a collection</summary>
''' <param name="tableName">worksheet name includes the appended $</param>
Public Sub New(ByVal tableName As String)
_TableName = tableName
End Sub
End Class
'''''''''''''''''''''''''''''''''''''''''''''''''
' WORKSHEETS COLLECTION
'''''''''''''''''''''''''''''''''''''''''''''''''
''' <summary>Simple Collection to store all the worksheets in the work book</summary>
Public Class WorkSheets
Inherits CollectionBase
''' <summary>method allows you to add items to the collection</summary>
''' <param name="WorkSheet"></param>
''' <remarks>Man I am good lookin</remarks>
Public Sub add(ByVal workSheet As WorkSheet)
List.Add(workSheet)
End Sub
''' <summary>Allows you to return Items from the collection by index</summary>
''' <param name="index">Collection is zero based expects a int as an index in the array</param>
Default Public Overridable ReadOnly Property Item(ByVal index As Integer) As WorkSheet
Get
Return CType(Me.List(index), WorkSheet)
End Get
End Property
End Class
'End of Steps 1**********************************************************************************************************************
Steps 2
'Beginnig of Steps 2**********************************************************************************************************************
'Call following method On button event
Sub ImportDate()
Dim savePath As String = Server.MapPath("../Attachments/")
Dim postedAttachment
Dim filename As String = System.IO.Path.GetFileName(UploadFile.PostedFile.FileName)
Dim strFile As String
strFile = savePath & filename
postedAttachment = UploadFile.PostedFile
If filename.Trim.Length <> 0 Then
postedAttachment.SaveAs(strFile)
isFileUploaded = True
End If
If isFileUploaded = True Then'file uploaded successfully read data from the same file
Dim Excel As New ExcelFile(savePath, filename)
'Try
' If Excel.ExcelWorkSheets.Item(0).TableName.Trim <> "AppraisalStatus$" Then
' Msg = ""
' Msg = "Please upload the standard formatted excel file."
' showmessage(Msg)
' Exit Sub
' End If
'Catch ex As Exception
' showmessage(ex.Message)
'End Try
Try
Dim ds1 As DataSet
Dim dtable As New DataTable
ds1 = Excel.GetWorkSheetByName(Excel.ExcelWorkSheets.Item(0).TableName)
Dim rcount, retval, CycleID, EmpCode, Sheet_ID As Integer
Dim CycleID_, EmpCode_, NormalizedRating_, SqlStr As String
Dim NormalizedRating As Decimal
BusinessLayer.ExecuteSQL("delete from z_EntityMast_Temp_Appraisal_Status") ' delete data that is temporarily saved
For rcount = 0 To ds1.Tables(0).Rows.Count - 1
Try'store data that has to be show in case of error occur
CycleID_ = IIf(IsDBNull(ds1.Tables(0).Rows(rcount)(0)), "", ds1.Tables(0).Rows(rcount)(0))
EmpCode_ = IIf(IsDBNull(ds1.Tables(0).Rows(rcount)(5)), "", ds1.Tables(0).Rows(rcount)(5))
NormalizedRating_ = IIf(IsDBNull(ds1.Tables(0).Rows(rcount)(8)), "", ds1.Tables(0).Rows(rcount)(8))
'store data that has to be shaved
CycleID = IIf(IsDBNull(ds1.Tables(0).Rows(rcount)(0)), Nothing, ds1.Tables(0).Rows(rcount)(0))
EmpCode = IIf(IsDBNull(ds1.Tables(0).Rows(rcount)(5)), Nothing, ds1.Tables(0).Rows(rcount)(5))
NormalizedRating = IIf(IsDBNull(ds1.Tables(0).Rows(rcount)(8)), Nothing, ds1.Tables(0).Rows(rcount)(8))
Sheet_ID = BusinessLayer.getFieldValue("select Appraisal_Sheets_ID from z_EntityDtl_Appraisal_Sheets_002002 where Appraisal_Cycle_ID=" & CycleID & " and Employee_Name=(select Employee_ID from z_Actor_Employee_001023 where Employee_Code=" & EmpCode & ")")
SqlStr = "insert into z_EntityMast_Temp_Appraisal_Status values(" & Sheet_ID & "," & CycleID & "," & EmpCode & "," & checkNumericNullValues(NormalizedRating_) & ")"
BusinessLayer.ExecuteSQL(SqlStr)
Catch ex As Exception
Msg = "Error at position of EmpCode: " & EmpCode_
showmessage(Msg)
Exit Sub
End Try
Next
isSccess = True
If isSccess = True Then
BusinessLayer.ExecuteSQL("update z_EntityDtl_Appraisal_Sheets_002002 set Normalized_Rating=(select Normalized_Rating from z_EntityMast_Temp_Appraisal_Status where Sheet_ID=z_EntityDtl_Appraisal_Sheets_002002.Appraisal_Sheets_ID)") ' update data after confirming that data is consistant
End If
Catch ex As Exception
'lblMessage1.Text = "The data import utility faced some error. Please contact site administrator or check the format of excel sheet .It should be in a proper format."
End Try
End If
End Sub
Function checkNumericNullValues(ByVal Val As String) As String
Dim rVal As String
If Val <> ""Then
rVal = Val
Else
rVal = "null"End If
Return rVal
End Function
Private Sub showmessage(ByVal msg As String)
Try
Dim strScript As String = "<script language=JavaScript>"
strScript += "alert(""" & msg & """);"
strScript += "</script>"If (Not Page.IsStartupScriptRegistered("clientScript")) Then
Page.RegisterStartupScript("clientScript", strScript)
End If
Catch ex As Exception
showmessage(ex.Message)
End Try
End Sub'End of Steps 2**********************************************************************************************************************
if you want to check uploded file type at client side also then use following code in .aspx page
function checkFileType()
{
var canname=document.getElementById("UploadFile").value;
if (canname.length == 0)
{
alert('Please Browse file to upload...');
document.getElementById("UploadFile").focus();
return false;
}
var path = document.getElementById('UploadFile').value;
var Index = path.lastIndexOf(".");
var length = path.length;
var filetype = path.substring(Index,length)
if (filetype == ".xls")
{
}
else
{
alert('[Only .xls] file is allowed');
ClearBrowseContent('UploadFile');
return false;
}
}
function ClearBrowseContent(control)
{
var browse=document.getElementById(control);
var newbrowse= browse.cloneNode(false);
browse.parentNode.replaceChild(newbrowse ,browse);
}
ASP.NET
Please remember to click “Mark as Answer” on the post that helps you
Jasim Akhtar
New Delhi ( INDIA )
Marked as answer by fahim02 on Apr 20, 2009 05:02 AM
fahim02
Member
53 Points
8 Posts
How to read Excel file data and save into database?
Apr 02, 2009 10:20 AM|LINK
How to read Excel file data and save into database?
asp .net
mcr_subbu
Contributor
2316 Points
517 Posts
Re: How to read Excel file data and save into database?
Apr 02, 2009 01:02 PM|LINK
Below is the code to read from an excel file to a dataset. After retrieving into the datset you can read from it and insert into database :
Dim connectionString As String
connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=<<Path>>;Extended Properties=Excel 12.0;"
Dim objConn As New OleDbConnection(connectionString)
objConn.Open()
Dim strConString As String = "SELECT * FROM [Sheet1$]"
Dim objCmdSelect As New OleDbCommand(strConString, objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter()
' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "ExcelData")
' Clean up objects.
objConn.Close()
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: How to read Excel file data and save into database?
Apr 02, 2009 01:09 PM|LINK
Either read directly using stored proc
http://support.microsoft.com/kb/321686
Or through ado.net for that refer my article
http://www.aspsnippets.com/post/2009/02/04/Read-Excel-using-ADONet.aspx
Contact me
miazaidi
Member
305 Points
182 Posts
Re: How to read Excel file data and save into database?
Apr 06, 2009 05:23 AM|LINK
Hi,
You need to upload your excel file on the server, so that It will be convinant for you to read that file contents. pls follow following steps.
speps 1.
'Beginnig of Steps 1********************************************************************************************************************** 'create a Class file with nama ExcelFile.vb Imports System.Data.OleDb Imports System.IO Imports System.Data Imports Microsoft.VisualBasic Public Class ExcelFile ''''''''''''''''''''''''''''''''''''''''''''''''' ' LOCAL VARIABLES ''''''''''''''''''''''''''''''''''''''''''''''''' #Region " local vars " Private _ConnectionString As String = Nothing Private _ErrorMessage As String = Nothing Private _ExcelWorkSheets As WorkSheets = Nothing Private _FileName As String = Nothing Private _FileType As String = Nothing Private _Path As String = Nothing #End Region ''''''''''''''''''''''''''''''''''''''''''''''''' ' PROPERTIES ''''''''''''''''''''''''''''''''''''''''''''''''' #Region " Properties " Private ReadOnly Property ConnectionString() As String Get _ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Path & FileName & ";" & _ "Extended Properties=Excel 8.0;" Return _ConnectionString End Get End Property Public Property ErrorMessage() As String Get Return _ErrorMessage End Get Set(ByVal value As String) _ErrorMessage = value End Set End Property Public Property ExcelWorkSheets() As WorkSheets Get Return _ExcelWorkSheets End Get Set(ByVal value As WorkSheets) value = _ExcelWorkSheets End Set End Property Public Property FileName() As String Get Return _FileName End Get Set(ByVal value As String) _FileName = value End Set End Property Public ReadOnly Property FileType() As String Get 'sets the file type based on the file name 'If _FileType Is Nothing Or _FileType <> "" Then GetFileType() 'End If Return _FileType End Get End Property Public Property Path() As String Get Return _Path End Get Set(ByVal value As String) _Path = value End Set End Property #End Region ''''''''''''''''''''''''''''''''''''''''''''''''' ' CONSTRUCTORS ''''''''''''''''''''''''''''''''''''''''''''''''' #Region " Constructors " ''' <summary>Creates the object baised on the file you already uploaded. Populates the WorkSheet Property with worksheet names</summary> ''' <param name="path">The Pshyical path that could / should be baised on the web config Server.MapPath("excelreading")</param> ''' <param name="fileName">this is the name that you saved the file as not necassarly the original file name</param> ''' <remarks>Contains error checking incase the wrong file type is uploaded</remarks> Public Sub New(ByVal path As String, ByVal fileName As String) _Path = path _FileName = fileName 'Check the file type before you get really excited. If UCase(FileType) <> "XLS" Then ErrorMessage = "Wrong File Type" Else LoadWorkSheetNames() End If End Sub #End Region ''''''''''''''''''''''''''''''''''''''''''''''''' ' METHODS ''''''''''''''''''''''''''''''''''''''''''''''''' #Region " methods " ''' <summary>Populates the WorkSheet property with a collection of worksheet names</summary> ''' <remarks>more field could be added but are not really relative. The time stamp is accessable ''' but it is the time stamp of when the file was coppied</remarks> Private Sub LoadWorkSheetNames() 'counter for loop Dim i As Int16 = Nothing Dim dt As DataTable = Nothing Try Dim objConn As New OleDbConnection(ConnectionString) objConn.Open() dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) 'always close Jerky objConn.Close() Dim tmpExcelWorkSheets As New WorkSheets 'if there are rows show them If dt.Rows.Count > 0 Then 'loop through all the transactions and add aall the items For i = 0 To dt.Rows.Count - 1 tmpExcelWorkSheets.add(New WorkSheet(dt.Rows(i)("TABLE_NAME"))) Next _ExcelWorkSheets = tmpExcelWorkSheets End If Catch ex As Exception ErrorMessage = ex.Message End Try End Sub #End Region ''''''''''''''''''''''''''''''''''''''''''''''''' ' FUNCTIONS ''''''''''''''''''''''''''''''''''''''''''''''''' #Region " functions " ''' <summary>Gets data from select workbook</summary> ''' <param name="workSheetName">Should ab a name from the excellWorkSheets property</param> ''' <returns>dataset</returns> ''' <remarks>The worksheet name does not require a $ appended to the name</remarks> Public Function GetWorkSheetByName(ByVal workSheetName As String) As DataSet Dim ds As New DataSet Try Dim myData As New OleDbDataAdapter("SELECT * FROM [" & workSheetName & "]", ConnectionString) myData.TableMappings.Add("Table", workSheetName) myData.Fill(ds) Catch ex As Exception _ErrorMessage = ex.Message End Try Return ds End Function ''' <summary>Strips the file type out of the file name property</summary> ''' <returns>file type as string</returns> Public Function GetFileType() As String _FileType = Right(FileName, Len(FileName) - InStr(FileName, ".")) Return _FileType End Function #End Region End Class ''''''''''''''''''''''''''''''''''''''''''''''''' ' WORKSHEET CLASS ''''''''''''''''''''''''''''''''''''''''''''''''' ''' <summary>Simple class to hold info about the worksheets in the workbook</summary> Public Class WorkSheet Private _TableName As String = Nothing ''' <summary>"TABLE_NAME" property loaded from OleDbSchemaGuid.Tables method</summary> Public Property TableName() As String Get Return _TableName End Get Set(ByVal value As String) _TableName = value End Set End Property ''' <summary>Constructor used to add new worksheets to a collection</summary> ''' <param name="tableName">worksheet name includes the appended $</param> Public Sub New(ByVal tableName As String) _TableName = tableName End Sub End Class ''''''''''''''''''''''''''''''''''''''''''''''''' ' WORKSHEETS COLLECTION ''''''''''''''''''''''''''''''''''''''''''''''''' ''' <summary>Simple Collection to store all the worksheets in the work book</summary> Public Class WorkSheets Inherits CollectionBase ''' <summary>method allows you to add items to the collection</summary> ''' <param name="WorkSheet"></param> ''' <remarks>Man I am good lookin</remarks> Public Sub add(ByVal workSheet As WorkSheet) List.Add(workSheet) End Sub ''' <summary>Allows you to return Items from the collection by index</summary> ''' <param name="index">Collection is zero based expects a int as an index in the array</param> Default Public Overridable ReadOnly Property Item(ByVal index As Integer) As WorkSheet Get Return CType(Me.List(index), WorkSheet) End Get End Property End Class 'End of Steps 1**********************************************************************************************************************Steps 2
if you want to check uploded file type at client side also then use following code in .aspx page
function checkFileType() { var canname=document.getElementById("UploadFile").value; if (canname.length == 0) { alert('Please Browse file to upload...'); document.getElementById("UploadFile").focus(); return false; } var path = document.getElementById('UploadFile').value; var Index = path.lastIndexOf("."); var length = path.length; var filetype = path.substring(Index,length) if (filetype == ".xls") { } else { alert('[Only .xls] file is allowed'); ClearBrowseContent('UploadFile'); return false; } } function ClearBrowseContent(control) { var browse=document.getElementById(control); var newbrowse= browse.cloneNode(false); browse.parentNode.replaceChild(newbrowse ,browse); }ASP.NET
Jasim Akhtar
New Delhi ( INDIA )