Solved

opening / Reading files using directorylistbox and filelistbox?

Posted on 2001-06-27
23
243 Views
Last Modified: 2010-05-02
I currently have a form that contains a directorylistbox, a filelistbox and a command button. I would like the command button to open / read the chosen file. This file would be a comma seperated file. I would then like to sort the data etc, and save different instanes of it with different names. I know how to do the last bit, but im not sure how to open / read the file, and how to distinguish what fields are what, using comma seperation.
0
Comment
Question by:vibale
  • 11
  • 11
23 Comments
 
LVL 3

Expert Comment

by:jrspano
ID: 6231183
get you path and file name then


dim holder as variant
Open "path\MyFile1" For Input As #1
Line Input #1,sLine1 'this gets the first line
holder = split(sline1,"'")
close #1
'holder is an array of the line now
example
if you have a,b,ddd then holder is
holder(0) is a, (1) is b, (2) is ddd etc
you then do what you need with it.

another way is to loop throught the line looking for the "," though the split is cleaner.  make a do loop around the line input and holder to get all the lines

do unit eof(#1)

loop
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6231188
that eof(#1) should have been eof(1)
0
 

Author Comment

by:vibale
ID: 6231594
Sorry, I'm Very new to Programming, and I dont seem to understand. This is what my command button does at the moment.

pathtofile = File1.Path & "\" & File1.FileName
Dim holder As Variant
Open pathtofile For Input As #1
Line Input #1, sline1
holder = Split(sline1, "'")
Close #1

I know that more code needs to go in there, but im not sure how. Now it seems to open the file, i need it refer to 1st field as something , the second field as something else and also process each line at a time. My guess is that it will use the do until...Loop command.
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6231684
yes you need the do loop command.  You can't refer to delimited text docs as "somethings"  You have to pull it line by line and do something with each line.  You  could use ADO to make a record set and then load it from the text file.  what are you going to do with the info in the file?


here is your example changed a little

pathtofile = File1.Path & "\" & File1.FileName
Dim holder As Variant
Open pathtofile For Input As #1
do until eof(1)
  Line Input #1, sline1
  holder = Split(sline1, "'")
  'do something with holder here.
loop
Close #1
this will read the file line by line and do what ever you want to on each line.  if you want to use the file like a database let me know and I'll give you a quick example on ADO.
0
 

Author Comment

by:vibale
ID: 6231715
Thats exactly what I want it to do, act like a database. It will then get the first part of the text, strip it, and somebits in, do something with the text after the comma, and then save a file, using the first part of text as the filename. I.E If there were 100 lines in the text file, then the program would generate 100 files (Probably). Although I might change this.
0
 

Author Comment

by:vibale
ID: 6231720
The most important thing is knowing how to read the file, and how to use the data within it.
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6231829
just reading the data and using it like a db is easy.  it is VERY hard to get the updated data back in to the same file though.  you don't know where each string came from.  if you want to make a record set out of the file here's how

set a reference to ms Active x data objects 2.5 or 2.6 if you have it

in code 3 column example
txt file
a,aa,aaa
b,bb,bbb
c,cc,ccc

dim rs as new adodb.recordset
with rs
.Fields.Append "FirstField", adChar, 36, adFldIsNullable
.Fields.Append "SecondField", adChar, 8, dFldIsNullable
.Fields.Append "ThirdField", adChar, 20, adFldIsNullable
'1st is a character field of 36 chars, 2nd is 8 chars, 3rd is 20 chars all allow nulls
end with

pathtofile = File1.Path & "\" & File1.FileName
Dim holder As Variant
Open pathtofile For Input As #1
do until eof(1)
 Line Input #1, sline1
 holder = Split(sline1, "'")
 rs.addnew
 rs!firstField = cstr(Holder(0))
 rs!secondfield = cstr(Holder(1))
 rs!thirdfield = cstr(Holder(2))
 rs.update
loop
Close #1

now you have a recordset of the data and can use it

ex
dim s as string

rs.movefirst
s = rs!thirdfield 's = aaa
rs.movenext
s = rs!secondfield 's = bb
rs.movenext
s = rs!thirdfield 's = ccc

rs.close 'close the recordset

there are other ways i believe to get the txt file straight into the recordset using ado 2.6 but you will have to look in to that if you want to persue that route.  I don't know how I haven't needed that.
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6231847
also i left out the .open right above the end with. oops
should be
with rs
.Fields.Append "FirstField", adChar, 36, adFldIsNullable
.Fields.Append "SecondField", adChar, 8, dFldIsNullable
.Fields.Append "ThirdField", adChar, 20, adFldIsNullable
.open
'1st is a character field of 36 chars, 2nd is 8 chars, 3rd is 20 chars all allow nulls

end with
0
 

Author Comment

by:vibale
ID: 6231883
Im not sure what you mean by;

set a reference to ms Active x data objects 2.5 or 2.6 if you have it

in code 3 column example
txt file
a,aa,aaa
b,bb,bbb
c,cc,ccc

The rest of it I have done, but when I run it, I get an error, which I think is related to me not doing what the above says. What do I do with the above?
0
 

Author Comment

by:vibale
ID: 6231884
Im not sure what you mean by;

set a reference to ms Active x data objects 2.5 or 2.6 if you have it

in code 3 column example
txt file
a,aa,aaa
b,bb,bbb
c,cc,ccc

The rest of it I have done, but when I run it, I get an error, which I think is related to me not doing what the above says. What do I do with the above?
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6232060

set a reference to ms Active x data objects 2.5 or 2.6 if you have it
goto project menu then references.  check the reference to microsoft activex recordset object 2.6(or what ever version you have)  if you don't have at least 2.5 you should get the upgrade from ms.


'this is used just as an example below in above comment. if you have a txt file with the following in it.
in code 3 column example
txt file
a,aa,aaa
b,bb,bbb
c,cc,ccc
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:vibale
ID: 6232163
What site can I get an upgrade from? I only have Microsoft ActiveX Data Objects Recordset 2.1 Library.
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6232361
get it from ms

http://www.microsoft.com/data/download.htm

get the mdac 2.6 sp1 download and install it

2.1 will work but 2.5 and 6 add some really good features.
0
 
LVL 8

Expert Comment

by:glass_cookie
ID: 6233802
Hi!

Here's 2 files over the net from you - sorry, I'm not going to read everything in this page:

Download...
http://www.vb-helper.com/Howto/startdef.zip
Description: Open a file using the application appropriate for its extension (.doc, .txt, .htm, etc.) (3K)

Download...
http://www.vb-helper.com/Howto/countfld.zip
Description: Total the values in a specific field in a comma-delimited text file (3K)

That's it!

glass cookie : )

PS. Ignore me if I'm out of point or if this is NA.
0
 

Author Comment

by:vibale
ID: 6234335
The previous error disappeared, but I know get different ones, most of which I've sorted, but I can work this one out. It says.

Subscript out of range

and when I hover over the line in my code where it stops, it says:

CStr(holder(2)) = <Subscript out of range>

any ideas?
0
 

Author Comment

by:vibale
ID: 6234376
Ive also changed the line that says:

Split(sline1, "'")

to

Split(sline1, ",")
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6234862
the subscript is about the array holder

it is 0 based

example

s = "a,b,c"
holder = split(s,",")

holder(0) = a
holder(1) = b
holder(2) = c
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6234871
you are also right in changeing the ' to a , in the split line.  I made a typo
0
 

Author Comment

by:vibale
ID: 6237215
What do I need to do. I can't seem to see anything wrong. What do you mean by it being 0 based.
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6237799
0 based is the array number the array starts on.  some things in vb are 1 based and start on 1 so if you tried to do something like onebasedarray(0) you get the subscript out of range when you get the error what does your text file look like?  if you have any blank lines you will only get Holder(0) not 1 and 2 etc.  you need to check for this or handle the error.
0
 

Author Comment

by:vibale
ID: 6237895
Thats great, that works fine. One final question. Is there anyway of returning an error to the user if the file they choose to use as input is not valid. I.E it doesnt have enough fields, or it has too many fields, or it isnt comma sepearted. Is there any way od detecting this?
0
 
LVL 3

Accepted Solution

by:
jrspano earned 75 total points
ID: 6238057
yes but you have to detect each case.
the too many fields is easy just check holder for higher numbers.  be sure to handle any errors if the file is real.
this will sort of work of the "," also if holder isn't valid then the file is wrong.  If you are creating the files yourself or someone that works with you put some kind of unique header on each file.  read the first line of the file if it is the header you have a good file.

once you determine the file is bad then use a message box to tell the user and then exit the sub or function
0
 

Author Comment

by:vibale
ID: 6238406
Thanks alot. That was really helpful.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now