Hi. I'm David and my site www.LearnOutLoud.com is going to be a large
database of audio and video learning titles. We've been doing data
entry manually through a back end tool so far with a lot of copying
and pasting, but what we wanted now is to develop a tool that can grab
from a data sheet like an Excel sheet and populate all our entry
fields automatically on a mass scale. We're going to be getting data
feeds from a lot of publishers with over 500 titles and we need some
way to enter this data in fast. And we'd rather not build this data
dump tool from scratch. Does anyone know of any programs or tools out
there that we can get to aid us in our cause?  If you provide us with
a solution or at least lead us in the right direction we'll give you
the answer.

Winrunner can automate it.  It accepts Excel sheets as a data table to
import data.  It's more testing application functionality.  Another
downside is that it's quite expensive. 
http://www.mercury.com/us/products/quality-center/functional-testing/winrunner/

Rational Robot.  Never used it.  Have no idea how it works.  I just
know that it's another automation tool. 
http://www-306.ibm.com/software/awdtools/tester/robot/

Macro scheduler.  Used it briefly.  I think it's cheaper than the
other two.  http://www.mjtnet.com/

AutoIt.  Freeware.  Since it's free, I'd use this one.  Not as user
friendly in that you'd need to code the script yourself instead of
recording your own clicks and keystrokes.  So it'll require more
legwork.  But still worth the cost IMO. 
http://www.hiddensoft.com/AutoIt/

One quick and dirty way to move data quickly from an Excel file into a
single table in a database is as follows.

Say you have a spreadsheet like this:

   A      B       C 
1  10     Bob     Bones
2  20     Greg    Green
3  35     Sally   Smith
4  54     Jenny   Jamison

1. Create a fomula in column D like this:
   ="insert into MyImportTable (SomeID, NameFirst, NameLast) values ("
& A1 & ", '" & B1 & "', '" & C1 & "')"

2. Copy that formula into all of the rows with data.

3. Highlight the column you put the formula in.

4. Paste the contents of the clipboard into a text file.

5. Run the text file from an interactive SQL session that is connected
to your database.

(optional) 
6. Run any procedures needed to translate, map, normalize, denormalize, log or
   otherwise transmogrify the data into your final form.


As far as automating the whole thing, there are plenty of quick and
dirty ways to approach this, but it will depend on what platforms,
tools etc. you have at your disposal.  Assuming you can get your data
in a consistent format, or set of formats, MS Access (don't laugh) can
be quickly programmed to import an Excel file into a table.  Then you
can either push the data into your database using ODBC or write a
query like the following (used for SQL Server insert statement
creation).

SELECT "insert into MyImportTable (SomeID, NameFirst, NameLast) values
(" & [SomeID] & "," & SqlString([NameFirst]) & "," &
SqlString([NameLast]) & ")" AS [/* Imported Data */]
FROM MyAccessTable;

Here's the helper function I reference in the query:

Function SqlString(s As String)
    If s = "" Then
        SqlString = "null"
    Else
        SqlString = s
        SqlString = Replace(SqlString, "'", "''")
        SqlString = Replace(SqlString, """", "' + char(34) + '")
        SqlString = "'" & SqlString & "'"
    End If
End Function

I have used this method to build quick and dirty import tools.  Create
a macro in Access that will delete from a table, import from Excel
into the table, export the query (selecting rows from table you just
imported, as above) to a text file and then Quit.  Run Access from a
batch file, calling your macro by name.  Then call isql from your
batch and pass in the name of the file that Access just created.

This sort of approach can get you up and running rapidly, but it is
not very robust and not a good long-term approach.  Good luck.

In step 3 above, you need to copy the column after you highlight it.

You never mentioned what your DB Backend is.  If you're running MS SQL
Server, check out DTS to do just this.  As long as your Excel layout
(or whatever file layout) doesn't change, you can easily build a
package or multiple packages to pull in the data directly, even with
some data massaging if necessary.

As another user suggested, Access can do this as well - linked tables
to Excel and your Database with a macro or two to copy/update the
data.  Not necessarily the easiest, but it works pretty well and
Access has a broad support community.  Only problem I see here would
be lack of easy automation.

-pete