[RESOLVED] txt file to db how to store it for MySQL

rremm2000

Hello all and thanks for any help you can proved.

I have text files which contain space delimited records not more than 500 lines of text in each file. These files are electronic records from a hand held device and the data in the text file looks similar to the following.

                   ecode1                                                  ecode2
Ln     code   bool   Date                Time     Measure  bool
1)	07	1	20080115	09:40	00759	   0
2)	01	1	20071004	10:07	00108	   0
3)	01	1	20071004	10:06	00108	   0
4)	01	1	20101004	10:06	00108	   0
5)	01	1	20071004	10:05	00108	   0

I'm doing this as a side job at work and I thought we could store these in MySQL by the unit's serial number however, this is not just a few files we need to add. We receive about 10-20K of these devices a month. So this is going to be a lot of data that needs to be kept track of with the unit's serial number

However, I'm not sure how I should set this up for storage.
I was thinking that maybe some structure like the following would be best but I'm just not sure.

Table 1 MID = auto increment Meter ID
MID Meter_SN

Table 2 LNID = line number ID
LNID MID Code Ecode1 Date Time Measure Ecode2

but we want to do some pretty wild queries like we will want to find all the serial numbers where the date is not sequencial ie line 4) in the example shows a sudden date change to year 2010 this was actually not set by the user but by an ESD electrostatic discharge event.

Alternately I was thinking maybe breaking up the whole thing into individual pieces like

Table 1 MID = auto increment Meter ID
MID Meter_SN

Table 2 LNID = line number ID MID is from Table 1
MID LNID

Table 3
MID LNID Code

Table 4
MID LNID Ecode1

Table 5
MID LNID Date

Table 6
MID LNID Time

Table 7
MID LNID Measure

Table 8
MID LNID Ecode2

I'm just not sure what the best approach is, we want to be able to analyze the data with some of the search queries and php.
Or
I could just dump the whole file into bin text table and pull each one out analyze it with php and then do the next one but that seem too long for the number of units we receive each month.

All these recordes would be kept for 5-10 years i.e. for life of product kind of thing but we want to be able to find various systematic problems that can be identified in the log files

Happy to hear any comments or suggestions

Jack_McSlay

fortunately, it appears the fields are separated via "\t". so it should be dead easy to parse the file


$file = file('records.txt'); //get the file, as an array with each element being one line of the file
unset ($file[0],$file[1]); //remove the first 2 lines, which seem to be useless

$entry = array(); // create an empty array
foreach ($file as $record){ //go through the records to parse them
	$record = explode ("\t",$record); //split the fields
	foreach ($record as &$field) $field = trim($field); //clear whitespaces

$record[0] = (int) $record[0]; // convert the id into a plain number

//conterting date and time to be inserted as a timestamp
$date = str_split ($record[3]);
$record[3] = $date[0].$date[1].'-'.$date[2].'-'.$date[3].' '.$record[4].':00';
unset ($record[4]);

$entry[] = '(\''.join ('\',\'',$record).'\')';
}
$entry = join (',',$entry);

mysql_query('INSERT INTO `records` VALUES '.$entry);

this should make a simple parse and insertion of data into mysql, then you need just to adjust it to your tables

rremm2000

wow, thanks very much