There's got to be an easier way

Ok so we have a vb app (version 5) running in background on a server, every so often it checks a temp directory to see if a csv file has been downloaded from a unix box.

If it detects the file, opens it up for input, and reads line by line. Now here is the question

A typical line would be something like:

"Name","Sales","Projected"

We are currently using InStr and Mid to extract the individual data fields.

Is there an easier way??????????

Will be modifying the app this weekend, (feature creep strikes), to add zip and archiving to it and would like to be able to hack some lines out of it.

Thanks in Advance.

[Gen-X]

There is a function in VB called split

It takes a string and splits it into seperate elements of an array.

But you have to watch out... if you use the delimiter inside your actual data (ie "Fred, was here") then it will think you have another element when you do not.

Thanks Gen,

Is split a function in vb5? Will have a look at the help files. No we shouldn't have the dreaded "," in the data field as we haven't hit the problem so far, and the mini guys tell me they specifically code it out.

[Paul282]

Sure is !!

Use the Split function.

Actually. use it twice for better performance,

Load the text file into a big string (which is faster than line-by-line) use split on vbCrLf to give you an array of lines (strings) then loop split again on the comma to get the text.

you may need a replace() to get rid of the " chr. but looping splits and replace functions is FAST.

I'm sure you can find some combination thereof to solve your problem.

I send a lot of time on text parsers too

[Serge]

Unfortunately, Split function exists only in VB6.

Thanks Serge,

Guess l will leave that part of the app alone, and just add the zip control.

[Paul282]

Unfortunately, Split function exists only in VB6.

Write you're own Function (control or Class) which does the same thing, you're bound to need it again.

Sub Split(STRs() as string, StrToSplit as String, Splitter as String)

Then redim the ref of the array, slice the file with the mid() of the splitter then dump it back in the array.

Then the only difference is that you pass the array reference with the function. (You're not passing the array data itself so it'll be fast)

[Gen-X]

If you are going to write your own class to do this you might want to consider doing it as a collection rather than as an array.

The main reason for this is that you don't have to "re-dim" which occasionally has the habbit of causing memory leaks and you end up with a Collection (or a linked list which is how collections are done) that allows you the following benefits :

- You can tell how many lines using collection.count
(Yes I know you can do UBound(array) but its slower )
- You can add new items dynamically if you want
- You can remove items in the MIDDLE without effecting others
- If you give it a key when adding lines you can reference it by more than just the line number

On that last one... Imagine the following :

Here is your file:

"Fred","10 King Street","Walkerville"
"John","17 Henry Road","KentTown"
"Sam","99 Maple Court","Anywhere Town"

You know that this means "Name", "Street", "Suburb"

Now the collection is created like this :

Collection.Add "Fred", "Name"
Collection.Add "10 King Street", "Street"
Collection.Add "Walkerville", "Suburb"

This means that when you have split the line up and returned it into your collection you can do something like this :

Dim colFields as Collection
Dim sLine as String

sLine = <Read Next Line>
Do While Not End of File
  NewSplit(colFields, sLine)

  MsgBox "Name is : " & colFields("Name")
  sLine = <Read Next Line>
Loop

This would return 3 message boxes containing "Fred", "John" and "Sam" and allows you some very nice features.

Hmmm...interesting reply. The code is already a module and works. I was just wandering if there was a function l had missed which would cut down the code length

kind of like

vData = Field(Dataline,",",1) which we would use in Universe.

Split sounds like the ideal solution...but...we have authority to purchase vb7 on release, (already we have teams of commandos outside MS HQ waiting to ambush the first shipments), but not vb6.

Still adding the zipping direct to the app will be pretty cool.

[Paul282]

What happens then If I make an activeX control which does nothing but pass to a split function ???????

If you install them will it work?

I suppose it might package the VB6 runtimes, but that is what you want isn't it?

---------------------------------------

But I disagree with the idea of using collections. What you want here is somthing small and fast that does the job, you don't need the rest of the .ADD stuff and if there is a memory leak in the redim statement then it'll fix when the sub goes out of focus or you can redim again to 0 at the end of the sub.

Collections and object bases linked lists have their advantages but I think I could get better performance (admittedly with less functionality) from arrays in this case.

[Gen-X]

Arrays are notorious for memory leaks that transend the procedures they are dimmed in and it is for that reason they occur... because when it leaves the procedure it doesn't reclaim its memory properly. You run the application and it works fine... you run it several times and suddenly it starts having problems... very hard to track and very hard to locate.

As for Better Performance I am not so sure.

It takes longer to do :

Public Enum Fields
  fName = 0
  fStreet = 1
  fSuburb = 2
End Enum

cmbFields.Add "Name"
cmbFields.Add "Street"
cmbField.Add "Suburb"

Split(Input,Array)

