[thelist] excel migration to database

[Sean G.]

"Jay Blanchard" <jay.blanchard at niicommunications.com> wrote in message
news:C8F323573C030A448F3E5A2B6FE2070B01AFA923 at nemesis...
I am migrating several hundred excel spreadsheets to MySQL, a time
consuming process. Is anyone aware of a tool where I can batch save all
of the sheets as tab delimited files?
-- 

Howdy,

There are a couple different ways of getting data from Excel into a
database.  You can import or link a spreadsheet into Access and play from
there.  If you're on Windows, you can set up an Excel file as an ODBC data
source.  Since you are working with several hundred spreadsheets, neither of
those options is practical.

I think you're the right track saving the spreadsheet to flat files for
import into your database.  Excel does have an option to save a spreadsheet
as a tab-delimited txt file.  The question now is, how to do this several
hundred times.

I am not the Excel guru, but I would look into creating a macro or VBA
module that automatically on open saves the spreadsheet to a tab-delim file.
I know this can be done, although I don't know what kind of issues you face
if a spreadsheet has more than one worksheet.

Put that auto-save macro into a new spreadsheet, and leave that spreadsheet
open while you open each of your data source spreadsheets.  Use some script
or .bat file to go through all the files.  Since you probably don't want to
end up with hundreds of open Excel files, your macro should close each file
after saving as a tab-delim file.

Hopefully that puts you on the right track.  My recommendation is, get
yourself an intern or two and dump it on them.  If you are an intern, run.
=)

HTH,


Sean G.

-- 
"I'd like to bury my face in two dozen 'Krispy Kreme' glazed donuts & just
lie there & think about Hello Kitty!"