Comma separated txt files

Hello,
I have a comma separated txt file format as follows, point#,Northing, Easting,Elevation, Description. I am trying to populate a listbox with only the point numbers like so;
point 100
point 101
point 102
etc

I know how to bring in the entire txt file, but how do I tell it to only use the point # then skip to the next line. Also, what if I wanted to use the elevations...how do I skip over commas until I reach the elevation data?

I am a novice with VBA at best so please don't shoot answers over my head without explaining.

Thanks in advance!
Eugh

 

You can use the Split function to separate the lines in arrays of strings
using the comma as separator, this way you will obtain something like this:

Dim VarData as Variant
Const InputTXTLine as string = "100,1,200,Description" 'This is an example.
VarData = Split(InputTXTLine,",")

'In VarData you will have an array:
'Vardata(0) = 100
'Vardata(1) = 1
'Vardata(2) = 200
'Vardata(3) = "Description"

Hope this helps ;-)

Easting,Elevation, Description. I am trying to populate a listbox with only
the point numbers like so;

use the point # then skip to the next line. Also, what if I wanted to use
the elevations...how do I skip over commas until I reach the elevation data?

 

Hi eugh

I'm working on something similar at the moment.

The disadvantage of using the split command in the above example appears to
be that the values aren't stored, so if you'd wanted the Northings of, say,
the 16th point in your list you'd have to sift through the file again to get
it.

I created a user-define Type variable to store all the values (have a look
in the VB Help):

The disadvantage of this way is you have to have a guess as to the number of
lines in the file (to make sure you have enough room) when Diming the
variable (see >>).

Does anuone know a way around this?

TIA

Dave F.

Type LayerAttr
LayNme As String
LayCol As String
LayLtp As String
End Type

Sub UserForm_Initialize()
Dim acLayer As AcadLayer
Dim FileNo As Integer
Dim Cnt As IntegerDim Lay As Variant

For Each acLayer In ThisDrawing.Layers ' places all layer names in
listbox
LBxDwgLayLst.AddItem acLayer.Name
Next ' acLayer
FileNo = FreeFile
Open "c:\dwgs\fx15\fxlayersCSV.txt" For Input As FileNo
Do While Not EOF(FileNo)
Input #FileNo, LayLst(Cnt).LayNme, LayLst(Cnt).LayCol,
LayLst(Cnt).LayLtp
LBxTxtLayLst.AddItem LayLst(Cnt).LayNme
Cnt = Cnt + 1
Loop ' eof
Close FileNo
End Sub

 

I can think of thee quickly off the top of my head. You can dynamically
redim your array to hold the number of items added to it in your loop; or
you can code up, or find somewhere, a list like data structure that will
dynamically resize itself to hold a variable number of UDT's; or you can
change from a UDT to a class and store instances of the class in a VB
collection.

I'm sure that there are others...

 

Thanks for replying

I'm not sure if you can do that. To be able to redim I have to have empty
brackets: Dim LayLst() As LayerAttr.
& I do not know the total number until it exited the loop

When I run I get a Subscript out of range error.


; or

I'm sorry, but I have no idea what your saying

Cheers
Dave F.

 

See comments inline below

You have to redim the array in each iteration of the loop and utilize the
preserve keyword to keep the existing data. Of the three, this is my least
favorite.

Instead of creating a UDT via the Type keyword, create a class module to
hold your data. Place the code below into a class module and name it
LayerAttr (As a side note, I prefer the name LayerInfo for this class).
I've only shown the property for Name, you'll need to add others for Color
and LineType.

'LayerAttr class module
Option Explicit

Private m_Name As String
Private m_Color As String
Private m_LineType As String


Public Property Get Name() As String
Name = m_Name
End Property

Public Property Let Name(newName As String)
m_Name = newName
End Property


Then in your form initialization you can create instances of the LayerAttr
class, fill them with data, and save them to a collection object.

Sub UserForm_Initialize()
<snip>

'Create a variable to hold LayerAttr objects
Dim la As LayerAttr

'Create a dictionary to hold multiple LayerAttr objects
Dim layerInfoList As Collection
Set layerInfoList = New Collection

'Retrieve the layer information from the file and save it
Do While Not EOF(FileNo)
'Create a new LayerAttr object and fill it with data
Set la = New LayerAttr
Input #FileNo, la.Name, la.Color, la.LineType