For i = 1 to UBound(Array)
  DoSomething Array(i)
Next i

MsgBox Array(fName)

Sub cmbFields_Click
  Select Case cmbFields
    Case "Name"
      Set Field = Array(fName)
    Case "Street"
      Set Field = Array(fStreet)
    Case "Suburb"
      Set Field = Array(fSuburb)
  End Select
End Sub

Than it takes to do :

cmbFields.Add "Name"
cmbFields.Add "Street"
cmbField.Add "Suburb"

Split(Input, Collection)

For each i in Collection
  DoSomething Collection(i)
Next i

MsgBox Collection("Name")

Sub cmbFields_Click
  Set Field = Collection(cmbFields)
End Sub

As you can see in the first one every time I want to select the field I either have to look up the element number or I have to convert the textual field name into that number.

With the second one its readily available from the moment its created.

This is the WHOLE reason why recordsets are created to use the Fields collection < rstRecords("FieldName") > instead of trying to directly use the Element of a supposed array < rstRecords(fName) > where you have to pre-determine what it means.

Something you have to keep in mind is that performance over functionality makes the code redundant... because the moment they want to do anything MORE with it you have to throw it away and start again.

I would take a bit extra Functionality over a bit extra Performance any day

[Edited by Gen-X on 04-14-2000 at 01:48 AM]

[Paul282]

I found that ADDing to collectios is slow if data sizes are large.

The the reason I sacrifice functionality here is because I just want to make a fast SPLIT function and nothing else. I wasn't speaking generally.

I think he'd be better off with VB6

[Greg!]

This maybe far too simple but......

Dim x, y, z as String

'Open the File
Open "myFileName" For Input As #100
'Start the Loop
Do While Not EOF(100)
'Read in the first item into x, second into y etc...
Input #100, x, y, z
Msgbox x & " " & y & " " z
Loop

This works for comma delimited files

Greg!

This maybe far too simple but......

Dim x, y, z as String

Dim x, y, z as String =
Dim x (variant)
Dim y (variant)
Dim z as string

x and y are variants
This is a common mistake, use:
Dim x as string, y as string, z as string to make all of them strings.

Hello Jethro,

Here is a little snipit of code that I use for reading comma delimited files:

Public Sub LoadData()

Dim I As Integer, iFileNum As Integer
Dim sFileName As String

' MyData is a UDT

sFileName = App.Path & "\data.dat" ' set the file name up

iFileNum = FreeFile ' get a free file number
Open sFileName For Input As iFileNum ' Open the file.

Do While Not EOF(iFileNum) ' Loop until end of file.

Input #iFileNum, MyData(I).sName, MyData(I).sStreet, MyData(I).sCity, MyData(I).sZip ' read in the data from line each line in the file
I = I + 1 ' increment my place holder
ReDim Preserve MyData(I)
Loop

' check and remove the last blank element
If MyData(UBound(MyData)).sName = "" Then
ReDim Preserve MyData(UBound(MyData) - 1)
End If

Close iFileNum ' close the file

End Sub

Hope this helps a little.

Roger

[MrShickadance]

Greg! is correct for CSV file's there is no reason for reading in a line at a time when Input will parse for you.

Greg! needs to check how he defines variables, but other than that, all is cool. =)

[Edneeis]

Like everyone else didn't answer your question, but. The Tip of the day at the dev-x site was the Split function for VB4 and 5 so if you are like me and don't like whats been done for me then visit this site:
http://www.vb-zone.com/registered/tip.asp

Then you can implement the Split function with a little cut and paste. Good luck.

Just in case the link didn't work here is the code:
Public Function Split(aText As String, _
Optional vSrch As Variant) As Variant
If IsMissing(vSrch) Then vSrch = " "
Dim j As Long, k As Long, a As String
ReDim s(0) As String
a = aText
k = InStr(a, vSrch)
Do While k
If j > UBound(s) Then
ReDim Preserve s(0 To j) As String
End If
s(j) = Left$(a, k - 1)
a = Mid$(a, k + Len(vSrch))
k = InStr(a, vSrch)
j = j + 1
Loop
If Len(a) Then
If j > UBound(s) Then
ReDim Preserve s(0 To j) As String
End If
s(j) = a
End If
Split = s
End Function
Dim vDat As Variant
vDat = Split("This is a test")
' vDat(0) = "This" vDat(1) = "is", etc...


Matt Hart
Tulsa, Oklahoma

Thanks for all the replies, there was some good stuff there. Serge was right no split function in vb5

Had a look over the weekend, and managed to cut down the code we already use, but still seems overly complex for what is a simple operation.

Basically the problem we have is that the download file can be of variable length, with a variable number of fields. I was just hoping there would be a function in vb5 which would in a couple of lines replace our code which is in about thirty lines, (including error handling and data format checks).

Oh well at least it works