Help Loading Formatted Text File to MySQL

johnbasham · February 23, 2011, 6:45am

I need help writing a simple PHP script that will load a formatted text file into a MySQL database.
The file is formatted into columns and rows. Each column and row have a set length. This is true for all but the top three lines of the text file. These top three lines of data identify to which location this data belons and the time it was initiated. So as a newbie I am having trouble figuring out how to insert this data with the two different formats into a single formatted database.

Here is an example of the top few rows of a typical file (they are much longer).

[font=courier][size=8pt] Station ID: KFTW Lat: 32.82 Long: -97.36
NAM Model Run: 0Z FEB 23, 2011

Forecast Hours: 0hr 6hr 12hr 18hr 24hr 30hr 36hr 42hr 48hr 54hr 60hr 66hr
Sfc Prs(mb): 990.4 990.7 987.6 989.3 984.5 985.3 982.9 983.0 981.6 988.4 990.8 991.1
Mean SLP (mb): 1015.8 1016.4 1013.4 1014.6 1009.2 1010.4 1008.2 1008.1 1006.4 1014.2 1017.4 1017.1
2m agl Tmp (F): 63.7 56.4 53.2 63.0 72.0 65.4 62.1 67.8 72.5 50.1 39.8 57.6
2m agl Dewpt(F): 41.0 42.2 49.8 59.2 63.5 64.3 62.1 59.3 36.7 39.9 38.3 41.0
2m agl RH (%): 43 58 88 87 74 96 100 73 26 67 94 53
10m agl Dir: 114 106 143 165 163 164 172 216 265 351 359 114
10m agl Spd(kt): 9 6 7 9 9 11 11 12 11 7 6 3
6hr Precip (in): 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.14 0.00 0.00 0.00 0.00
AccumPrecip(in): 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.14 0.15 0.15 0.15 0.15
Sfc CAPE (J/kg): 0.0 0.0 0.0 0.0 446.6 145.5 82.8 419.0 0.0 0.0 0.0 0.0
Sfc CINH (J/kg): 0.0 -0.7 0.0 0.0 -26.1 -117.5 -343.5 -76.4 0.0 0.0 -0.6 -0.9
0-3km Hel(J/kg): 317.1 441.1 157.9 155.8 125.1 238.4 194.5 99.3 59.1 150.9 119.4 145.3
Precip H20 (in): 0.68 0.83 0.86 1.07 1.02 1.16 1.28 1.07 0.34 0.32 0.33 0.35
Lifted Index©: 2.2 1.5 1.1 -0.1 -1.7 -4.1 -4.0 -1.2 5.2 10.1 12.2 12.4
700mb VV(-ub/s): 0.7 -0.6 0.8 -0.5 2.0 -1.3 8.3 0.9 2.1 -2.2 -2.3 0.7[/size][/font]

Any help with this is greatly appreciated. I will have hundreds of text files like this for different locations around the United States. I need to figure out the best way not only to load the data into the MySQL database, but choose the best method for storing these individual site data sets for retrieval later.

Warmest Regards,
John Basham
Senior Meteorologist
Storm Spotter Live, Inc.
Fort Worth, Texas

Coder · February 23, 2011, 10:13am

Hi,

I have created a quick script that will read and parse your data file (based on your data sample). The data is then displayed in HTML table, so that you can see results. Here is the code:
[php]

<?php set_time_limit(600); // allow 10 minutes for processing file $fd=fopen("data.txt","r"); if($fd){ $row=0; $id=''; $lat=''; $long=''; $run=''; $data=array(); while(!feof ($fd)){ // loop: for each row $row++; $line=trim(fgets($fd,4096)); switch($row){ case 1: // parse row 1 if(preg_match('~station id:([a-z0-9\s\-]*)lat:([0-9\.\s\-]*)long:([0-9\.\s\-]*)~i',$line,$matches)){ $id=trim($matches[1]); $lat=trim($matches[2]); $long=trim($matches[3]); } break; case 2: // parse row 2 if(preg_match('~nam model run:([\s\S]*)~i',$line,$matches)){ $run=trim($matches[1]); } break; case 3: // ignore row 3 break; default: // parse data rows if($line!=''){ $i=$row-3; $data[$i]=array(); $data[$i][0]=trim(substr($line,0,16)); // column 0 for($j=1;$j<=12;$j++){ // fixed size columns 1-12 $data[$i][$j]=trim(substr($line,16+7*($j-1),6)); } } } } fclose($fd); } // display parsed data -------------------------------------------- echo '

Station ID: '.$id.', Lat: '.$lat.', Long: '.$long.'

'; echo '

NAM Model Run: '.$run.'

'; echo ''; for($i=1;$i<=$row-3;$i++){ echo ''; for($j=0;$j<=12;$j++){ if($j==0){ echo ''; } else{ echo ''; } } echo ''; } echo '

'.$data[$i][$j].'

'; ?>

[/php]

As for database structure, I would suggest to create 2 tables, say: FileInfo and FileData. Each record in FileInfo table will contain Station ID, Lat, Long, Model Run, etc. While FileData will contain all the other data from file. FileData table will also have a field referring to corresponding FileInfo record.