Tricky PHP/MySQL Question

mloweris

Complicated Question.
I have a system which measures individual feed intake of cows using an electronic transponder rfid system. Once per day It spits out a text file. (.DAT acutally) To a directory on a local machine. Each line of the text file is a feeding event with times, weights and identification associated with it. however since there is only one file per day there is no date associated with each record. Conviently the file name is in the following format. (VRYYMMDD.dat) the vr never changes but year month day changes as you would expect. i would like to develop a web based application to summarize daily files to monitor animals on the system. Here is my problem, to do this i would like to take the file and load it into a MySQL table each day, i need to parse the date from the filename and append it to each line ( there are 10 fields + date which i need to add). I have managed to do a few of the components, for example I have come up with code to generate a dropdown box populated with the directory contents, (just the filenames) and actually came up with the pregmatch( ) expressions to pull the date out, but am having trouble putting it all together.. I would really appreciate any ideas this group might have to help me out. I am quite sure what i want to do just learning php so i am completely lost in how to put it all togher.
Thanks For any comments.

devinemke

can you post an example of what the text file output looks like?

mloweris

here is a small segment of one of the text files.
there are actually about 15,000 feeding events per day but this should give the format,
the field names are as follows
transponder numeric 18
tattoo character 15
bunk numeric 2
time in time
time out time
id3 numeric 6
wtin decimal 5,2
wtout decimal 5,2
ration character 15
intake decimal 5,2 (field 8-field 7)

You will notice that the file name is VR041111.txt
they actually are .dat files but to upload it here i had to change it
also the 041111 was a bad choice of file to use for this as it does not let you easily see the date format.
it is yy-mm-dd.

Roger_Ramjet

You need a table to hold the file names, which you can get with scandir . This will enable your script to track which files have been processed. How you extract and expand the date from the file name is up to you, there are lots of ways to do that.

Your file is a fixed length comma delimited text file that MySQL can easily inport using load data infile. Import each file into a work table from which you can then run an INSERT.. SELECT query to add the date field as you insert the data into the main table. MySQL can also use regular expressions to parse the filename instead of doing it with PHP

mloweris

Roger Ramjet,
Thanks for your answers, i have two comments.
1. regarding the table of files that have been imported,

I guess that i was thinking of moving files from the directory once they were processed rather than tracking which ones were imported already. Since the files are placed in the directory by the feed intake system rather than by some person it would be better to have them automatically removed. this would eventually lead to a dropdown with only the files that have not yet been loaded into the table. most days this would be one file, and monday there would be three files to load, (sat,sun,mon).

Regarding the idea of using a working table and SQL insert and replace to build the daily chunk of data to insert into the big data table.

I really like this idea, i had it in my head that i would use fgetcsv and read one line at a time to populate an insert statement, with the date variable beign generated further up in the script. this in a loop was my thought, but a working table that i could replace the date field with the parsed file name then bulk load that into the proper data table sounds easier for me (since i know more sql than php.)

I really appreciate your time to comment on this.
Mark

Roger_Ramjet

Glad to help. Using a work table is a standard practice of mine for importing data. It can simplify things no end and unleashes the power of SQL as well. It also prevents main table corruption when the data is duff. Just set up the work table for the incoming data without worrying how it needs to be transformed for your main data tables. You can run all sorts of validation checks on the data and then transform field types etc when you append it to the main table.

I suggested the file tracking table so that you will have a record of what was done and when. That can be invaluable when you get problems like an abnormal termination due to server or comms error. Your file move script can referance the table before moving the file to check that it has been processed fully etc.