Read particular column in text file

[MDA]

Hi all,

I have comma separated text file with a comma separated header on the first line and several hundred lines of data below. I need to search for one particular column header for instance, "CITY." Then I need to check every row for that particular column and verify the data in that column.

If the data in that column fails, I want to return the entire line as an error to the user.

I know the general idea of reading files, but not sure how to seek a particular column by looking at the header row.

Any ideas or samples are greatly appreciated!

Regards,

MDA

 

[ADoozer]

ok, here is some really rough code, will need some error checking and a little refinement but it should do the job!

Option Compare Text
Option Explicit

Private Sub Command1_Click()
    GetColumn "CITY", "c:/temp.txt"
End Sub

Private Function GetColumn(myHeader As String, myFileName As String)
    
    Dim ff1 As Integer
    Dim TempData As String
    Dim TempArray() As String
    Dim i As Integer
    Dim myColumn As Integer
    
    ff1 = FreeFile
    Open myFileName For Input As #ff1
    
    'get header line
    Line Input #ff1, TempData
    TempArray = Split(TempData, ",")
    For i = 0 To UBound(TempArray)
        If TempArray(i) = myHeader Then
            myColumn = i
            Exit For
        End If
    Next i
    
    'get line info
    While Not EOF(ff1)
        Line Input #ff1, TempData
        TempArray = Split(TempData, ",")
        If Not ValidateMe(TempArray(myColumn)) Then
            MsgBox "error occured:" & TempData
        End If
    Wend
    
    Close #ff1
    
End Function

Private Function ValidateMe(myCell As String) As Boolean
    
    If myCell = "London" Then
        ValidateMe = False
        Exit Function
    End If
    
    ValidateMe = True
    
End Function

good luck!


If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[johnwm]

Sounds like an ideal candidate for opening the file as a csv database and using a query.


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[MDA]

Thanks guys,

ADoozer your solutiuon did the trick... I just get an error "Subscript out of range." But that is just a little issue.

Thanks again for your help.

MDA

 

[ADoozer]

no worries.... like i said needs a bit of error checking...

johnwms idea of using csv's might perform a lot quicker, however youll have to figure the code for that yurself... its 1.50am here and im tired!!

good luck!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[sankalpa]

MDA says "I just get an error "Subscript out of range." But that is just a little issue."

I get the same error and a web search has found no answer so I shall put a question up about it.

(Perhaps I should have used the "Write your own .. FAQ" link above?)

 

[ADoozer]

i imagine (and i cant be sure) that you are getting the error on this part

If Not ValidateMe(TempArray(myColumn)) Then
MsgBox "error occured:" & TempData
End If

possibly due to not all "columns" having data in them (or missing commas) and myColumn being a bigger number than the size of the array, hence the need for some form of error checking!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[MDA]

You are correct... I have syntax for the start and end of the file.

i.e.
@HDR:CITY,STATE,ZIP,...

and the last line is:
@END:
I can get around this,no problem. The new challenge is that I actually have to do this with VBscript! Seems these functions dont all work with VBscript. I have seen the light though, so thanks for all your input!

MDA