locked
How to verify data while reading from excel sheet and then store in database RRS feed

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

    Wednesday, November 13, 2013 2:15 AM