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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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 While 

Another 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.