Thread: Access 97 Database and Postgres
I need to get data out of an Access database and into a *real* database,
i.e., Postgres. I've done the ODBC thing. It was painful to set up
(did you know MS has _bugs_ in their ODBC support?? *gasp!*) and even
more painful exporting the data into Postgres. Some tables failed to
export, plus I ended up having to do a pg_dump, massage the schema *and*
the data and reload it all anyway.
Is there a better way? (Even just _slightly_ better?) During our
migraineXXXXXXXXX migration I will need to transfer the data a few more
times and I really don't want to make a career out of exporting data...
Thanks for any help.
-cw-
--
Charlie Wilkinson - TRIS Development Systems Administrator
IS:SD:CT:CC:TD
Phone: 202-283-3241
MSMail: Charlie E Wilkinson
SMTP: cwilkins@tris.irs.gov
Home: cwilkins@boinklabs.com
This message constructed from 90% post-consumer electrons.
On Wed, 26 Jul 2000, Wilkinson Charlie E wrote:
> Is there a better way? (Even just _slightly_ better?) During our
> migraineXXXXXXXXX migration I will need to transfer the data a few more
> times and I really don't want to make a career out of exporting data...
I've had better success exporting the data out of Access in some sort of
delimited format, creating my own schema in Postgres, then importing the
data in via COPY. Trying to do it with ODBC was just a pain the arse,
slow, and VERY unpredictable.
You could also autmoate this somewhat by writing a Perl script or
something similar and querying the data out of Access (via ODBC, of
course), and then putting it directly into Postgres via DBI. This way you
can massage data in between if necessary.
Brett W. McCoy
http://www.chapelperilous.net/~bmccoy/
-------------------------------------------------------------------------------
"If the code and the comments disagree, then both are probably wrong."
-- Norm Schryer
> -----Original Message-----
> From: bmccoy@chapelperilous.net
>
[Exporting data from Access...]
>
> Trying to do it with ODBC was just a pain the arse,
> slow, and VERY unpredictable.
That pretty much describes my experience. All that and "Hotfixes" too.
Yeesh... I started down that road because I thought it would be easier.
*cough*
Did you know that Access 2000 ships so broken that if you try to
export a table via ODBC it starts out with something like:
CREATE TABLE foo ( varchar(20), int4, float8 )
Naturally Postgres had some issues with the lack of column names. So
I'm wondering when Postgres will be fixed to handle this new MS
standard?? ;-)
(Seriously, if any masochistic individual on the list wants the
MS hotfixes for that, drop me a note.)
> You could also autmoate this somewhat by writing a Perl script or
> something similar and querying the data out of Access (via ODBC, of
> course), and then putting it directly into Postgres via DBI.
> This way you
> can massage data in between if necessary.
Mmm?? Now you've really got my ear. You're saying I could hack up
something in Perl on the Linux box to snarf data out of the Access
database on the Windoze box via ODBC? (I just assumed that the
Windoze ODBC stuff would be client-only.) I've already done some DBI
stuff with Postgres, so I'm not completely out in the dark here.
Provided I wouldn't have to code up anything _too_ massive, this
sounds like something I very much wish I had known a couple of
months ago!
Thanks Brett.
-cw-
--
Charlie Wilkinson - TRIS Development Systems Administrator
IS:SD:CT:CC:TD
Phone: 202-283-3241
MSMail: Charlie E Wilkinson
SMTP: cwilkins@tris.irs.gov
Home: cwilkins@boinklabs.com
This message constructed from 90% post-consumer electrons.
On Wed, 26 Jul 2000, Wilkinson Charlie E wrote:
> Mmm?? Now you've really got my ear. You're saying I could hack up
> something in Perl on the Linux box to snarf data out of the Access
> database on the Windoze box via ODBC? (I just assumed that the
> Windoze ODBC stuff would be client-only.) I've already done some DBI
> stuff with Postgres, so I'm not completely out in the dark here.
Check out www.unixodbc.com -- you'll have to set up an ODBC-ODBC bridge to
connect to Access, but it can be done. There's also an ODBC module for
Perl on the Windows side, but then you're still stuck with using ODBC to
connect back to Postgres. But it's Perl -- it's much faster, smarter and
cooler than having do it inside of Access or with Visual Basic!
Brett W. McCoy
http://www.chapelperilous.net/~bmccoy/
-------------------------------------------------------------------------------
Drop that pickle!