VBA question

OK, don't laugh 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.

 

have you try first import it into excel with space delimited then export it as txt with comma or anything you choose.

 

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

 

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.

 

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 I guess not. Thanks a bunch!

 

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!

 

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.

 

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.

 

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.