Reading CSV into Dataset (BES Log files)

jonturlington 0 Newbie Poster

14 Years Ago

It gives an incorrect row count and the column count only shows 1.

So when I try to handle the true number of columns in the CSV file it reports an OutOfIndexRange error.

Any and all help would be greatly appreciated.

Example Data:

"Name.ID","Type of Call","Name","Phone Number","Start Date","Server Log Date","Elapsed Time","Memo","Command","UID"
"Name Removed","Outgoing","","5555555555","2009/04/20 20:00:25","2009/05/14 09:29:10","00:00:43","","Update","5555555555"
"Name Removed","Incoming - Missed Call, Unopened","Name Removed","5555555555","2009/05/11 20:32:41","2009/05/14 09:29:10","00:00:00","","Update","5555555555"
"Name Removed","Incoming - Missed Call, Unopened","","5555555555","2009/04/20 19:59:20","2009/05/14 09:29:10","00:00:00","","Update","5555555555"
"Name Removed","Incoming - Completed","","5555555555","2009/04/20 19:58:41","2009/05/14 09:29:10","00:00:05","","Update","5555555555"
"Name Removed","Incoming - Completed","Name Removed","5555555555","2009/04/20 17:13:41","2009/05/14 09:29:10","00:00:15","","Update","5555555555"
"Name Removed","Incoming - Completed","","5555555555","2009/04/20 16:26:26","2009/05/14 09:29:10","00:00:26","","Update","5555555555"

Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports System.Threading

