Parse Text file extract to table 1

[briprogram]

I am new to Foxpro and I am using VFP7. I want to extract tab delimited data all with code from a text file that looks like the following and move it into a new table. First I dont need line numbers 1- 10. Line 11 and 12 will be column headings. After that is the days of the month which I want to put into the new table columns created from lines 11 and 12.
Can anyone explain what coding commands or functions to use to accomplish this?
And also what coding is required to open the txt document while foxpro extracts the data to the table?

Thanks so much

1MONTHLY CLIMATOLOGICAL SUMMARY for APR. 2005
2
3NAME: Pinellas Park CITY: Pinellas Park STATE: Florida
4ELEV: 18 ft LAT: 27° 50' 36" N LONG: 82° 44' 08" W
5
6 TEMPERATURE (°F), RAIN (in), WIND SPEED (mph)
7
8 HEAT COOL AVG
9 MEAN DEG DEG 10WIND DOM
11DAY TEMP HIGH TIME LOW TIME DAYS DAYS RAIN SPEED HIGH TIME DIR
12------------------------------------------------------------------------------------
1 75.6 84.3 12:00p 67.1 5:00a 0.0 10.6 0.00 4.2 20.0 3:30p SSE
2 66.5 75.6 1:00a 61.8 8:30p 0.7 2.2 0.34 5.5 28.0 3:00a N
3 60.5 69.4 3:30p 48.8 7:30a 5.0 0.7 0.00 1.8 14.0 3:00p N
4 63.0 79.6 4:30p 48.3 5:00a 5.7 3.7 0.00 1.0 12.0 11:30a NE
5 67.9 82.4 3:00p 55.0 7:30a 2.7 5.6 0.00 1.5 17.0 12:30p ESE
6 72.8 82.4 4:30p 65.0 6:30a 0.0 7.8 0.00 2.9 19.0 1:00p ESE
7 72.5 80.9 1:30p 68.1 8:00p 0.0 7.5 0.13 7.4 34.0 2:00p SSE
8 70.2 77.2 1:30p 64.2 7:00a 0.0 5.3 0.00 2.3 18.0 12:30a W
9 68.9 77.6 2:30p 60.9 5:00a 0.7 4.6 0.00 1.3 13.0 3:00p NW
10 71.3 82.6 3:30p 60.9 6:30a 0.5 6.8 0.00 1.9 16.0 10:00p NE
11 71.0 83.2 5:30p 61.0 6:30a 0.7 6.7 0.00 2.4 17.0 10:30a E
12 72.9 80.7 1:30p 65.2 6:00a 0.0 7.9 0.00 3.9 20.0 8:00p ESE
13 71.3 78.5 1:00p 62.9 12:00m 0.1 6.3 0.30 2.6 19.0 3:00a W
14 68.8 78.5 1:00p 58.4 3:30a 1.2 5.0 0.00 2.0 17.0 7:00p W
15 64.0 75.1 4:00p 53.1 7:30a 3.6 2.6 0.00 3.4 18.0 1:00p N
16 64.0 74.4 4:00p 56.5 6:30a 3.3 2.3 0.00 3.8 18.0 2:00p NE
17 64.1 75.8 5:00p 51.4 7:00a 4.2 3.3 0.00 2.2 14.0 10:30p N
18 65.8 80.3 5:00p 53.3 7:30a 3.4 4.2 0.00 1.7 13.0 3:30p NE
19 67.8 79.4 2:30p 55.7 7:00a 2.0 4.8 0.00 1.4 13.0 1:00p E
20 70.2 80.3 3:30p 63.5 12:00m 0.1 5.3 0.00 1.7 13.0 4:00p E
21 69.7 80.5 3:30p 59.2 6:00a 1.4 6.0 0.00 1.3 16.0 3:30p NW
22 69.9 83.0 2:00p 59.0 6:30a 1.1 6.1 0.00 1.2 14.0 1:00p W
23 68.9 77.7 1:00p 60.5 7:00a 1.0 4.9 0.03 2.8 26.0 3:00p SW
24 62.7 68.8 3:00p 54.2 7:00a 2.9 0.6 0.00 2.5 17.0 11:30a N
25 63.0 75.8 4:00p 49.4 7:00a 4.6 2.6 0.00 1.1 12.0 1:30p W
26 67.1 79.0 2:00p 55.3 6:00a 2.4 4.5 0.95 3.7 26.0 11:30a SSE
27 69.6 78.1 4:00p 59.5 12:00m 0.5 5.1 0.03 2.3 18.0 11:30a SSE
28 69.2 82.6 5:00p 58.4 5:00a 1.5 5.8 0.01 1.5 14.0 9:30a NE
29 73.3 83.6 2:30p 62.7 1:30a 0.2 8.6 0.00 2.5 17.0 11:00a SSW
30 75.7 83.4 3:00p 67.6 7:00a 0.0 10.7 0.00 5.2 26.0 1:30p SSE

 

