CODEWITHSUNDEEP
vbaexcelcsv
Mblankfield
Mblankfield

Reputation: 23

Verifying specific data wihin a csv using excel vba

Hoping to get a little help with a project I'm trying to improve upon. I have a function I'm using to retrieve the most recent date of a csv file (from a folder of approx. 13000 files) using two cells inside my workbook for file path and file name. It works great and will find the files I am looking for very quickly.

In order to improve this tool, I would need to look inside the csv and verify that a few numbers match another cell in my workbook. I am not sure how to tell the program where to look and how to check for a match. The numbers are in the same location everytime and will be the exact same format as the cell in my workbook. The csv does not use commas either (if that matters). For example, the numbers will be located in the 6th column and 2nd row. I want to check that with the value of my A5 cell before returning the most recent date. If it is not a match I want to check the next most recent date for a match and so on. Current function I'm using:

Function ReportTime(ByVal sESN As String, ByVal sFolder As String)
     Dim FileName As String
     Dim MostRecentFile As String
     Dim MostRecentDate As Date
     Dim oFSO As FileSystemObject

If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"

Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FolderExists(sFolder) Then
    FileName = Dir(sFolder & sESN & "*hdr.txt", 0)
    If FileName <> "" Then
        MostRecentFile = FileName
        MostRecentDate = FileDateTime(sFolder & FileName)
        Do While FileName <> ""
            If FileDateTime(sFolder & FileName) > MostRecentDate Then
                 MostRecentFile = FileName
                 MostRecentDate = FileDateTime(sFolder & FileName)
             End If
             FileName = Dir
             DoEvents
        Loop
    End If
Else
    MostRecentFile = "Err: folder not found."
End If
Set oFSO = Nothing

ReportTime = MostRecentDate
End Function

Upvotes: 0

Views: 84

Answers (1)

Rosetta
Rosetta

Reputation: 2725

Its equally easy to do so compared to what you already have. Here is a sample code. What it does is it looks through a file line by line.

Sub ReadTextFile()
    Dim fpath   As String
    Dim fline   As String
    Dim fnumb   As Long
    Dim i       As Long
    Dim Wanted  As String

    fpath = "C:\Test\Testfile.txt"
    fnumb = FreeFile
    Open fpath For Input As #fnumb
        i = 1
        Do While Not EOF(fnumb)
            Line Input #fnumb, fline
            If i = 2 Then
                Wanted = Split(fline, vbTab)(5) '<~~ here is the output
                Exit Do
            End If
            i = i + 1
        Loop
    Close #fnumb
End Sub

If you want to focus at line number 2 of the file, just catch it when i = 2

After catching text on line 2, then if your file is, say, tab delimited, you can use split(fline, vbTab)(5) to extract the content in column 6; Otherwise if your file is a fixed width file, then simply use the MID function.

After having what you needed, just exit do to end the loop and quick-end the procedure without having to go through the entire file.

hth

Upvotes: 1

Related Questions