Expert: Alex Barry - 7/29/2009

Question
I'm trying to input a text file,without commas, such as -
John Doe 9 $2,000,000
Bill Niceguy 12 1,999,999
Chuck J. Poorman 15 1,888,888
Franklin Whoisit III 6 1,777,777  & etc. Upto 260 names.
How can I format it so that I can access all the data by inserting commas.
Such as-- John, Doe, 9, $2,000,000
         Frankin, Whoisit III, 6, 1,777,777 & erc. to 260 names
I hope that I made it clear--- Thankyou, John D.

Answer
Hi, John,

First, I want to address a problem I see with your example inputted information (and how we should probably handle it).  QBasic, while inputting from a file, is going to look for commas and line endings (CR+LF).  This will cause a problem when we are inputting information using comma separation, because you money-amount includes commas, so it is likely going to cause some issue.  The way I would go around that is to write my own customized method of separating information from a string, and we'd get the information from the QBasic command "LINE INPUT" which, instead of just getting the next variable, it will just capture the whole line of a file.  How I'd specifically get the money amount is count the number of separators, because there will only be three (3), then the rest is going to be the money amount.  Also, I notice that some of your money amounts start with a "$"...if that is the case for all of the information, then it will be much easier to know whether or not the customized parser will be able to find it.

There are a few ways to go about this.  The easiest way to set this up is to store your information in User-Defined Types (UDTs), regardless of how we are retrieving the information.  Here is how I'd do that:

TYPE NameInfo
 person as string * 32
 num as integer ' I'm not sure that that number represents (between the last name and money amount)
 amount as single ' Change it to an integer if you don't need the decimal place
END TYPE

DIM list(1 to 260) as NameInfo

' You may have to get this through INPUT "", list(x).varname
list(1).first = "John"
list(1).last = "Doe"
list(1).num = 9
list(1).amount = 2000000.00

Now, to retrieve information, either way, we're going to just use a "LINE INPUT #x, varname"

DIM hndl AS INTEGER
hndl = FREEFILE
OPEN "your file.name" FOR INPUT AS #hndl

DIM information AS STRING
WHILE NOT EOF(hndl)
 LINE INPUT #hndl, information
 ' We'd need something here to put our string information into the new UDT
WEND

CLOSE #hndl

So, now I'd write something to break the information apart.  Here is a function you could use (I'll explain it as much as I can).

DECLARE SUB ParseInformation( i AS STRING, separater AS STRING, person AS STRING, num AS INTEGER, amount AS SINGLE )
' So, here is the paramter list:
'  i         - the LINE INPUT'd string
'  separater - the sub-string that separates data (a "," or a " ", in this case)
'  person    - really, this will return the name of the person (I'll show you how to use this down a little ways)
'  num       - returns the number value
'  amount    - returns the money

SUB ParseInformation( i AS STRING, separater AS STRING, person AS STRING, num AS INTEGER, amount AS SINGLE )
 DIM seplen AS INTEGER
 DIM start AS INTEGER
 DIM finish AS INTEGER
 DIM length AS INTEGER
 DIM isNumber AS INTEGER
 DIM firstNum AS INTEGER
 DIM chunks AS INTEGER
 DIM chunk(1 to 5) AS STRING

 person = ""
 num = 0
 amount = 0.0
 firstNum = -1

 seplen = LEN( separater )
 start = 1
 finish = INSTR( start, i, separater )
 chunks = 0
 DO
   chunks = chunks + 1
   isNumber = 0
   length = ( finish - start ) + 1
   chunk( chunks ) = MID$( i, start, length )
   IF chunk( chunks ) = STR$( VAL( chunk ) ) THEN ' This is handy for seeing if a string is a number
     isNumber = 1
     IF firstNum = -1 THEN firstNum = chunks
   END IF
   chunks = chunks + 1
   start = finish + seplen
   finish = INSTR( start, i, separater )
 LOOP
 chunks = chunks + 1
 chunk( chunks ) = MID$( i, start )

 DIM j AS INTEGER
 DIM p AS STRING
 p = ""
 FOR j = 1 to firstNum-1
   p = p + chunk( j ) + " "
 NEXT j
 person = p
 num = VAL( chunk( firstNum ) )
 FOR j = firstNum + 1 TO 5
   p = p + chunk( j )
 NEXT j
 amount = VAL( p )
END SUB


So, here it is altogether (although, i'm not going to put the actual sub in this part...):

TYPE NameInfo
 person as string * 32
 num as integer ' I'm not sure that that number represents (between the last name and money amount)
 amount as single ' Change it to an integer if you don't need the decimal place
END TYPE

DECLARE SUB ParseInformation( i AS STRING, separater AS STRING, person AS STRING, num AS INTEGER, amount AS SINGLE )

DIM list(1 to 260) as NameInfo
DIM cnt AS INTEGER
DIM hndl AS INTEGER
hndl = FREEFILE
OPEN "your file.name" FOR INPUT AS #hndl

DIM information AS STRING
cnt = 1
WHILE NOT EOF(hndl)
 LINE INPUT #hndl, information
 ' Optionally, for the comma, replace the following " " with a ", "
 ParseInformation( information, " ", list( cnt ).person, list( cnt ).num, list( cnt ).amount )
WEND


That is it, I hope that helps you out,
-Alex