Answered by:
How to verify data while reading from excel sheet and then store in database

Question
-
User945338570 posted
Hi,
I am creating a web application that takes an excel sheet containing following fields:
Location ID, Location Name, Address, City, State, Is Master Location, Is Active
Now I want to store these records in SQL database. So I created a table named tbl_location
CREATE TABLE [dbo].[tbl_location]( [location_id] [int] NOT NULL, [location_name] [varchar](50) NULL, [address] [varchar](500) NULL, [city] [varchar](100) NULL, [state] [varchar](100) NULL, [is_master_location] [bit] NULL, [is_active] [bit] NULL, [phone] [int] NULL, )
But before storing that data in SQL, I want to check the following fields in Excel sheet
Location Name - Should not contain any integer
Is Active - Should contain only 0 or 1
Phone - Should contain only integer
Now the rows which do not meet the criteria will be skipped and rest of them will be stored in table.
I tried coding it but only able to upload excel on server but don't know how to read, validate and then store it.
My Code:
Protected Sub btnUpload_Click(sender As Object, e As System.EventArgs) Handles btnUpload.Click 'Code to upload excel file on server If FileUploadToServer.HasFile Then Try Dim FileName As String = FileUploadToServer.PostedFile.FileName Dim Extension As String = Path.GetExtension(FileUploadToServer.PostedFile.FileName) Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath") Dim FilePath As String = Server.MapPath("~/UploadNow") & "/" & FileName FileUploadToServer.PostedFile.SaveAs(FilePath) 'Response.Write(FilePath) 'Exit Sub 'GetExcelSheets(FilePath, Extension, "Yes") 'Code to read Excel file and store it in database ' Connection String to Excel Workbook 2010 (xlsx) Dim excelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~\UploadNow\location.xlsx") + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;""" ' Create Connection to Excel Workbook Using connection As New OleDbConnection(excelConnectionString) Dim command As New OleDbCommand("Select * FROM [Sheet1$] ", connection) connection.Open() ' Create DbDataReader to Data Worksheet Using dr As DbDataReader = command.ExecuteReader() ' SQL Server Connection String Const sqlConnectionString As String = "Data Source=MYPC\SQLEXPRESS; Initial Catalog=billing; Persist Security Info=True;User ID=username;Password=password" ' Bulk Copy to SQL Server Using bulkCopy As New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName = "tbl_location" bulkCopy.WriteToServer(dr) End Using End Using connection.Close() End Using lblMsg.Text = "Upload Successful!" Catch ex As Exception lblMsg.Text = "Error saving file" + FileUploadToServer.PostedFile.FileName.ToString() End Try End If End Sub
Tuesday, November 12, 2013 7:59 AM
Answers
-
User1508394307 posted
Currently you send datareader directly to the database. In order to validate data, you can enumerate every row and if copy valid rows to a new datatable. Then send that datatable to the database.
Example, instead of this
Using dr As DbDataReader = command.ExecuteReader() ' SQL Server Connection String Const sqlConnectionString As String = "Data Source=MYPC\SQLEXPRESS; Initial Catalog=billing; Persist Security Info=True;User ID=username;Password=password" ' Bulk Copy to SQL Server Using bulkCopy As New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName = "tbl_location" bulkCopy.WriteToServer(dr) End Using End Using
try
Dim table As New DataTable table.Columns.Add("location_id", GetType(Integer)) table.Columns.Add("location_name", GetType(String)) ... Using dr As DbDataReader = command.ExecuteReader() While dr.Read() If Not IsNumeric(dr("location_name")) _ And (CStr(dr("location_name")) = "0" Or CStr(dr("location_name")) = "1") _ And IsNumeric(dr("phone")) Then table.Rows.Add(dr("location_id"), dr("location_name"), ...) End If End While End Using ' SQL Server Connection String Const sqlConnectionString As String = "Data Source=MYPC\SQLEXPRESS; Initial Catalog=billing; Persist Security Info=True;User ID=username;Password=password" ' Bulk Copy to SQL Server Using bulkCopy As New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName = "tbl_location" bulkCopy.WriteToServer(table) End Using
Hope this helps.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, November 12, 2013 8:21 AM
All replies
-
User1508394307 posted
Currently you send datareader directly to the database. In order to validate data, you can enumerate every row and if copy valid rows to a new datatable. Then send that datatable to the database.
Example, instead of this
Using dr As DbDataReader = command.ExecuteReader() ' SQL Server Connection String Const sqlConnectionString As String = "Data Source=MYPC\SQLEXPRESS; Initial Catalog=billing; Persist Security Info=True;User ID=username;Password=password" ' Bulk Copy to SQL Server Using bulkCopy As New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName = "tbl_location" bulkCopy.WriteToServer(dr) End Using End Using
try
Dim table As New DataTable table.Columns.Add("location_id", GetType(Integer)) table.Columns.Add("location_name", GetType(String)) ... Using dr As DbDataReader = command.ExecuteReader() While dr.Read() If Not IsNumeric(dr("location_name")) _ And (CStr(dr("location_name")) = "0" Or CStr(dr("location_name")) = "1") _ And IsNumeric(dr("phone")) Then table.Rows.Add(dr("location_id"), dr("location_name"), ...) End If End While End Using ' SQL Server Connection String Const sqlConnectionString As String = "Data Source=MYPC\SQLEXPRESS; Initial Catalog=billing; Persist Security Info=True;User ID=username;Password=password" ' Bulk Copy to SQL Server Using bulkCopy As New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName = "tbl_location" bulkCopy.WriteToServer(table) End Using
Hope this helps.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, November 12, 2013 8:21 AM -
User555306248 posted
http://forums.asp.net/t/1854909.aspx
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
Tuesday, November 12, 2013 10:58 PM -
User945338570 posted
smirnov
Currently you send datareader directly to the database. In order to validate data, you can enumerate every row and if copy valid rows to a new datatable. Then send that datatable to the database. Example, instead of this Using dr As DbDataReader = command.ExecuteReader() ' SQL Server Connection String Const sqlConnectionString As String = "Data Source=MYPC\SQLEXPRESS; Initial Catalog=billing; Persist Security Info=True;User ID=username;Password=password" ' Bulk Copy to SQL Server Using bulkCopy As New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName = "tbl_location" bulkCopy.WriteToServer(dr) End Using End Using
try
Dim table As New DataTable table.Columns.Add("location_id", GetType(Integer)) table.Columns.Add("location_name", GetType(String)) ... Using dr As DbDataReader = command.ExecuteReader() While dr.Read() If Not IsNumeric(dr("location_name")) _ And (CStr(dr("location_name")) = "0" Or CStr(dr("location_name")) = "1") _ And IsNumeric(dr("phone")) Then table.Rows.Add(dr("location_id"), dr("location_name"), ...) End If End While End Using ' SQL Server Connection String Const sqlConnectionString As String = "Data Source=MYPC\SQLEXPRESS; Initial Catalog=billing; Persist Security Info=True;User ID=username;Password=password" ' Bulk Copy to SQL Server Using bulkCopy As New SqlBulkCopy(sqlConnectionString) bulkCopy.DestinationTableName = "tbl_location" bulkCopy.WriteToServer(table) End Using
Hope this helps.
Please let me know how to check if location_id already exists in database and if exists then display error and skip that record before inserting the recodrs into database.
Wednesday, November 13, 2013 12:44 AM -
User1508394307 posted
In this case you might need to select all existing location_id into another datatable, and validate that column in
While dr.Read()
...
End WhileAnother way is check it in the database. You copy all data not in "tbl_location" but in "tbl_location_temp". Once bulkCopy.WriteToServer is executed you can call an additional query to copy only required data.
Example
Using con As SqlConnection = New SqlConnection(sqlConnectionString) con.Open() Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(con) bulkCopy.DestinationTableName = "tbl_location_temp" bulkCopy.WriteToServer(table) End Using Dim cmd As SqlCommand = New SqlCommand( _ "INSERT INTO tbl_location SELECT * FROM tbl_location_temp " & _ "WHERE NOT location_id IN (SELECT location_id FROM tbl_location);" & _ "DELETE FROM tbl_location_temp", con) cmd.ExecuteNonQuery() End Using
This will copy only records with non-existed location_id.
Wednesday, November 13, 2013 2:15 AM