'Store the new LayerAttr object in the Collection,
'This code assumes that layer names are not repeated!!!!
layerInfoList.Add la, la.Name

Set la = Nothing
Loop ' eof

'Add names to the listbox
For Each la In layerInfoList
LBxTxtLayLst.AddItem la.Name
Next la

<snip>
End Sub

If you declare the layerInfoList as a private member variable, similar to
the m_Color var. in the LayerAttr class, then all procedures in your form
will have access to the list. This is my second favorite of the three
ideas.

I'm on my way to the airport, so option #3 will have to wait <g>

 

Have a good flight

Thanks, I'll have a look a that.

Another option I want to look at is using scripting dictionaries.
Have you ever used them instead of arrays?

I'm not sure if they could be used instead of multi-dimensional arrays, as
in my case?

Mike Tuersley recommends them:
http://groups.google.co.uk/groups?q...ulx61qv4.15yh7kha9d80k$&rnum=1

Oh! dear, what out for word wraps.

Thanks
Dave F.

 

Thanks. The flight went well...after we got off the ground 4 hours late.
It was the wee hours of the morning before I finally crawled into the hotel
bed. If I start rambling incoherently, you'll know why <g>

Arrays have their place but more often than not in a situation like you are
talking about a Collection object ,or Scripting Dictionary, or a custom data
structure is almost always the better way to go. Which to use is up to you,
your experience level, and the specifications of the project. Collections
are easy because they are built into VB. Scripting Dictionaries aren't as
easy only because they aren't built into VB, but they do have advantages
over Collections such as speed and more built in functionality. Custom
built data structures are the most difficult, until you have a base library
built, but offer the most bang.

If you want my recommendation, which is solely based on the very few details
that we've shared in this brief discussion, I'd suggest that you look into
the Scripting Dictionary for this project. The code to use it is nearly
identical to what I've shown you using the Collection object.

Is it nap time yet???

 

dim pntno as string
dim north as string
dim east as string
dim elev as string
dim desc as string

while not eof(filenumber)
input #filnumber, pntno, north, east, elev, desc
listbox.additem "Point " & pntno
loop
stay away from the line input statement that will require you to parse out the individual items in each record.

Tom

 

Bobby,

Thanks for explaining all this.
I'm sure I'm not the only one here that's benefiting.

I haven't had time to put the code into practise yet, so appologies if I get
this wrong:

I can see how I can substitute a dictionary for a collection, but I'm
failing to comprehend is how/where it
would store the extra info (colour/ltype). The example appears to only store
the Layer name & discarding the rest.

Is this correct, or am I missing something?

TIA
Dave F.

 

Hi Dave,
You didn't get it wrong. The example that I showed only had a property for
storing the Name. Here is the complete class module with the additional
properties for color and linetype:

'LayerAttr class module
Option Explicit

Private m_Name As String
Private m_Color As String
Private m_LineType As String


Public Property Get Name() As String
Name = m_Name
End Property

Public Property Let Name(newName As String)
m_Name = newName
End Property


Public Property Get Color() As String
Color = m_Color
End Property

Public Property Let Color(newColor As String)
m_Color = newColor
End Property


Public Property Get LineType() As String
LineType = m_LineType
End Property

Public Property Let LineType(newLineType As String)
m_LineType = newLineType
End Property


HTH

 

Thanks again
Unfortunately i still don't get it :-(

Plese see >>.

Thanksin advance

Do While Not EOF(FileNo)
'Create a new LayerAttr object and fill it with data
Set la = New LayerAttr
Input #FileNo, la.Name, la.Color, la.LineType

'Store the new LayerAttr object in the Collection,
'This code assumes that layer names are not repeated!!!!to store all 3 attributes of la - is that possible?)....
layerInfoList.Add la, la.Name
multi-dimensional?)
Set la = Nothing
Loop ' eof

'Add names to the listbox
For Each la In layerInfoList
LBxTxtLayLst.AddItem la.Name
Next la

 

layerInfoList.Add la, la.Name

This doesn't just store the name, it has two arguments la and la.Name. The first stores the entire object with all its properties. The second arg is what you want as an index value for the collection. By doing so, you can retrieve the object from the collection by calling its name in the Item method rather than merely an index number.

