Can i import data from either Access or Excel ?

Richard Baroniunas
Software Developer and DBA
Richard@Baronsoftware.com

Richard

>Can i import data from either Access or Excel ?

One off - Tim provides migrators and I think one is for Access (not sure because I've never used it)

Ongoing is a bit more tricky but if you can export from Access to a csv or xml file ElevateDB can import them.

There's no native support for Excel BUT I can recommend Mike Skolnik's comonents - www,scalabium.com - inexpensive but good. I use an older version which will doesn't handle the more recent Excel versions but Mike's latest does. His code is well written and well supported.

Roy Lambert

rbaroniunas

I have done fairly big imports from Excel. I usually save to CSV then use EDB's built in IMPORT TABLE syntax. All you have to do is to create a store in the DB which points to the folder holding the CSV file and fiddle with a few settings such as whether you want to import WITH HEADERS. It is not too complicated. I would recommend creating a test table and a test CSV file with just 3 or 4 columns first to play with.

Remember your CSV has to be EXACTLY rows and columns as per the rows and columns in the tables. If values are empty that is OK. Commas are used as delimiters. Modern versions of Excel and all versions of EDB are pretty good with text that contains commas. Where a value contains a comma that value is surrounded by double quotes, and IMPORT TABLE copes well, importing the data, but ignoring the double-quotes.

The main issues I have are with excel sometimes being lazy in formatting for example dates. This is usually a result of user incompetence. Prior to any import (especially large volumes of data) I would recommend using Excel to format the whole of each column in the data-type wanted, i.e. a column with a date, select it, then right-click "Format Cells ..." and pick "Date".

Work hard in excel to ensure the data is OK, and usually you are OK.

--

I did a big import from ACCESS (about 100,000 records) about 8 years back. From memory I think chose to export to Excel first ...

Richard,

<< Can i import data from either Access or Excel ? >>

As Roy asked, will this be a one-off or an on-going process ?  The best solution is almost always CSV files, due to their ubiquitous nature and the fact that they are easily created using almost any other product.

ElevateDB can import .csv files very quickly, and has formatting options to be able to handle almost any combination of date/time/number/boolean formats.

Tim Young
Elevate Software
www.elevatesoft.com