Reading in Excel data

Discussion in 'Programming General' started by fredecus, Apr 3, 2012.

Reading in Excel data
  1. Unread Apr 3, 2012 at 12:56 PM
  2. Joined:
    Jan 17, 2010
    Posts:
    871
    Referrals:
    0
    Location:
    Alabama
    fredecus

    fredecus Apprentice

    Reading in Excel data

    Hey, I have a bunch of old data in excel files. I learned VB a loonggg time ago and have forgotten a good bit of it.
    I have a form that pops up asking to browse for an excel file.
    Next you select an increment in the time stamp for it to scan through (example: if you put 5 it will pull data from every 5 seconds)
    It then reads through the file and pulls the data out and presents it in a form and also saves it as a .txt.

    How could I get it to also print out information that is missing?
    Say it pulls out 0,5,15,20. How could I get it to say that 10 is missing from the document?

    I will pay well for help here if it becomes too much.

    Code:
    Imports System.IO
    
    Imports System.Text
    
     
    
    Public Class Form1
    
        Public Fname As String = "C:\" 'Set Default FileName
    
        Public Sname As String = "C:\PMIResults.txt" 'Set default save location
    
        Public secondcheck As Boolean 'Variable to determine if the Multiple Check box is checked
    
        Public browsed As Boolean = False
    
        Public numberOfPmi As Integer
    
     
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            'Browse button gives the capability to choose a file other than the default to input into the form
    
            OpenFileDialog1.ShowDialog()
    
            Fname = OpenFileDialog1.FileName
    
            Label1.Text = Fname
    
            browsed = True
    
        End Sub
    
        Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    
            'Browse button gives the capability to choose a file name other than the default to save the form into
    
            SaveFileDialog1.ShowDialog()
    
            Sname = SaveFileDialog1.FileName
    
            Label3.Text = Sname
    
        End Sub
    
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 'Click of the Run button
    
            Dim data(), temp1, myfilename_temp() As String
    
            Dim check_time As Decimal
    
            Dim run_count As Integer = 1
    
            numberOfPmi = 0
    
            'Opens file dialog if Filename is not the default
    
    95:     If browsed = False Then
    
                Dim didwork As Integer
    
                didwork = OpenFileDialog1.ShowDialog()
    
                Fname = OpenFileDialog1.FileName
    
                If didwork = DialogResult.Cancel Then
    
                    GoTo 99
    
                End If
    
            End If
    
            
    
            check_time = TextBox1.Text 'Set the first interval time to check
    
     
    
            Dim fileReader As System.IO.StreamReader
    
            Dim cols, rows, i, x, end_line, start_line As Integer
    
     
    
            fileReader = File.OpenText(Fname) 'Open file temporarily to determine the number of columns
    
            temp1 = fileReader.ReadLine
    
            data = Split(temp1, ControlChars.Tab)
    
            cols = data.LongLength
    
            rows = 0
    
     
    
            Do 'Counts the number of rows and finds the start and end points of the data
    
                temp1 = fileReader.ReadLine
    
                rows = rows + 1
    
     
    
                If temp1 = "#End_Data" Then 'Finds the end of the data
    
                    end_line = rows - 1
    
                    Exit Do
    
                    'Exits the loop if it finds #End_Data
    
                End If
    
     
    
                If temp1 = "#Start_Data" Then 'Finds the start of the data
    
                    start_line = rows + 1
    
                End If
    
     
    
            Loop Until temp1 Is Nothing
    
     
    
    63:     fileReader.Close()
    
            fileReader = File.OpenText(Fname) 'Reopens the file
    
     
    
            Dim table As String
    
            Dim Reader As New System.IO.StreamReader(Fname)
    
     
    
            table = Reader.ReadToEnd 'Reads the file
    
            Reader.Close()
    
            data = table.Split(ControlChars.Tab)
    
            fileReader.Close() 'Closes the file
    
            x = 0
    
     
    
            Dim stringmatch As Boolean
    
            Dim time((end_line - start_line)) As String
    
     
    
            For i = 0 To (data.Length - 1) 'Searches for data in the the file with the yyyy:ddd:hh:mm:ss format
    
                stringmatch = data(i) Like "####:###:##:##:##"
    
                If stringmatch = True Then
    
                    time(x) = data(i) 'If the format is matched, store the data in the time variable
    
                    x = x + 1
    
                End If
    
            Next
    
     
    
            Dim time1 As String
    
            Dim years1, days1, hours1, minutes1, seconds1, years2, days2, hours2, minutes2, seconds2 As String
    
            time1 = ""
    
     
    
            For i = 0 To time.Length - 1 'Loop concatenates the time variable into a string
    
                time1 = time1 & ":" & time(i)
    
            Next
    
     
    
            Dim gap_start As String
    
            Dim gap_time
    
            Dim gap_end
    
            Dim myfilename = Sname
    
            Dim MyTXTWriter As System.IO.StreamWriter
    
     
    
    42:     MyTXTWriter = My.Computer.FileSystem.OpenTextFileWriter(myfilename, False)
    
            MyTXTWriter.WriteLine("Permanently Missing Interval (PMI) Within Given Data File" & vbCrLf)
    
            MyTXTWriter.WriteLine("Start:" & vbTab & vbTab & vbTab & "Length:" & vbTab & vbTab & "End:" & vbCrLf)
    
     
    
            Dim hms As TimeSpan
    
            Dim h, m, s As String
    
     
    
            For i = 0 To (time.Length - 2) 'This loop separates the string into individual pieces to compare for gaps
    
                years1 = (time1(18 * i + 1) & time1(18 * i + 2) & time1(18 * i + 3) & time1(18 * i + 4))
    
                days1 = (time1(18 * i + 6) & time1(18 * i + 7) & time1(18 * i + 8))
    
                hours1 = (time1(18 * i + 10) & time1(18 * i + 11))
    
                minutes1 = (time1(18 * i + 13) & time1(18 * i + 14))
    
                seconds1 = (time1(18 * i + 16) & time1(18 * i + 17))
    
                years2 = (time1(18 * (i + 1) + 1) & time1(18 * (i + 1) + 2) & time1(18 * (i + 1) + 3) & time1(18 * (i + 1) + 4))
    
                days2 = (time1(18 * (i + 1) + 6) & time1(18 * (i + 1) + 7) & time1(18 * (i + 1) + 8))
    
                hours2 = (time1(18 * (i + 1) + 10) & time1(18 * (i + 1) + 11))
    
                minutes2 = (time1(18 * (i + 1) + 13) & time1(18 * (i + 1) + 14))
    
                seconds2 = (time1(18 * (i + 1) + 16) & time1(18 * (i + 1) + 17))
    
                'This If loop does the actual comparison and uses a new self indexed variable to keep track of how many PMIs it encounters
    
     
    
                If (Val(hours2) * 3600 + Val(minutes2) * 60 + Val(seconds2)) - (Val(hours1) * 3600 + Val(minutes1) * 60 + Val(seconds1)) >= check_time Then
    
                    gap_start = ((years1) & ":" & (days1) & ":" & (hours1) & ":" & (minutes1) & ":" & (seconds1))
    
                    gap_end = ((years2) & ":" & (days2) & ":" & (hours2) & ":" & (minutes2) & ":" & (seconds2))
    
                    gap_time = ((Val(hours2) * 3600 + Val(minutes2) * 60 + Val(seconds2)) - (Val(hours1) * 3600 + Val(minutes1) * 60 + Val(seconds1)))
    
                    hms = TimeSpan.FromSeconds(gap_time)
    
                    h = hms.Hours.ToString
    
                    m = hms.Minutes.ToString
    
                    s = hms.Seconds.ToString
    
     
    
                    If h.Length = 1 Then 'Takes gap time to hh:mm:ss format
    
                        h = "0" & h
    
                    End If
    
     
    
                    If m.Length = 1 Then
    
                        m = "0" & m
    
                    End If
    
     
    
                    If s.Length = 1 Then
    
                        s = "0" & s
    
                    End If
    
     
    
                    gap_time = h & ":" & m & ":" & s
    
                    Results.listviewload(gap_start, gap_time, gap_end) 'Sends the results to the listview in the results window
    
                    numberOfPmi = numberOfPmi + 1
    
                    MyTXTWriter.WriteLine(gap_start & vbTab & gap_time & vbTab & gap_end & vbCrLf) 'Writes the line of results to the text file
    
     
    
                End If
    
            Next
    
     
    
            MyTXTWriter.Close()
    
     
    
            If secondcheck = True And run_count = 1 Then 'Index run_count for use in a switching statement to determine the command path
    
                myfilename_temp = Split(myfilename, ".", 2)
    
                myfilename = myfilename_temp(0) & "2.txt"
    
                Results.listviewload("Second Check Results", " ", " ") ' Adds a line in the listview to seperate the results
    
                check_time = TextBox3.Text 'Loads the second check time into the variable
    
                run_count = run_count + 1
    
                GoTo 42
    
            End If
    
     
    
            Button3.Visible = True 'Turn on the Reset button if the fields are used
    
            Beep() 'Lets you know the program is done
    
     
    
            'Signal check is complete
    
            If numberOfPmi = 0 Then
    
                Label5.Visible = True
    
            Else
    
                Button5.Visible = True
    
            End If
    
     
    
    99:
    
        End Sub
    
        Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged
    
     
    
            If CheckBox1.CheckState = 1 Then 'If the checkbox is checked, turn on the second check time interface
    
                Label8.Visible = True
    
                TextBox3.Visible = True
    
                secondcheck = True
    
                Label13.Visible = True
    
            Else
    
                Label8.Visible = False
    
                TextBox3.Visible = False
    
                Label13.Visible = False
    
                secondcheck = False
    
            End If
    
     
    
        End Sub
    
        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    
            Button3.Visible = False 'Turn off the reset button until the program is rerun
    
            TextBox1.Text = "20"
    
            TextBox3.Text = "120"
    
            Fname = "C:\" 'Reset default filename
    
            Label1.Text = "C:\" 'Default
    
            CheckBox1.CheckState = 0 'Uncheck the box
    
            Label5.Visible = False
    
            Button5.Visible = False
    
            browsed = False
    
            Me.Height = 182 'Set the window size back to normal
    
        End Sub
    
        Public Sub PMIChecker_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            Me.Height = 182 'Set the window size to the smaller value on load, until results are displayed
    
        End Sub
    
     
    
        Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
    
            Results.Visible = True 'Opens the results window
    
        End Sub
    
    End Class
    
    
     
  3. Unread Apr 6, 2012 at 11:36 PM
  4. Joined:
    Nov 23, 2006
    Posts:
    6,528
    Referrals:
    15
    Location:
    Canada
    Gohan has AIDS Extreme Homosex MushyMuncher
    Blupig

    Blupig 1 big meme

    Reading in Excel data

    If this is explicitly a paid job, post in the Software Market instead.
    What you'll need to do is looking into Office interop, but the tricky part is that it's version specific - So if you use the libraries from the 2003 Office interop (I've done a fair bit of work with that) they won't work with 2007 documents, etc. You should have the libraries for it already installed since I'm pretty sure they ship with whatever version of Office you've got. The interop has been shipped with Office since Office 97 I believe, but if you go as far back as that and the 2000 suite you might be dealing with OLE/Win32 libraries. Unsure about how recently they've been using those, they could still be using them for all I know.
     
  5. Unread Apr 8, 2012 at 11:56 PM
  6. Joined:
    Jan 17, 2010
    Posts:
    871
    Referrals:
    0
    Location:
    Alabama
    fredecus

    fredecus Apprentice

    Reading in Excel data

    Not really a paid job, only if it is too difficult, I have some of it working, I just need someone to help me read data across cells, I will update with the code I have In the morning.
     
  7. Unread Apr 10, 2012 at 12:37 PM
  8. Joined:
    Nov 23, 2006
    Posts:
    6,528
    Referrals:
    15
    Location:
    Canada
    Gohan has AIDS Extreme Homosex MushyMuncher
    Blupig

    Blupig 1 big meme

    Reading in Excel data

    I learned from some good tutorials online, just search for a guide to the Office API for .NET and make sure you get the year right like I mentioned in my last post.
     
  9. Unread Apr 12, 2012 at 5:33 PM
  10. Joined:
    Jan 17, 2010
    Posts:
    871
    Referrals:
    0
    Location:
    Alabama
    fredecus

    fredecus Apprentice

    Reading in Excel data

    Updated with the code, I have it working now but I need a little more help.

    What would I do to have it print out any information that is missing when it scans through the increments?

    How would I get it to scan in information from multiple columns?

    off topic-how do I make my forum avi a .gif? lol.
     
< Simple Geometric Fractal Creator [In progress] | IRC Bot launguage? >

Users viewing this thread
1 guest