#134159 - 2005-02-19 01:10 AM
parse txt file and dump output to a database.
|
JefDye
Fresh Scripter
Registered: 2004-08-10
Posts: 6
|
Here is my task:
I have a text file that I have to parse for new information daily. Here is a sample of the text file.
Code:
Chairs:
Bloomfield, Columbus, Duncan, Holdrege, Osceola, Schuyler,
Superior, Tarnov, Wakefield 1.75
Columbus, Duncan, Osceola, Schuyler, Tarnov 2.45
Belgrade 2.41
Footstools:
Bloomfield, Columbus, Duncan, Holdrege, Osceola, Schuyler,
Superior, Tarnov, Wakefield 1.75
Columbus, Duncan, Osceola, Schuyler, Tarnov 2.16
Belgrade 2.41
Tables:
Columbus, Duncan, Osceola, Schuyler, Silver Creek, Tarnov 5.17
Belgrade, Plymouth 5.16
I need to read the number following the word Columbus under the chairs and tables section but not the entry under footstools in the file. You will notice the number does not always appear on the same line as the word Columbus, it may be on the next line down. I then need to write that data to a csv file which will then be imported into a database for historical purposes.
Does anyone have any ideas?
Thanks,
Jef
Edited by sealeopard (2005-02-19 04:17 AM)
|
|
Top
|
|
|
|
|
#134161 - 2005-02-19 01:52 PM
Re: parse txt file and dump output to a database.
|
Les
KiX Master
Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
|
Parsing data out of files consistently requires that certain assumptions are always true. In your case, if I understand it right, it is the first number that follows the word "Columbus" except for the one following "Footstools". If that is always the case, and if the number always has a decimal in it, Split(), Left(), and Right() are very effective at doing it.
If necessary. you can Split() on the colon to section off the headings like "Footstools" but in my example, I did not. Assuming you have the file in a string, Split() on the word "Columbus" and then the decimal.
$StringArray = Split($String,'Columbus')
$Chairs = Right(Split($StringArray[1],'.')[0],1)+'.'+Left(Split($StringArray[1],'.')[1],2)
$Chairs ?
_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
|
|
Top
|
|
|
|
|
#134163 - 2005-02-19 05:56 PM
Re: parse txt file and dump output to a database.
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4403
Loc: New Jersey
|
This works...
Code:
$Wanted = 'Chairs:Tables:' ; sections we want
$Site = 'Columbus' ; Site we want to report on
$Search = '' ; Found section flag (Text)
$Grab = 0 ; Found site, but wrong line flag (Bool)
If Open(5,'Furniture.txt', 2) = 0
$Line = ReadLine(5)
While @ERROR = 0
If InStr($Wanted, Trim($Line)) And $Line <> '' ; found a section
$Search = Trim($Line)
EndIf
If $Search ; search is active
If InStr($Line, $Site) Or $Grab ; found a site
If Right($Line, 1) <> ',' ; is line complete?
$Tmp = Split($Line, ' ') ; split the data
$Value = Trim($Tmp[UBound($Tmp)]) ; get last field
$Search ',' $Value ? ; write it, probably to a file...
$Search = '' ; found it - reset search
$Grab = 0 ; reset Grab
Else
$Grab = 1 ; set grab
EndIF
EndIf
EndIf
$Line = ReadLine(5)
Loop
$ = Close(5)
EndIf
The key issue is that the data may be on a different line from the search criteria. You need to parse the file over multiple lines to collect what you want.
The file is read, searching for the SECTION: we want, allowing us to skip "Footstools:". When a section of interest is found, a FLAG is set (with the section name) indicating we should search for a detail record.
If the Section flag ($Search) is set, we start looking for the site. If we find the site or the Grab flag is true - but the last char is a "," - we know that the data is on a continuation line, so we set the GRAB flag and get the next line.
If Grab is true OR the site is in the line of data AND the last char is NOT a "," - the line is split on spaces, and the last field is captured.
Note where the FLAGs are set and cleared. The FLAG basically says we found one thing and need to locate the next. Once we find it, the flags should be cleared to prepare for the next search.
You'll need to clean the code just a bit, and modify the line that writes your CSV data. You might want to look at the CSV UDF I posted today.
Glenn
_________________________
Actually I am a Rocket Scientist! 
|
|
Top
|
|
|
|
|
#134164 - 2005-02-21 05:55 PM
Re: parse txt file and dump output to a database.
|
JefDye
Fresh Scripter
Registered: 2004-08-10
Posts: 6
|
Glenn,
Excellent data analysis. I am looking forward to trying your suggestion. It sounds like that should work. I will let you know. Thanks for the detailed explanation.
Jef
|
|
Top
|
|
|
|
|
#134165 - 2005-02-21 07:18 PM
Re: parse txt file and dump output to a database.
|
JefDye
Fresh Scripter
Registered: 2004-08-10
Posts: 6
|
Glenn,
I made a few tweaks and it is perfect. I am going to check out the csv UDF right now. Thank you so much.
Jef
|
|
Top
|
|
|
|
Moderator: Arend_, Allen, Jochen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Mart
|
0 registered
and 1046 anonymous users online.
|
|
|
