Chittibabu

=====================================
    Public Sub ReadAndImportExcelData()
        Dim MyCommand As OleDbDataAdapter
        Dim i

        Dim Dttbl As New DataTable
        Dim str As String = String.Empty
        Dim csImport As New Import
        Dim ExcelFilePath As String = Server.MapPath("GAreport.xls")
        Dim Filepath As String = ExcelFilePath
        Dim tempArr As String() = Filepath.Split("\")
        Dim fileName As String = tempArr(tempArr.Length - 1).ToString
        Dim dtStr As String = DateAndTime.Now()
        dtStr = dtStr.Replace("/", "_").Replace(":", "_").Replace(" ", "_")
        csImport.ImportKey = dtStr & "_" & fileName

        csImport.ProjectID = "007"
        Dim MyConnection As OleDbConnection
        MyConnection = New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" & ExcelFilePath & "; Extended Properties=Excel 8.0")
        MyCommand = New OleDbDataAdapter("select * from [GoogleData$]", MyConnection)

        MyCommand.Fill(Dttbl)
        If Dttbl.Rows.Count > 0 Then
            For i = 0 To Dttbl.Rows.Count - 1
                Dim j = 0
                For Each dc As DataColumn In Dttbl.Columns
                    If dc.ColumnName = "Referral Path" Then
                        csImport.ReferralPath = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Source" Then
                        csImport.Source = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Keyword" Then
                        csImport.Keyword = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Mobile" Then
                        csImport.Mobile = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Country/Territory" Then
                        csImport.Country_Territory = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Page Path" Then
                        csImport.PagePath = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Hour" Then
                        csImport.Hour = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "New Visits" Then
                        csImport.NewVisits = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Pageviews" Then
                        csImport.PageViews = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Visitors" Then
                        csImport.Visitors = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Time on Site" Then
                        csImport.TimeOnSite = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Time on Page" Then
                        csImport.TimeOnPage = Dttbl.Rows(i)(j)
                    ElseIf dc.ColumnName = "Entrances" Then
                        csImport.Entrances = Dttbl.Rows(i)(j)
                    End If
                    j += 1
                Next
                csImport.ImportData()
            Next
        End If
    End Sub
=====================================
    Public Sub ReadExcelWorkBookSheets()
        Dim objExcelConn As New OleDb.OleDbConnection
        Dim i As Integer
        Dim sSName As String = ""
        Dim dtTables As DataTable

        objExcelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Server.MapPath("GAreport1.xls") & "';Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
        objExcelConn.Open()

        dtTables = objExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dtTables.Rows.Count > 0 Then
            For i = 0 To dtTables.Rows.Count - 1
                sSName += dtTables.Rows(i)("TABLE_NAME") & "<br>"

            Next
        End If
        Response.Write(sSName)
    End Sub
=====================================
Public Class Import
#Region "Private Variables"
    Dim _ReferralPath As String
    Dim _Source As String
    Dim _Keyword As String
    Dim _Mobile As String
    Dim _Country_Territory As String
    Dim _PagePath As String
    Dim _Hour As Integer
    Dim _NewVisits As Integer
    Dim _PageViews As Integer
    Dim _Visitors As Integer
    Dim _TimeOnSite As Integer
    Dim _TimeOnPage As Integer
    Dim _Entrances As Integer
    Dim _ImportKey As String
    Dim _ProjectID As Integer

#End Region
#Region "Public Properties"
    Public Property ReferralPath() As String
        Get
            Return _ReferralPath
        End Get
        Set(ByVal Value As String)
            _ReferralPath = Value
        End Set
    End Property
    Public Property Source() As String
        Get
            Return _Source
        End Get
        Set(ByVal Value As String)
            _Source = Value
        End Set
    End Property
    Public Property Keyword() As String
        Get
            Return _Keyword
        End Get
        Set(ByVal Value As String)
            _Keyword = Value
        End Set
    End Property
    Public Property Mobile() As String
        Get
            Return _Mobile
        End Get
        Set(ByVal Value As String)
            _Mobile = Value
        End Set
    End Property
    Public Property Country_Territory() As String
        Get
            Return _Country_Territory
        End Get
        Set(ByVal Value As String)
            _Country_Territory = Value
        End Set
    End Property
    Public Property PagePath() As String
        Get
            Return _PagePath
        End Get
        Set(ByVal Value As String)
            _PagePath = Value
        End Set
    End Property
    Public Property Hour() As Integer
        Get
            Return _Hour
        End Get
        Set(ByVal Value As Integer)
            _Hour = Value
        End Set
    End Property
    Public Property NewVisits() As Integer
        Get
            Return _NewVisits
        End Get
        Set(ByVal Value As Integer)
            _NewVisits = Value
        End Set
    End Property
    Public Property PageViews() As Integer
        Get
            Return _PageViews
        End Get
        Set(ByVal Value As Integer)
            _PageViews = Value
        End Set
    End Property
    Public Property Visitors() As Integer
        Get
            Return _Visitors
        End Get
        Set(ByVal Value As Integer)
            _Visitors = Value
        End Set
    End Property
    Public Property TimeOnSite() As Integer
        Get
            Return _TimeOnSite
        End Get
        Set(ByVal Value As Integer)
            _TimeOnSite = Value
        End Set
    End Property
    Public Property TimeOnPage() As Integer
        Get
            Return _TimeOnPage
        End Get
        Set(ByVal Value As Integer)
            _TimeOnPage = Value
        End Set
    End Property
    Public Property Entrances() As Integer
        Get
            Return _Entrances
        End Get
        Set(ByVal Value As Integer)
            _Entrances = Value
        End Set
    End Property
    Public Property ImportKey() As String
        Get
            Return _ImportKey
        End Get
        Set(ByVal Value As String)
            _ImportKey = Value
        End Set
    End Property
    Public Property ProjectID() As Integer
        Get
            Return _ProjectID
        End Get
        Set(ByVal Value As Integer)
            _ProjectID = Value
        End Set
    End Property
#End Region
    Public Sub ImportData()
        Dim ConStr As String = ConfigurationManager.AppSettings("ConnectionString")
        Dim sql As String
        With Me
            sql = "INSERT INTO GA_FeedInfo (ReferralPath, Source, Keyword, Mobile,Country_Territory,PagePath,Hour,NewVisits,PageViews,Visitors,TimeOnSite,TimeOnPage,Entrances,ImportKey,ProjectID) " & _
                         " values('" & .ReferralPath & "','" & .Source & "','" & .Keyword & "','" & .Mobile & "','" & .Country_Territory & "','" & .PagePath & "','" & .Hour & "','" & .NewVisits & "','" & .PageViews & "','" & .Visitors & "','" & .TimeOnSite & "','" & .TimeOnPage & "','" & .Entrances & "','" & .ImportKey & "','" & .ProjectID & "')"
        End With
        SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql)
    End Sub
End Class

Tweet