VBA question

Discussion in 'Trading Software' started by just_dabbling, Apr 2, 2003.

  1. OK, don't laugh :D I know this is probably easy for some of ya.
    I'm trying to read in a large text file line by line, break it into variables and then do some filtering etc.
    The file is space delimited. I do smth like this:
    Open "c:\CrspVBA.txt" For Input As #1
    Do While Not EOF(1)
    'Read one line and then break it into variables
    Input #1, PERMCO, PERMNO, NCUSIP, SICL, Ticker, Symbol, Prc, Ret, Shr, Numtrd, Vol, Facpr, Cap, Caldt
    ...
    ...

    The first two variables are numeric, then I have the CUSIP number, which has both, numbers and letters in it, then the SIC code, then the ticker, another version of the ticker, the rest is numeric. The text fields don't have quotation marks around them.
    Previously, my files were fixed and it was easy to break down the line into variables by specifying the position, lengh and using text functions. This one is space delimited and I'm having trouble reading it in.
    When it reads that line, it takes the first two variables OK and assigns them. Then it takes the rest of the line and assigns it to NCUSIP (with spaces and all).
    The rest gets messed up.
    Thanks a lot in advance.
     
  2. nkhoi

    nkhoi Moderator

    have you try first import it into excel with space delimited then export it as txt with comma or anything you choose.
     
  3. Here's some code I had written a couple of years ago to read a delimited file in VB. I read an entire line at a time and parse out the fields. Note that it is indented properly but I couldn't get it to post as such.


    Private Function ReadFile()

    Dim fnum As Integer
    Dim LineStr As String

    Dim Symbol As String
    Dim Exchange As String
    Dim last As Single
    Dim prevclose As Single
    Dim Volume As Long

    fnum = FreeFile()
    Open "c:\xxx.txt" For Input Access Read Shared As #fnum

    Line Input #fnum, LineStr

    Do While Not EOF(fnum)

    ' fill in your fields and field numbers here
    Symbol = GetField(1, LineStr)
    Exchange = GetField(5, LineStr)
    last = Val(GetField(30, LineStr))
    prevclose = Val(GetField(29, LineStr))
    Volume = Val(GetField(35, LineStr))

    DoEvents
    Line Input #fnum, LineStr
    Loop

    Close #fnum

    End Function

    Private Function GetField(ByVal FieldNum As Integer, ByVal LineStr As String) As String

    ' this function returns a field from a space delimited string based on the field number

    Dim pos As Integer
    Dim i As Integer

    pos = 1

    For i = 1 To FieldNum - 1
    pos = InStr(pos, LineStr, " ")
    If pos = 0 Then
    GetField = ""
    Exit Function
    Else
    pos = pos + 1
    End If
    Next

    If InStr(pos, LineStr, " ") = 0 Then
    GetField = Right$(LineStr, Len(LineStr) - pos + 1) ' last field
    Else
    GetField = Mid$(LineStr, pos, InStr(pos, LineStr, " ") - pos)
    End If

    GetField = Trim$(GetField)

    End Function
     
  4. It's about 800Mb in size excel doesn't have that many rows. I can import it into some other package and export it with another delimiter, I have just tried tab delimited. It does the same thing.
    I think it's because once it encounters a text field, it treats this variable as text and not knowing where the end of it is (without quotation marks) just gives it whatever is left in the line. I'm not an expert but that's what I gather from it.... :(
    Thanks though.
     
  5. Thanks richtrader! It's actually very similar to what I thought I'd end up having to do, basically reading character by character to see where the spaces are. I was hoping there was an easier way :D I guess not. Thanks a bunch!
     
  6. Fortunately, you're not going completely character by character, but rather you jump from delimiter to delimiter by using the InStr function. It's pretty fast. For a file of your size, you can always do a quick read through the file so you can display a status message, like this:



    Dim reccount As Long
    Dim CurrRec As Long

    On Error Resume Next

    ' get number of records so we can display status
    fnum = FreeFile()
    Open "c:\xxx.txt" For Input Access Read Shared As #fnum

    If Err.Number > 0 Then
    MsgBox "error! " & Err.Number & " " & Err.Description & vbCrLf & fname
    Exit Function
    End If

    Do While Not EOF(fnum)
    Line Input #fnum, LineStr
    reccount = reccount + 1
    Loop

    Me.Refresh

    Close #fnum


    and then you can increment a variable after each processed record and use this variable to display a message:


    CurrRec = 0

    Do While ....

    ... code from above ...

    CurrRec = CurrRec + 1

    If CurrRec Mod 500 = 0 Then
    lblStatus.Caption = CurrRec & " of " & reccount & " Records processed..."
    End If
    ...
    DoEvents
    Loop


    Good luck!
     
  7. I'm more used to VB, but I assume you can do the same in VBA. Use the filesystemobject and the Split function. It's tailor-made to do just want you want to do.
    For more advanced text handling functions, look into Regular Expressions - they were spawned out of the unix world and can do just about anything you want.
     
  8. mauzj

    mauzj

    just_dabbling,

    Depending on what version of VBA you're using, you may be able to split each line into an array with the split function.

    The syntax is something like split(<line>,<delimitter>)

    Mauzj.
     
  9. Thanks a lot guys!
    This thread has been so helpful and is another proof to me of what a great resource ET is!
    Good Luck to all of you.