Dim MyLA As LayerAtt
Set MyLA = layerInfoList.Item(0)
'or
Set MyLA = layerInfoList.Item("LayerName")

 

Hi Dave,
Ed is correct. All of the information is being stored. Maybe a comparison
to what your original code was doing will help.

Your original code was creating an array of 80 LayerAttr user defined types.
You opened the file and filled some of the LayerAttr types with data from
the file and left others untouched, although they were still residing in
memory. You could then access this retrieved data by going to an index of
the array and accessing the fields of the LayerAttr type at that index.

MsgBox ("The name of the first layer in the file is " & LayLst(0).LayNme)

All that I have done is to replace the LayerAttr user defined type with a
class module that has the exact same functionality as the user defined type.
You could just as easily fill your array with instances of the class and
access the data stored within the class the same way that you did with the
user defined types. (Note: The only reason that I'm using a class and not
a UDT is that UDT's can't be stored in Collection objects.)

MsgBox ("The name of the first layer in the file is " & LayLst(0).Name)

However instead of using an array with a static size that you set when
creating the array, we are using a Collection, or Scripting Dictionary,
which is in theory a dynamic array that resizes itself to hold however much
data you put in it. If you only read in three layers from the file then the
Collection only has three LayerAttr objects in it. To do this you have to
manually create instances of the class and add them to the Collection. This
is unlike the array that automatically fills itself with LayerAttr objects
when it is created.

You can look back over my example code to see this object creation, saving
the data to the LayerAttr instance, and then saving each LayerAttr to the
Collection cycle inside of the loop that is reading the file.

Notice that after the loop is complete and all of the information has been
read from the file, the code then goes back and iterates over the
layerInfoList Collection, grabs each LayerAttr object stored there, and then
places the information stored in the Name property into the listbox on the
form.

I hope that makes sense. It's late in the day and my brain is fried and I
can't tell if it does or not <g>

 

Ah! The penny's dropped.

I was being confused by this from the VBscript help file:
Dim d ' Create a variable.
Set d = CreateObject("Scripting.Dictionary")
d.Add "a", "Athens" ' Add some keys and items.
d.Add "b", "Belgrade"
d.Add "c", "Cairo"From that I was assuming (not unreasonably, I think), that
the key could only be used as an index.Thanks to Ed, & especially Bobby for

 

Indeed, the penny has dropped. I see the source of your confusion now. In
my example I used a Collection object and you were referencing the Scripting
Dictionary help. You see, the Item and Key arguments are reversed for these
two objects. At one time I had vowed to use named arguments...I think that
I'll renew that vow <g> Have a good one Dave!

 

Hi
This is what I've got so far & I'm sure (hope!) I'm nearly there.
..
The >> line is highlighting La.Name & giving the error:

Variable required - can't assign to this expression.

I've tested La.Name by putting a value into it & I've retrieved it
successfully, so the Class appears to be working OK.

I've looked in the help & this seems to be the problem, but I don't know how
to solve it:

"You used a function call or an expression as an argument to Input #, Let,
Get, or Put. For example, you may have used an argument that appears to be a
valid reference to an array variable, but instead is a call to a function of
the same name".

I've checked for functions called La - there aren't any.

Any ideas?

TIA
Dave F.

Sub UserForm_Initialize()
Dim FileNo As Integer
Dim La As LayerAttr
Dim LayerInfoList As Collection

Set LayerInfoList = New Collection
FileNo = FreeFile

Open "c:\dwgs\fx15\fxlayersCSV.txt" For Input As FileNo

Do While Not EOF(FileNo)
Set La = New LayerAttrLayerInfoList.Add La, La.Name
Set La = Nothing
Loop ' eof

Close FileNo
End Sub
------------------------
This is the Class module
------------------------
Option Explicit

Private m_Name As String
Private m_Color As String
Private m_LineType As String

Public Property Get Name() As String
Name = m_Name
End Property

Public Property Let Name(newName As String)
m_Name = newName
End Property

Public Property Get Color() As String
Color = m_Color
End Property

Public Property Let Color(newColor As String)
m_Color = newColor
End Property

Public Property Get LineType() As String
LineType = m_LineType
End Property

Public Property Let LineType(newLineType As String)
m_LineType = newLineType
End Property