Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have a job as an intern at a technology company, I was given the unfortunate job of performing some data entry into our web management system. The information entered into the web form is stored in a MySQL DB. Upon receiving the data I realized I would have to submit this online form about 1000 different times all consisting of about 10 different text fields / check boxes per form. (So in other words, would be completely mind numbing and be a ridiculous waste of time and resources, or so I thought...)

Having used databases a good bit prior to this, my immediate reaction was to just write a short MySQL script to bulk import all of the data, especially since it was already presented to me in an excel spreadsheet ready to go. Thought it may have been some sort of a test since it seemed too obvious. I wrote the script which consisted of about 10 lines of code but was then informed I couldn't be trusted with MySQL Admin privileges to run said script. So my next thought would be to write a script to just enter the information through the web form (Which will take ten times longer but it's what I have to)

Being unfamiliar with scripting of this nature (seems like I would need something similar to a bot, but the good kind) I was unsure of how to proceed to do this. Is there a preferred language to use to enter the data i have into the web form I do have access to? I'm not particularly looking for this to be done for me by any means just a nice point in the right direction as far as what scripting language to use and how to pair that with the data I have that needs to be entered.

Thanks for the help/ valuable input!

EDIT:

Is there a way to perform this using perl without having access to place any files on the server?

Would I be able to run some Javascript loops to pull the data out of .csv or just a .txt format with line delimiters and insert it into the web form?

share|improve this question
    
I highly recommend Scrapy, it does what you need and it's fairly easy to learn. –  OneOfOne Jul 12 '12 at 1:32
    
see the comment below my answer about your perl-related question. No, you don't need local access to the server. As long as the web-form itself can be accessed from your workstation's browser, it means a perl script (or anything else) running on your workstation could also directly submit content to the web-form. –  haylem Jul 12 '12 at 3:09
2  
Regarding the JS route, the short answer is no, as you normally can't access the content of a file in a browser environment. The long answer would be yes, if you are prepared to wrap the page within an iframe that would load the xls from a server you host locally, deal with browser restrictions to get to the content of the file and parse it, and then update the values in the iframe (that contains the form). That would be one (crazy) approach, but other weird hacks could be imagined. I'd advise to go the simple way. We're talking potentiall a 100 chars shell script vs nightmarish code here. –  haylem Jul 12 '12 at 3:12
add comment

3 Answers 3

There are a few possibilities (last one would be the easiest and most sensical, in my opinion, except if that's meant to be a long-term and reusable piece of code):

  • Use a web-testing framework

    They are meant to do this sort of stuff, so obviousy they do it well. But I think they're a bit heavyweight for what you want to do. For instance, Adel recommended Selenium, which is a great testing tool but a freaking monster to get started with (and will fire up browsers, except if you use the new WebDriver-based API that will use a browser-less driver like HtmlUnit.

    So, that's why I'd recommend, if you go down this route, to just use something like HtmlUnit (which you could invoke from a Java program, or from any other JVM-based language: Groovy, Scala, Clojure...). But I'd still regard this as relatively heavy.

  • Use a general-purpose scripting language

    Python, Perl and a herd of others will allow you to write this from scratch quickly, or to reuse an existing library to implement your own HTTP client to send your POST requests.

    If this think is going to be maintained in the future, I'd go with Python. If it's going to be quick and dirty, Perl is a hacker's best friend (and CPAN its favorite sledgehammer).

  • Use bare shell scripting and something like curl

    Go even more minimalistic: bare shell-scripting to process your inputs and format your data, some curl invocations to POST to the server, and voila!

    If you're on Windows, Powershell will be your friend.


There are hundreds of other possibilities to do this, I barely mention the ones that come to mind and that I'd use.

If that was my task, I'd probably write an ugly command line that:

  • if it's a simple spreadsheet (not something where you'd need to cherry pick cell values):

    1. converts the .xls to .csv,
    2. pipe it through to whatever suits you to transform the data,
    3. pipe it to a curl command to POST.
  • if it's a complex spreadsheet (where you'd need to cherry pick cell values):

    • script the data extraction bit,
    • invoke curl or use the scripting language's built-in libs to POST.

