Form Extract from selected HTML text / Post to mySQL DB

ChevyAdmin · #1 06-20-2008, 04:56 PM

Hi Everyone,

I have a unique issue that I'd like to see if anyone has any information on. I've been serarching for a few weeks now and have found bits and pieces of what I'd like to do, but so far no one program, item or person can seem to help me. Here's a summary of what I'm attempting.

We have MSword invoices that come over each day and they're printed, and manually entered into a database that we can't mess with, i.e, no pulling the data without an act of congress through vendor.

These word documents have no header tags, or name fields, so it's pretty much raw data.

What I'm looking to do is be able to have these Word documents either by themselve or after they become HTML documents, be
1.) Viewed in a web browser with an interface that can scroll through the documents within a certain directory on our intranet web server.
2.) The documents will be cleaned out each day via script so that the next days invoices can be viewed easily and without redundant searching with this newly developed interface.

Now I know the above can be done fairly easily. I'm still not sure how to acomplish it, but I know there are some real experts in these forums whose help I would very much appreciate.

The second, and most complicated portion comes in with our SQL database. What we'd like to do within the browser interface is be able to pull selected areas of the text, i.e, invoice number, year, make model, etc., and have that data be extracted from each individual Word / HTML document and then have it posted into our mySQL database fields.

I've attempted to mess around with the layers in BV with hopes I could create some sort of transparent form field over the text and then have the form fields somehow populate with the underlying text. Kind of like a snapshot, but with only the selected fields being captured. Once the submit button gets pressed, only the form data that is populated would then be populated into our database when we hit submit. I've included a picture below of what it might look like, but have not got the functionality built into it yet. If anyone has any ideas on how this might be accomplished, or if it's even possible, please let me know.

Thank you all very much for any input you can give.
[IMG]http://usera.imagecave.com/moranaut**007/Banners/Extract_Idea.jpg[/IMG]

navaldesign · #2 06-20-2008, 06:02 PM

Not sure of exactly what you want to do because further info would be required.

Step 1: you are saying that you are MANUALLY entering the info into "a database we can't mess with". Is this database also on your intranet ? is it on the internet ?

If both your database and the other one are fed through html forms, you can create a script that will POST to both databases (with one posting only) so you accomplish both tasks.

As to viewing the invoices in a browser (though i don't understand why, simply store the MS Word file on a intranet accessible directory on your server, and all your intranet users can retrieve it from there) you can simply save the MS Word file as htm and upload it on your server. A cron job could then delete files from the specific folder, if dated, OR, you can simply add a routine to run manually, everyday, to delete previous days files.

Unless i have misread your post..

ChevyAdmin · #3 06-23-2008, 05:02 PM

Hi Naval Design, Thank you for taking the time to read my post,...we're almost there with what you wrote, but it's a bit more complicated, and I'm sure it shouldn't be.
The database is on our intranet, but due to restrictions placed on us by the manufacturer, we can't access that data except through a terminal window or a new application that will cost us a ton of money to get. I originally researched it, but it's overkill for what we actually need it to do.

You see, each day, a lady in our office access a secure website, and pulls our invoices through the built in WORD viewer on the site. She then prints them off and has another office worker manually enter in the invoice data into our Dealer management system (Database). Once it's entered in there, we really can't pull data from it, because it's locked down. The company that we use to manage that information won't let us pull from it for outside applications.

I originally set up a DMS system to manage the WORD documents we were downloading, but the management didn't like typing in search criteria. They preferred some sort of drop down menus that were already pre-populated with the inventory data, i.e. Year, Make, Model, Color, etc. that they could select from. I knew that we could develop a PHP page that could populate with that data, but had to figure out how we could get that data extracted from the WORD documents.

I searched on the internet for some way to extract the specified fields from the MS WORD Documents, but ended up in a sea of data mining applications that didn't do what I was hoping for. It would be great it GM could just send the invoices over to us in Excel format, but that's the act of congress thing I was speaking of. They'll only allow us to view this data in MS word format, or through their secure website with a less than user friendly interface. Most of the people that I work with that need access to these invoices are sales people, and they're pretty much the On / OFF, click here bunch. Once you get past that, they're lost. They know how to sell cars really well, but once you get them past the 3rd step, of anything technical, they glaze over, and It's useless. That's why I'm trying to make the end user interface as simple as possible.

I've listed below what ideally we'd like to see happen.

1.) Download the word documents from the secure server, and pop them into a shared intranet directory.
2.) Run a script, program, or procedure that somehow will pull specific data from the Word documents, and populate into a MySql database.
3.) Web based viewer for our intranet that has drop down menus for us to chose from, i.e. Year, Make, Model,, etc.

Once all the critera is selected from the drop down menus, that will narrow the field of available invoices. (shown below the drop down menus in a small viewer window that dynamically updates and lists the invoices as items are selected.) For example, it I started with all the 2008 Malibus, it would list all of them in the window until I selected the Red color drop down, then all that would be listed there would be all the red 2008 Malibu vehicles.
Then the end user could click the specific invoice to view the original word document that the data was pulled from. They could then either print it, put it into a pending state, or drop it into a sold folder. ( I was thinking like a tag or something on the file, but I'm quickly finding out that my programming skills are sorely lacking).

I've got what I want to do in my head, but It's kind of like saying I want to build the worlds first flying car with nothing more than a hair dryer, and a lawnchair. It seems almost impossible to do what I'm hoping for, and I'm not quite sure that It can even be done. The tools, and knowledge I have are limited, and I've been trying to come up with a simple solution, but I don't think it exists.

If this can be accomplished, or invented, I'm sure there might ba a patent in it somewhere. Thank you again for your reply. I hope the above information helps.

navaldesign · #4 06-23-2008, 05:16 PM

There is no way you can extract specific data from a Word document. Some try can be made if the invoice format has specific structure, but i doubt it.

To do what you want, you need a script. One that would allow a user, to type in all the details required, store them in a database, and also store the Word document that you have downloaded from Gm as well. Then the user interface could be made in such a way that it would allow the rest of the features (as you select some data, lik eyear. color etc, a Ajax script searches tha database to see which invoices match that criteria, and updates a window in the side or the lower part of the screen). When all search criteria are entered, the window will be populated only with those invoices, and the user can select which one to see simply clicking on it.

This is not so easy an application, but neither a hard one. However, i don't know what kind of money you are talking about, i would expect (more clarifications are required) such an application to be on the $600 - 800 range

ChevyAdmin · #5 06-25-2008, 10:10 PM

Thanks Naval Design,... Once again, your expertise are greatly appreciated. I think I'll put this one on hold for a little while.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)