Looking for a quick way to move data from ~1500 files that all have the exact same structure into an access database. I have just started my Google quest into this but I know there are a few excel gurus here that have helped in the past so I figured it was worth a shot. Any ideas?

The structure of the files are very basic -
Row 1: header column - about 15 columns
Row 2: Yes, No, a number or short comment.

5/27/2008 12:56:36 PM

what's the structure in Access? One big table? Multiple tables with foreign keys?

5/27/2008 1:55:05 PM

I have not created it yet but it will be the exact same layout as the spreadsheet.

5/27/2008 2:07:49 PM

It would be very easy to write an app that would do this, I wrote one internally here to do this exact same thing, but I cant send it out since its internal.

5/27/2008 2:59:00 PM

Any pointers or recommended things to read up on?

5/27/2008 3:10:19 PM

You could do this very easily if you know any scripting language.

Here's a good start if you're familiar with Python: http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/440661

5/27/2008 3:13:50 PM

uh you can do this with just VBA. seriously simple, create a data connection to your access DB, then write a super simple script to move the data

5/27/2008 3:40:35 PM

Or that. I know dick about VBA.

5/27/2008 3:42:49 PM

so Noen - if I do a bit of reading tonight you think I could pick up enough to pull this off?

Any sites you recommend I look into for a quick crash course?

5/27/2008 4:04:46 PM

THIS IS BUILT INTO ACCESS! The data import function supports Excel spreadsheets! (and it can be called repeatedly for a number of different worksheets through VBA)

Creating a macro to connect to the database, generate the tables, and loop through the spreadsheets adding the data is easy, but it is also slow. It took several minutes for around 30,000 rows for a particular database I worked with in the past. A data import took all of about 10 seconds, but unfortunately I needed to process the data as I added it.

[Edited on May 27, 2008 at 4:23 PM. Reason : ]

5/27/2008 4:22:46 PM

^also an excellent idea if you dont need to reformat anything

5/27/2008 4:45:47 PM