Skip to main content

Upload Excel Sheet into DataBase

Upload excel values into database by mapping the fields
UploadItems.aspx

<%@ Page Language="VB" MasterPageFile="~/MainPage.master" AutoEventWireup="false" CodeFile="UploadItemscsv.aspx.vb" Inherits="UploadItems" title="Upload Items CSV" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
    <style type="text/css">

        .style3
        {
            height: 216px;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContainer" Runat="Server">
   <fieldset ><legend>Add Items into Data Base through CSV </legend>
    <table width="100%">
        <tr>
            <td align="center" colspan="2" >
                <asp:Label ID="lblMsg" runat="server" Font-Bold="True" CssClass="Err"></asp:Label>
                </td>
        </tr>
        <tr>
            <td align="center" colspan="2" >
                &nbsp;</td>
        </tr>
        <tr>
            <td align="right" width="50%">
                Upload CSV File :
                <asp:FileUpload ID="FileUpload1" runat="server" />
            </td>
            <td align="center">
                <asp:Button ID="btnSave" runat="server"  Text="Upload" Width="74px"
                    CssClass="button" />
            </td>
        </tr>
        <tr>
            <td align="right" width="50%">
                &nbsp;</td>
            <td align="center">
                &nbsp;</td>
        </tr>
        <tr>
            <td>
            <asp:UpdatePanel ID="updFieldMapping" runat="server">
            <ContentTemplate>
            <fieldset><legend>Mapping DateBase Fields</legend>
                <table width="100%">
                    <tr>
                        <td align="left">
                            <asp:Label ID="Label1" runat="server" Font-Bold="True"
                                Text="Excel Sheet Fields"></asp:Label>
                        </td>
                        <td>
                            &nbsp;</td>
                        <td>
&nbsp;
                            <asp:Label ID="Label2" runat="server" Font-Bold="True" Text="Data Base Fields"></asp:Label>
&nbsp;</td>
                        <td>
                            &nbsp;</td>
                    </tr>
                    <tr>
                        <td>
                            <asp:ListBox ID="lstExcelFields" runat="server" Height="250px" Width="145px">
                            </asp:ListBox>
                        </td>
                        <td class="style3" width="10">
                            &nbsp;</td>
                        <td>
                            <asp:ListBox ID="lstDBFields" runat="server" Height="250px" Width="145px">
                                <asp:ListItem>Item Name</asp:ListItem>
                            </asp:ListBox>
                        </td>
                        <td class="style3" width="10">
                <asp:Button ID="btnGo" runat="server" Text="Go" Width="50px" CssClass="button" />
                        </td>
                        <td valign="top">
                            <table>
                                <tr>
                                    <td>
                            <asp:TextBox ID="txtName" runat="server" ReadOnly="True"></asp:TextBox>
                            </td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                                <tr>
                                    <td>
                                        &nbsp;</td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                </table>
                </fieldset>
            </ContentTemplate>
            </asp:UpdatePanel>
            </td>
            <td>
            <fieldset><legend>Uploaded CSV File</legend>
            <asp:Panel ID="ExcelListpane" runat='server' Width="100%" ScrollBars="Auto"
                    Height="250px">
                <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
                    GridLines="None" Width="100%">
                    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                    <Columns>
                        <asp:TemplateField ShowHeader="False">
                            <ItemTemplate>
                                <asp:CheckBox ID="CheckBox1" runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                    <EmptyDataTemplate>
                        <asp:Label ID="emptymsg" runat="server" CssClass="Err" Text="No data found"></asp:Label>
                    </EmptyDataTemplate>
                    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <AlternatingRowStyle BackColor="White" />
                </asp:GridView>                                                            
                </asp:Panel>
                <asp:Button
                    ID="btnSelectAll" runat="server" Text="Select All" CssClass="button"
                    Enabled="False" />
                <asp:Button
                    ID="btnDeSelectAll" runat="server" Text="De Select All" CssClass="button"
                    Enabled="False" />
                <asp:Button ID="btnSaveInDb" runat="server" Text="Save" CssClass="button"
                    Enabled="False" />
             </fieldset>
            </td>
        </tr>
        <tr>
            <td colspan="2">
                &nbsp;</td>
        </tr>
        </table>
    </fieldset>
</asp:Content>



UploadItems.aspx.vb


Imports System.IO
Imports System.Data
Imports System.Data.OleDb

Partial Class UploadItems
    Inherits System.Web.UI.Page

    Dim str As String = ""

    'Dim mycon As New MySqlConnection(ConfigurationManager.ConnectionStrings("INVConnectionString").ConnectionString)
    'Dim mycon As New SqlConnection("Data Source=whurray;Initial Catalog=whurrayDB;User ID=ray;Password=ray;Pooling=False")
    'Dim mycom As New MySqlCommand
    'Dim ds As New DataSet
    'Dim dr As DataRow
    'Dim ada As MySqlDataAdapter
    'Dim strMemId As String

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            If FileUpload1.HasFile Then
                Dim fileExt As String
                fileExt = System.IO.Path.GetExtension(FileUpload1.FileName)
                If fileExt = ".csv" Or fileExt = ".CSV" Then
                    'Make variables needed
                    Dim objStream As Stream

                    Dim fileLen As Integer
                    'Get the length of the file.
                    fileLen = FileUpload1.PostedFile.ContentLength
                    'Create a byte array to hold the contents of the file.
                    Dim Input(fileLen) As Byte
                    'Move byte array into a stream, then start it at beginning and pass it to method.
                    objStream = FileUpload1.PostedFile.InputStream
                    objStream.Read(Input, 0, fileLen)
                    objStream.Position = 0
                    'Manage File
                    importToDG(objStream)
                    lblMsg.Visible = True
                    SaveUploadedFile()
                    lblMsg.Text = "Csv File Uploaded Successfully with " & GridView1.Rows.Count & " records."
                    'lblcount.Text = "Records found : " & GridView1.Rows.Count
                    ' btnSaveInDb.Visible = True
                    'btnSelectAll.Visible = True
                    btnSelectAll.Enabled = True
                    btnDeSelectAll.Enabled = True
                    btnSaveInDb.Enabled = True
                Else
                    lblMsg.Visible = True
                    lblMsg.Text = "Please upload csv files"                  
                    Exit Sub
                End If
            End If
        Catch ex As Exception
            lblMsg.Visible = True
            lblMsg.Text = ex.ToString
        End Try
    End Sub

    Private Sub importToDG(ByVal fileStream As Stream)
        Dim streamRdr As StreamReader
        Dim fileLines As String()
        Dim line As String

        streamRdr = New StreamReader(fileStream)

        Dim myDTable As DataTable = New DataTable
        Dim myDRow As DataRow = myDTable.NewRow
        'Dim ItemName, PhoneNo As New DataColumn

        'ItemName.ColumnName = "Item Name"
        'PhoneNo.ColumnName = "PhoneNo"
        'EmailID.ColumnName = "EmailID"
        'Address.ColumnName = "Address"
        'City.ColumnName = "City"
        'State.ColumnName = "State"
        'Country.ColumnName = "Country"
        'CompanyName.ColumnName = "CompanyName"
        'PhoneNo1.ColumnName = "PhoneNo1"
        'PhoneNo2.ColumnName = "PhoneNo2"
        'PhoneNo3.ColumnName = "PhoneNo3"
        'PhoneNo4.ColumnName = "PhoneNo4"

        'myDTable.Columns.Add(ItemName)
        'myDTable.Columns.Add(PhoneNo)
        'myDTable.Columns.Add(EmailID)
        'myDTable.Columns.Add(Address)
        'myDTable.Columns.Add(City)
        'myDTable.Columns.Add(State)
        'myDTable.Columns.Add(Country)
        'myDTable.Columns.Add(CompanyName)
        'myDTable.Columns.Add(PhoneNo1)
        'myDTable.Columns.Add(PhoneNo2)
        'myDTable.Columns.Add(PhoneNo3)
        'myDTable.Columns.Add(PhoneNo4)

        'Now set the streamreader back to beginning.
        streamRdr.DiscardBufferedData()
        streamRdr.BaseStream.Seek(0, SeekOrigin.Begin)
        streamRdr.BaseStream.Position = 0

        Dim i As Integer = 0 'Skip the first row.
        While Not streamRdr.EndOfStream
            line = streamRdr.ReadLine 'Read first line
            fileLines = line.Split(",") 'Split the line up by the delimeter
            If i = 0 Then

                For j As Integer = 0 To fileLines.Length - 1
                    lstExcelFields.Items.Add(fileLines(j))
                    myDTable.Columns.Add(New DataColumn(fileLines(j)))

                Next
            End If
            If i > 0 Then
                myDRow.ItemArray = fileLines
                myDTable.Rows.Add(myDRow)
                myDRow = myDTable.NewRow
                'Else
                '    myDRow.ItemArray = fileLines
                '    myDTable.Rows.Add(myDRow)
                '    myDTable.Columns.Add(fileLines(0))
                '    myDTable.Columns.Add(fileLines(1))
                '    myDTable.Columns.Add(fileLines(2))

            End If
            i += 1
        End While
        GridView1.DataSource = myDTable
        GridView1.DataBind()

    End Sub

    Public Sub GetData()

        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & str & ";Extended Properties=Excel 12.0"
        ' Create the connection object
        Dim oledbConn As OleDbConnection = New OleDbConnection(connString)
        Try
            ' Open connection
            oledbConn.Open()

            ' Create OleDbCommand object and select data from worksheet Sheet3
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)

            ' Create new OleDbDataAdapter
            Dim oleda As OleDbDataAdapter = New OleDbDataAdapter()
            oleda.SelectCommand = cmd

            ' Create a DataSet which will hold the data extracted from the worksheet.
            Dim ds As DataSet = New DataSet()

            ' Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Sheet1")

            ' Bind the data to the GridView
            GridView1.DataSource = ds.Tables(0).DefaultView
            GridView1.DataBind()

        Catch ex As Exception
            lblMsg.Visible = True
            lblMsg.Text = ex.Message
        Finally
            ' Close connection
            oledbConn.Close()
        End Try
    End Sub

    Protected Sub btnSelectAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectAll.Click
        Try
            Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
            For j As Integer = 0 To GridView1.Rows.Count - 1
                GridView1.SelectedIndex = j
                checkbox1 = GridView1.SelectedRow.FindControl("checkbox1")
                checkbox1.Checked = True
            Next
        Catch ex As Exception
            lblMsg.Text = ex.ToString
            lblMsg.Visible = True
        End Try
    End Sub

    Protected Sub btnSaveInDb_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveInDb.Click
        If ViewState.Count < 1 Then      
            lblMsg.Text = "Please Map the Item Name field"
            Return
        End If

        Try
            Dim uploaditems As Integer = 0
            Dim existsitems As Integer = 0
            Dim selecteditems As Integer = 0
            Dim skipeditems As Integer = 0
            Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
            For j As Integer = 0 To GridView1.Rows.Count - 1
                'GridView1.SelectedIndex = j
                checkbox1 = GridView1.Rows(j).FindControl("checkbox1")
                'GridView1.Rows(j).FindControl("checkbox1").
                If checkbox1.Checked Then
                    selecteditems += 1
                    Dim str As String = GridView1.Rows(j).Cells(ViewState("str1")).Text
                    If GridView1.Rows(j).Cells(ViewState("str1")).Text.Length > 3 And str <> "&nbsp;" Then
                        Dim len As Integer = GridView1.Rows(j).Cells(ViewState("str1")).Text.Length
                        If clsItems.AddItem(GridView1.Rows(j).Cells(ViewState("str1")).Text) = 1 Then
                            uploaditems += 1
                        Else
                            existsitems += 1
                        End If
                    Else
                        skipeditems += 1
                    End If
                End If
            Next
            lblMsg.Text = selecteditems & " Items Selected, " & uploaditems & " items Uploaded," & existsitems & " Items existed, " & skipeditems & " Items skipped"
        Catch ex As Exception
            lblMsg.Text = ex.ToString
            lblMsg.Visible = True
        End Try
    End Sub

    Protected Sub btnDeSelectAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDeSelectAll.Click
        Try
            Dim checkbox1 As New CheckBox '= GridView1.SelectedRow.FindControl("checkbox1")
            For j As Integer = 0 To GridView1.Rows.Count - 1
                GridView1.SelectedIndex = j
                checkbox1 = GridView1.SelectedRow.FindControl("checkbox1")
                checkbox1.Checked = False
            Next
        Catch ex As Exception
            lblMsg.Text = ex.ToString
            lblMsg.Visible = True
        End Try
    End Sub

    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
        If lstDBFields.SelectedIndex = 0 Then
            txtName.Text = lstExcelFields.SelectedItem.Text & " --> " & lstDBFields.SelectedItem.Text
            Session.Add("strj1", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
            'Session("str1") = lstExcelFields.SelectedIndex + 1
            ViewState("str1") = lstExcelFields.SelectedIndex + 1
        End If
        'If lstDBFields.SelectedIndex = 2 Then
        '    txtPhoneNo.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj2", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 3 Then

        '    txtEmailId.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj3", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 4 Then
        '    txtAddress.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj4", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 5 Then
        '    txtCity.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj5", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 6 Then
        '    txtState.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj6", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 7 Then
        '    txtCountry.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj7", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 8 Then
        '    txtCompanyName.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj8", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 9 Then
        '    txtPhoneNo1.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj9", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 10 Then
        '    txtPhoneNo2.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj10", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 11 Then
        '    txtPhoneNo3.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj11", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
        'If lstDBFields.SelectedIndex = 12 Then
        '    txtPhoneNo4.Text = lstExcelFields.SelectedItem.Text & " as " & lstDBFields.SelectedItem.Text
        '    Session.Add("strj12", lstExcelFields.SelectedIndex & "," & lstDBFields.SelectedIndex)
        'End If
    End Sub
    Public Sub SaveUploadedFile()
        If FileUpload1.HasFile Then
            FileUpload1.SaveAs((Server.MapPath(".\Uploads") & "\") & FileUpload1.FileName)
            str = Server.MapPath(".\Uploads") & "\" & FileUpload1.FileName
        End If
    End Sub
End Class
Labels:

Comments

Post a Comment

Popular posts from this blog

PNR Status by web Scraping Method (ASP.NET) C#

To Get the PNR Status by web Scraping Method Steps to Execute the Function Step 1 : Add the below method in your Form and Pass the PNR Number arguement public string GetPNRStatus( string sPNR) { string URI = "http://www.indianrail.gov.in/cgi_bin/inet_pnrstat_cgi.cgi" ; string Parameters = Uri .EscapeUriString( "lccp_pnrno1=" +sPNR+ "&amp;submitpnr=Get Status" ); System.Net. HttpWebRequest req = ( HttpWebRequest )System.Net. WebRequest .Create(URI); //HTTP POST Headers req.ContentType = "application/x-www-form-urlencoded" ; req.Host = "www.indianrail.gov.in" ; //You can use your own user-agent. req.UserAgent = "Mozilla/5.0 (compatible; MSIE 7.0; Windows Phone OS 7.5; Trident/5.0; IEMobile/9.0) DELL;Venue Pro" ; req.Headers.Add( HttpRequestHeader .AcceptLanguage, "en-us,en;q=0.5" ); req.Headers.Add( HttpRequestHeader .AcceptCharset, "ISO-8859-1,utf-8;q=...
20 comments
Read more

SonarQube Configuration For .NET Core Web API

When multiple developers are working on the same project, it's good to have a code review. SonarQube is a tool through which we can evaluate our code. Here, for demo purposes, we are going to evaluate the web API which is built on .NET Core. Let's see step by step implementation. In order to run SonarQube, we need to install JAVA in our local system.   Refer to the below link to download JAVA installer and install JAVA. https://www.oracle.com/technetwork/java/javase/downloads/index-jsp-138363.html Configure the 'PATH' system variable under environment variables Go to Control Panel > System > Advanced System Settings, it will open the System Properties window. Click on the "Environment Variables" button. Click on the "View" button under User Variables. Give the variable name as 'JAVA_HOME'. The variable value will be your JDK path where you installed JAVA. Select path variable under system variable and click o...
Post a Comment
Read more

Automate Git Commands Across Repos Using Batch Scripts

  Writing Scripts to Apply Commands Across Repositories I store all of my local repositories in a particular directory. I wrote these scripts to automate one or more git commands to run on each repository within that directory. It could also be used to do a general clean-up before committing anything to your repository. Here are two ways to do it. Method 1: Use this when you want to store commands in a single file to be executed in sequence for every repo in your directory. 1 @echo off 2 ​ 3 for /d %%i in (%cd%\*) do ( 4 echo ************************************************************************* 5 ​ 6 echo "%%i" 7 cd "%%i" 8 ​ 9 echo ----------------------------------------- 10 echo status 11 git status 12 echo ----------------------------------------- 13 ​ 14 echo ************************************************************************* 15 ) 16 ​ 17 cd .. 18 ​ The script is simple: It’s composed of a single for loop. W...
Post a Comment
Read more