I'only use a more advanced testing framework if:

  • I'm likely to be asked to do this again for different data formats,
  • I'm likely to be asked to do this again for different datasources and target DBs,
  • I'm likely to be asked to do this often.

In that case, a more engineered approach does make some sense, for maintainability and extensibility.


In all cases, remember to backup that script and pass it along, and to document it (a README would do) and implement a usage. If they have one, version it in their SCM.


Note: another reason why taking the web-form submission approach might be better than the "direct to SQL" approach is that the server receiving the form might be doing extra checks you aren't aware of at this time.

Not saying it's the case, but maybe security wasn't the only reason.

Good luck with the job.


EDIT: just noticed you tagged this as "PHP". I don't really see why, as that would imply for the code to be server-side (well, you could use PHP for any kind of scripting, but why do this to yourself?)

share|improve this answer
    
Thanks for all your input and suggestions. I will take some time and go over them. There was no definitive reason as to why I tagged it with PHP, being that I was unsure of what language/ route to take I just tagged it with a few languages I knew could be used as a script in this case or close to it. One of my first questions where I got to the tag section and had to ponder for a minute what to put haha. Thanks though –  ityler22 Jul 12 '12 at 2:10
    
At first I was thinking of going with the perl route, but since I am not going to have direct access to place files on the server, (please correct me if i'm wrong) but that would make using perl much mor difficult..I think am going to try to come up with a quick solution using curl and maybe also using formfind to help identify the names of all the form elements and see how that goes. –  ityler22 Jul 12 '12 at 2:26
    
@ityler22: I think there's some confusion: where do these XLS files come from? My understanding was that you have XLS files, and that based on the content of these files, you have to submit a web-form that then stores to a MySQL Db. So all you need is any computer on which you can install whichever tool your prefer, and then pick a solution that you like to post information to the server's web-form (basically, most probably a POST request to the action URL of the <form> tag in the web-page, with correctly named key-value pairs for the form's fields). You don't need local server access. –  haylem Jul 12 '12 at 3:06
1  
@ityler22: using formfind is an option is the form is long, otherwise just inspecting the code would do, Might even be faster than spending time installing and running formfind. Anyways, I hope this helped. –  haylem Jul 12 '12 at 3:07
    
OK thank you for clarifying, my mistake. I was initially thinking I would need access to said server to place the perl script on in order to run it. Whoops.. The original file is in XLS format but I have already converted it into a .csv when I was going to attempt to use MySQL to create the script. Yea I originally would have just inspected the page and looked for the element ids but while researching I stumbled upon the FormFind, just posted to possibly help someone else in different circumstances. Thanks for the help / clarification. –  ityler22 Jul 12 '12 at 3:09
show 1 more comment

Firstly, you probably should change your attitude a little. You say " given the unfortunate job " - why? I mean, you may learn valuable lessons here. Plus, you are an intern. It's a great job, and it does involve "dirty" work.

Hmm, I might advise you to check out Selenium. If you have to automate a web function, it's quite useful to use Selenium.

share|improve this answer
2  
Sorry I may have came off slightly more displeased then I actually was. It was just very disheartening to be given a problem, come up with a solution that I would have thought was a really good idea (saving company time / resources) that my co-workers hadn't considered and then only be told that I didn't have permission to use my solution. I will check out Selenium and report back though, thanks! –  ityler22 Jul 12 '12 at 1:39
    
Selenium seems way overboard to me for this. A testing framework seems like a good idea, but for barely submitting forms I'd go with a lighter one that's faster to pick up, like HtmlUnit. Or even just write a small HTTP client with HttpClient in Java, or in Perl, or in Python, etc... –  haylem Jul 12 '12 at 1:49
add comment

I think haylem's answer provides the best set of options. That said, I'd like to mention Autohotkey if you are on a Windows platform. It is a decent scripting language, but very strong for automating GUI tasks.

I agree that posting the data directly is the best way to go but there are situations when this doesn't work as easily. An example would be if your administration system requires a login and then tracks an authentication ticket of some sort using client-side state. You could certainly emulate this using headless HTTP, but it adds complexity to the problem. At some point it might become easier to manipulate the browser directly. Autohotkey is a good choice for this. It's just a scripting language so it's not as heavy as Selenium, and it can be useful for other unrelated tasks as well.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.