Count Records in .csv file

[madhouse]

I have a form that allows a user to select a .csv file to import into Access. Once the data has been imported into Access I then have a few functions to format the data. While the functions are running, there is a progress bar on the form so that the user can see how far the data processing has completed.

Now what I also want to do is have the progress bar running while the data is being imported from the .csv file, but to do this I first need to count how many records there are to be imported. Does anybody know a way of counting the number of records in a .csv file?

 

[Louiegee]

Three ideas come to mind quickly:
1) you would have to write the following function
public function countrecs(FileName as string)
dim cnt%,l$
open FileName for input as #1
cnt=0
do until eof(1)
line input #1,l
cnt=cnt+1
loop
close 1
countrecs=cnt
end function
This isn't so bad if the file isn't that big.
orrrrr

2) You can also get the filesize from the DOS and count bytes instead of records. It's even more complicated though. If the first way takes too much time then this may be better. Shell to DOS and do a "dir>somefilename". This puts the directory info into a text file. Then you'd have to open and parse that file to get the filesize of the file you want.
orrrr
3) You can use (depending on your OS) one of Windows' common dialog boxes, (I forget which one) to get the filesize but I don't know if you can get the number of records.


There may be better quicker ways, but I don't know them.

LouieGee

 

[MichaelRed]

see the following procedure:

Public Function basGrabFile(FilIn As String) As String

    'Michael Red    3/3/2003
    'Sample Usage:  ? basGrabFile("C:\MsAccess\DrawArcsInVB.Txt")
    'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
     and the entire text is returned to the caller [prog | procedure]

    Dim MyFil As Integer

    Dim MyTxt As String
    Dim MyPrts() As String
    Dim MyPrtRec() As String

    'Just grab the Stuff
    MyFil = FreeFile

    Open FilIn For Binary As #MyFil

    MyTxt = String(LOF(MyFil), " ")
    Get #MyFil, 1, MyTxt

    Close #MyFil

    basGrabFile = MyTxt

End Function

In another procedure, call [basGrabFile], asigning the return to a SIMPLE string. See the spllit function. Declare a variant or a string array, and use split to asign the return of the split function the the variant (or string array). The UBound of the variant (or string array) Plus one is the number of lines.


a file named "Fleas.Txt"

My Dog has Fleas
Your Dog has Fleas
His Dog has Fleas
All Dogs have Fleas
This Cat Has Fleas
That Cat Has Fleas
Those Cats Have Fleas
Who Doesn't Have Fleas
I Don't Have Fleas

A sample / test program to illustrate the procedure

Public Function basTestGrabFile(FileIn As String) As Long

    'Example Usage
    '? basTestGrabFile("C:\My Documents\Fleas.Txt")
    '9

    Dim MyBigStr As String
    Dim MyLines() As String

    MyBigStr = basGrabFile(FileIn)
    MyLines = basSplit(MyBigStr, vbNewLine)

    basTestGrabFile = UBound(MyLines) + 1
    
End Function

p.s. don't be concerned by the "basSpllit" function in hte test / sample routine. I have @@$)(*&#)*&^($#^(#^ SOME reference and so used an old version of split I generated fot Ms. A. '97

MichaelRed
m.red@att.net

Searching for employment in all the wrong places