Parsing Text Documents for MySQL

# MAIL FROM:all-THISServer RCPT TO:SW-THAT DATA SUBJECT:PRIVATE PROPERTY DIG NOTICE - 324746708 SUBJECT ::PRIVATE PROPERTY DIG NOTICE - 324746708 DIG NUMBER ::324746708 AMENDED DIG NOTICE ::NO CONTACT PERSON NAME ::N I C K POC PHONE NUMBER ::847-303-5800 COMPANY ::U.S. WATERPROOFING COMPANY DIG DATE ::09/08/2003 10:13:00 EMERGENCY ::NO ACTIVITY COMMENT :: FROM STREET NUMBER ::10826 FROM STREET DIR ::S TO STREET NUMBER ::10826 TO STREET DIR ::S TO STREET NAME ::TROY STREET SUFFIX ::ST WARD ::19 LOCATION COMMENT ::SCHEDULED WATERPROOFING TO BE PERFORMED: LOCATORS: LOCATION COMMENT ::WILL YOU LOCATION COMMENT ::PLEASE LOCATE WITHIN (5 FEET) OF THE SOUTH AND EAST LOCATION COMMENT ::WALL OF LOCATION COMMENT ::PROPERTY. OUR FAX HERE IS 403-303-5258. THANKS! DISTRIBUTED TO :: 5, 15, 21, 29, 122, 134, 146, 148 . QUIT I need either PHP or Perl script to parse this file and enter it as a single record in a MySQL database? The first 6 lines of text are not needed. Everything from “SUBJECT?? to the last “LOCATION COMMENT?? are needed as fields in the database. What is on the left of the “::?? can be eliminated I want different field names in the MySQL database. From “DISTRIBUTED TO?? to the end of the document can be eliminated. I have many of these files, each formatted the same way and each file has a unique file name without any extension example: 11244201 [login to view URL] SW 20030904 N 324746708 The file name has important information that is not in the document itself. The first 8 digits of the file name represent a key. The next 16 characters need to be stripped away. The “SW?? needs to be saved. The 20030904 is the date need to be saved. The “N?? needs to be saved. The “32474670?? can be stripped away. The Script would have to loop over many of these files and enter a record in the database for each. If a duplicate entry appears it would need to prompt for action: “REPLACE?? or “SAVE AS NEW RECORD?? while showing fields from both records for comparison. A 27 field table in the MySQL database updated daily with this script. 5 fields from the document name 21 fields from within the document and one uniquely generated by the MySQL database. I've had to remove all email symbology from the post with a - ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. 2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request. 3) Complete ownership and distribution copyrights to all work purchased. 4) The data can either be uploaded as function of the script from the endusers computer or it can be run as a batch form the server one the documents ard sent over. 5) A script that will create the field names and table structure of the MySQL database ready to accept records for the parsing script. ## Platform Slackware Linux 9.0 Running Apache, PHP, Perl and MySQL The enduser will be on a windows box using IE