[wgcs]

All you need is:

CREATE CURSOR MyData ( MeanDayTemp N(6,1), ;
  HighTemp    N(6,1), ;
  HighTime    C(8),   ;
  LowTemp     N(6,1), ;
  LowTime   C(8),   ;
  HeatDegDays N(6,1), ;
  CoolDegDays N(6,1), ;
  Rain        N(6,1), ;
  AvgWindSpd  N(6,1), ;
  WindHigh    N(6,1), ;
  WHighTime   C(8), ;
  DomDir      C(6) )

append from myfile.txt type sdf
DELETE NEXT 11 && to ignore the first 11 lines

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[crewchiefpro6]

Hmmmm.... Looks familiar. What weather station are you getting this info from?

Reason I ask is that I sell weather stations, and I design automobile racing software. These readings look similar to some of my stuff.

 

[briprogram]

Crewchiefpro6, I am getting this information from my own Davis weather site. Pinellas Park Florida.

http://home.tampabay.rr.com/brian/index.html

Also thank you Bill for the information.

I will be playing with it and let you know what I come up with. It's good to have something to start with.

Brian

 

[mkrausnick]

I think the APPEND option IN Bill's post should be DELIMITED WITH TAB, not SDF. SDF is for fixed format ASCII.

Mike Krausnick
Dublin, California

 

[DSummZZZ]

And you also need a GO TOP, otherwise after the APPEND, the record pointer will be at EOF():


append from myfile.txt type sdf
GO TOP
DELETE NEXT 11 && to ignore the first 11 lines

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[wgcs]

Thanks, Dave, for that good catch. I tested line by line, but browsed in between, so I didn't notice that mistake.

Mike: The file, as it was posted, had no tabs in it, but was fixed-width, so SDF would work, and DELIMITED wouldn't. Of course, if it was changed before posting here, maybe it really had tabs to start with. However, the header being as it is, I expect the file was really a "report" using character positioning for layout... hence spaces instead of tabs.

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[mkrausnick]

Buggynet: Bill's point is important:

... to extract tab delimited data ...

If the data is truly tab-delimited, then use DELIMITED WITH TAB. If it's a columnnar report where all columns are fixed-width, then use SDF.

Mike Krausnick
Dublin, California

 

[briprogram]

The text file is space delimited so I am using Delimited with blank. It works ok. Just ok but I will work that out later. So far I am viewing the cursor created and see the pointer is stopped at record 11 but how does the first 11 rows actually get deleted and the results placed in a table?

Thanks
You guys are coming up with some helpful hints
Brian

 

[mkrausnick]

The following code will delete the first 11 records and remove them from the table:

use mytable exclusive in 0
select mytable
append from ... type sdf       && imports the data
go top                         && set file pointer to top of file
delete next 11                 && delete the headers you don't want
pack                           && remove the deleted records from the table

If you're going to run this many times, bracket the PACK with SET SAFETY OFF and SET SAFETY ON to suppress the warning message.

Mike Krausnick
Dublin, California

 

[mkrausnick]

I don't know if you're new to xBase as well as being new to VFP, so it's worth mentioning that in the DBF world, "deleting a record" actually means "marking a record for later deletion". The PACK statement is what actually removes deleted records from a table and re-claims the space used by them.

You can eliminate the need for the PACK statement if VFP is configured to ignore deleted records. To do that, click on Tools->Options->Data and check the IGNORE DELETED RECORDS checkbox. The disk space won't be reclaimed, but logically the records will be invisible to your code.



Mike Krausnick
Dublin, California