Database Entry Automation

[ojef1]

Not sure where the best place to post this would be...but what would be the best way (if at all) to go about writing a program that would automate data entry (from an excel spreadsheet) into an online database.

I've been doing them one by one for the past couple of weeks...only have about 10,000 left...any help would be much appreciated thanks

 

[Nanobyte]

9,999....9,998......The most flexible option is to use a macro/script program. There is no easy method that is going to have you up and running in a few hours. You have to learn some programming whatever you do.

There are a variety of script programs. AutoIt and Autohotkey probably have the largest support base. Both are free. I use Macro Express Pro (payware) and AutoIt. AutoIt is very handy for Excel because it has a number of built-in apps - it can directly access cell values for example. AutoIt would be neatest but has more syntax issues. To get something working quickly, MEPro is way ahead. Not so flexible but easy to program.

What these apps do is mostly mimic what you would do manually. You have to allow time for things to happen and anticipate problems. What usually happens is that you write a script, run it, and some unexpected dialog opens up and you have to adapt the script to cope with it. To give you some idea of how it would work with MEPro, below is a quick outline for just one item being entered in a data entry web page. Specifics may be much more complicated.

1. Open up data entry web page
2. Check it has loaded
3. Open up first Excel page
4. Move to first cell in data column using Excel's Go To (Ctrl+G)
5. Copy value
6. If blank, increment row count by 1, return to 4
7. If cell contains non-blank, copy value, increment row count by 1
8. Activate web page, tab to entry box
9. Paste in Excel value
10. Tab to "Enter" button or hit keyboard Enter
11. If another page appears, return to data entry page
12. Activate spreadsheet
13. Return to 4
14. If say three blank cells in a row, end of spreadsheet
15. Activate next spreadsheet, return to 4

You cannot be specific unless you have the documents in front of you and can actually try the script out.

You can make it much simpler. You have the webpage and Excel sheet open. You manually move to a cell with the mouse, hit a hotkey that runs a macro that copies just that data, pastes it into the webpage and hits Enter for you. For expedience, that may be a better route to go as there would be far less programming and debugging.

 

[ojef1]

I just downloaded autoIt and this seems perfect thanks a lot...this looks like an awesome script with a lot of potential for other things as well

 

[Nanobyte]

The syntax in AutoIt takes a bit of learning. I suggest you start with my last suggestion. Make a script that will copy a previously selected cell, select the webpage, navigate to the data entry box, paste the cell content and hit "Enter" or whatever. Once that is working from a hotkey you can look at automating so it goes through an entire worksheet automatically.

The SciTE editor is worth downloading and using if you did not already. The Excel UDFs are in Autoit3/Examples/Helpfile. It's Help file is in the Autoit3 root folder together with the standard Help file.