Module Module1

    Dim LogDirectory As String = "C:\BES Log Importer\Logs"
    Dim IODirectory As DirectoryInfo = New DirectoryInfo(LogDirectory)

    Sub Main()
        For Each IOFile As FileInfo In IODirectory.GetFiles("*.csv", SearchOption.AllDirectories)
            Console.WriteLine("Current File: " & IOFile.Name)
            AddToDatabase(IOFile.Name, IOFile.DirectoryName)
        Next
        Console.Read()
        Process.GetCurrentProcess.Kill()
    End Sub

    Sub AddToDatabase(ByVal mFile As String, ByVal mFolder As String)
        Dim ConnectionString, CommandText As String
        Dim conn As OleDb.OleDbConnection
        Dim Command As OleDbCommand
        Dim Count As Integer

        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mFolder & ";Extended Properties='text;HDR=Yes;FMT=Delimited';"
        CommandText = "select * from " & mFile

        conn = New System.Data.OleDb.OleDbConnection(ConnectionString)
        Command = New System.Data.OleDb.OleDbCommand(CommandText, conn)

        conn.Open()

        Dim da As OleDbDataAdapter = New OleDbDataAdapter(CommandText, conn)
        Dim ds As DataSet = New DataSet
        da.Fill(ds, mFile)

        Console.WriteLine(mFile & " Row Count: " & ds.Tables(0).Rows.Count)
        Console.WriteLine(mFile & " Column Count: " & ds.Tables(0).Columns.Count)

        Dim SqlConn As SqlConnection = New SqlConnection("Data Source=XXX-XXX\ULOG;Initial Catalog=BESLogs;User Id=sa;Password=XXXXXXXX;")
        Dim SqlComm As SqlCommand = New SqlCommand("Select * from nothing", SqlConn)

        For Count = 0 To ds.Tables(0).Rows.Count - 1
            If Count < 0 Then Exit For
            If Not (SqlComm Is Nothing) Then SqlComm.Parameters.Clear()
            If mFile.StartsWith("S") Then

                Console.WriteLine(ds.Tables(0).Rows(Count).Item(0) & "," & ds.Tables(0).Rows(Count).Item(1) & "," & ds.Tables(0).Rows(Count).Item(2) & "," & ds.Tables(0).Rows(Count).Item(3) & "," & ds.Tables(0).Rows(Count).Item(4) & "," & ds.Tables(0).Rows(Count).Item(5) & "," & ds.Tables(0).Rows(Count).Item(6) & "," & ds.Tables(0).Rows(Count).Item(7) & "," & ds.Tables(0).Rows(Count).Item(8) & "," & ds.Tables(0).Rows(Count).Item(9) & "," & ds.Tables(0).Rows(Count).Item(10) & "," & ds.Tables(0).Rows(Count).Item(11))

                SqlComm = New SqlCommand("INSERT INTO [BESLogs].[dbo].[SMSLog]([NameID], [EmailAddress], [TypeofMessage], [To], [From], [CallbackPhoneNumber], [Body], [SendReceivedDate], [ServerLogDate], [OverallMessageStatus], [Command], [UID]) VALUES(@SqlParam0,@SqlParam1,@SqlParam2,@SqlParam3,@SqlParam4,@SqlParam5,@SqlParam6,@SqlParam7,@SqlParam8,@SqlParam9,@SqlParam10,@SqlParam11)", SqlConn)

                Dim SqlParam0 As SqlParameter = New SqlParameter("SqlParam0", SqlDbType.VarChar, 100)
                Dim SqlParam1 As SqlParameter = New SqlParameter("SqlParam1", SqlDbType.VarChar, 100)
                Dim SqlParam2 As SqlParameter = New SqlParameter("SqlParam2", SqlDbType.VarChar, 100)
                Dim SqlParam3 As SqlParameter = New SqlParameter("SqlParam3", SqlDbType.VarChar, 100)
                Dim SqlParam4 As SqlParameter = New SqlParameter("SqlParam4", SqlDbType.VarChar, 100)
                Dim SqlParam5 As SqlParameter = New SqlParameter("SqlParam5", SqlDbType.VarChar, 100)
                Dim SqlParam6 As SqlParameter = New SqlParameter("SqlParam6", SqlDbType.VarChar, 8000)
                Dim SqlParam7 As SqlParameter = New SqlParameter("SqlParam7", SqlDbType.DateTime)
                Dim SqlParam8 As SqlParameter = New SqlParameter("SqlParam8", SqlDbType.DateTime)
                Dim SqlParam9 As SqlParameter = New SqlParameter("SqlParam9", SqlDbType.VarChar, 100)
                Dim SqlParam10 As SqlParameter = New SqlParameter("SqlParam10", SqlDbType.VarChar, 100)
                Dim SqlParam11 As SqlParameter = New SqlParameter("SqlParam11", SqlDbType.VarChar, 100)

                SqlParam0.Value = ds.Tables(0).Rows(Count).Item(0)
                SqlParam1.Value = ds.Tables(0).Rows(Count).Item(1)
                SqlParam2.Value = ds.Tables(0).Rows(Count).Item(2)
                SqlParam3.Value = ds.Tables(0).Rows(Count).Item(3)
                SqlParam4.Value = ds.Tables(0).Rows(Count).Item(4)
                SqlParam5.Value = ds.Tables(0).Rows(Count).Item(5)
                SqlParam6.Value = ds.Tables(0).Rows(Count).Item(6)
                SqlParam7.Value = ds.Tables(0).Rows(Count).Item(7)
                SqlParam8.Value = ds.Tables(0).Rows(Count).Item(8)
                SqlParam9.Value = ds.Tables(0).Rows(Count).Item(9)
                SqlParam10.Value = ds.Tables(0).Rows(Count).Item(10)
                SqlParam11.Value = ds.Tables(0).Rows(Count).Item(11)

                SqlComm.Parameters.Add(SqlParam0)
                SqlComm.Parameters.Add(SqlParam1)
                SqlComm.Parameters.Add(SqlParam2)
                SqlComm.Parameters.Add(SqlParam3)
                SqlComm.Parameters.Add(SqlParam4)
                SqlComm.Parameters.Add(SqlParam5)
                SqlComm.Parameters.Add(SqlParam6)
                SqlComm.Parameters.Add(SqlParam7)
                SqlComm.Parameters.Add(SqlParam8)
                SqlComm.Parameters.Add(SqlParam9)
                SqlComm.Parameters.Add(SqlParam10)
                SqlComm.Parameters.Add(SqlParam11)

                SqlComm.Connection.Open()
                Console.WriteLine(SqlComm.CommandText)
                SqlComm.ExecuteNonQuery()
                SqlComm.Connection.Close()

                System.Threading.Thread.Sleep(50)

            Else

                Console.WriteLine(ds.Tables(0).Rows(Count).Item(0) & "," & ds.Tables(0).Rows(Count).Item(1) & "," & ds.Tables(0).Rows(Count).Item(2) & "," & ds.Tables(0).Rows(Count).Item(3) & "," & ds.Tables(0).Rows(Count).Item(4) & "," & ds.Tables(0).Rows(Count).Item(5) & "," & ds.Tables(0).Rows(Count).Item(6) & "," & ds.Tables(0).Rows(Count).Item(7) & "," & ds.Tables(0).Rows(Count).Item(8) & "," & ds.Tables(0).Rows(Count).Item(9) & "," & ds.Tables(0).Rows(Count).Item(10))

                SqlComm = New SqlCommand("INSERT INTO [BESLogs].[dbo].[PhoneCallLog]([NameID], [TypeofCall], [Name], [PhoneNumber], [StartDate], [ServerLogDate], [ElapsedTime], [Memo], [Command], [UID], [PhoneLine]) VALUES(@SqlParam0,@SqlParam1,@SqlParam2,@SqlParam3,@SqlParam4,@SqlParam5,@SqlParam6,@SqlParam7,@SqlParam8,@SqlParam9,@SqlParam10)", SqlConn)

                Dim SqlParam0 As SqlParameter = New SqlParameter("SqlParam0", SqlDbType.VarChar, 100)
                Dim SqlParam1 As SqlParameter = New SqlParameter("SqlParam1", SqlDbType.VarChar, 100)
                Dim SqlParam2 As SqlParameter = New SqlParameter("SqlParam2", SqlDbType.VarChar, 100)
                Dim SqlParam3 As SqlParameter = New SqlParameter("SqlParam3", SqlDbType.VarChar, 100)
                Dim SqlParam4 As SqlParameter = New SqlParameter("SqlParam4", SqlDbType.DateTime)
                Dim SqlParam5 As SqlParameter = New SqlParameter("SqlParam5", SqlDbType.DateTime)
                Dim SqlParam6 As SqlParameter = New SqlParameter("SqlParam6", SqlDbType.VarChar, 100)
                Dim SqlParam7 As SqlParameter = New SqlParameter("SqlParam7", SqlDbType.VarChar, 100)
                Dim SqlParam8 As SqlParameter = New SqlParameter("SqlParam8", SqlDbType.VarChar, 100)
                Dim SqlParam9 As SqlParameter = New SqlParameter("SqlParam9", SqlDbType.VarChar, 100)
                Dim SqlParam10 As SqlParameter = New SqlParameter("SqlParam10", SqlDbType.VarChar, 100)

                SqlParam0.Value = ds.Tables(0).Rows(Count).Item(0)
                SqlParam1.Value = ds.Tables(0).Rows(Count).Item(1)
                SqlParam2.Value = ds.Tables(0).Rows(Count).Item(2)
                SqlParam3.Value = ds.Tables(0).Rows(Count).Item(3)
                SqlParam4.Value = ds.Tables(0).Rows(Count).Item(4)
                SqlParam5.Value = ds.Tables(0).Rows(Count).Item(5)
                SqlParam6.Value = ds.Tables(0).Rows(Count).Item(6)
                SqlParam7.Value = ds.Tables(0).Rows(Count).Item(7)
                SqlParam8.Value = ds.Tables(0).Rows(Count).Item(8)
                SqlParam9.Value = ds.Tables(0).Rows(Count).Item(9)
                SqlParam10.Value = ds.Tables(0).Rows(Count).Item(10)

                SqlComm.Parameters.Add(SqlParam0)
                SqlComm.Parameters.Add(SqlParam1)
                SqlComm.Parameters.Add(SqlParam2)
                SqlComm.Parameters.Add(SqlParam3)
                SqlComm.Parameters.Add(SqlParam4)
                SqlComm.Parameters.Add(SqlParam5)
                SqlComm.Parameters.Add(SqlParam6)
                SqlComm.Parameters.Add(SqlParam7)
                SqlComm.Parameters.Add(SqlParam8)
                SqlComm.Parameters.Add(SqlParam9)
                SqlComm.Parameters.Add(SqlParam10)

                SqlComm.Connection.Open()
                Console.WriteLine(SqlComm.CommandText)
                SqlComm.ExecuteNonQuery()
                SqlComm.Connection.Close()

                System.Threading.Thread.Sleep(50)

            End If
        Next

    End Sub

End Module

vb.net

Edited 14 Years Ago by jonturlington because: n/a

1 Contributor
0 Replies
51 Views

Be the first to reply

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.