Moving data from M$ JetDB file to Postgres on Linux

On 02/28/2014 03:21 PM, Rich Shepard wrote:

I just downloaded two scientific data files from a federal agency's Web
site. Both are in M$ JetDB format. I run only linux and keep all my
scientific dat in postgres.

My Web search did not turn up anything useful; the closest was a thread
from this mail list in 2000 on how to send a postgres query through odbc to
an Access database.

Is there a filter I can use to get the data from these files?

The only software I could find to directly read MDB files on Linux is :

http://mdbtools.sourceforge.net/

Not sure what version of MDB you have and whether the above is current
enough to read them. Otherwise you need to crank up an Access instance
and go from there.

TIA,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 28 Feb 2014, Adrian Klaver wrote:

The only software I could find to directly read MDB files on Linux is :
http://mdbtools.sourceforge.net/
Not sure what version of MDB you have and whether the above is current enough
to read them. Otherwise you need to crank up an Access instance and go from
there.

Adrian,

I have mdbtools which I used on Access files in the past. I was not aware
that JetDB was the same data format.

And, I discovered 'jet-tool' on code.google.com. That'll do the job, I
believe.

Carpe weekend,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 28 Feb 2014, Rich Shepard wrote:

And, I discovered 'jet-tool' on code.google.com. That'll do the job, I
believe.

Well, it won't work: it's a M$ .exe file and the source is apparently in
some M$-type of Pascal.

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Rich Shepard wrote on 01.03.2014 00:21:

I just downloaded two scientific data files from a federal agency's Web
site. Both are in M$ JetDB format. I run only linux and keep all my
scientific dat in postgres.

My Web search did not turn up anything useful; the closest was a thread
from this mail list in 2000 on how to send a postgres query through odbc to
an Access database.

Is there a filter I can use to get the data from these files?

It's not clear to me what exactly you are trying to do.

Are you trying to query the Access database from within Postgres?

Or are you just trying to copy the data into the Postgres database?

If the latter you could try one of the JDBC based query tools to export
or copy the data using the UCanAccess JDBC driver for MS Access:

http://sourceforge.net/projects/ucanaccess/

UCanAccess works quite well with the tool I am maintaining as far as I can tell.
The WbCopy command or the DbExplorer should be able to copy the data over to Postgres.

http://www.sql-workbench.net/
http://www.sql-workbench.net/manual/command-copy.html
http://www.sql-workbench.net/manual/data-pumper.html

Another alternative would be to export the Access database to flat files and import those into Postgres.

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/28/2014 03:58 PM, Rich Shepard wrote:

On Fri, 28 Feb 2014, Adrian Klaver wrote:

The only software I could find to directly read MDB files on Linux is :
http://mdbtools.sourceforge.net/
Not sure what version of MDB you have and whether the above is current
enough to read them. Otherwise you need to crank up an Access instance
and go from there.

Adrian,

I have mdbtools which I used on Access files in the past. I was not
aware
that JetDB was the same data format.

Well JET is the database engine for Access and mdb is one of the file
format extensions for Access database files. JET has been used in other
MS products and Access has used different file formats(extensions) over
the years. So you may be in for a chore depending on what files you
actually have. Might be worth it to do some exploring and see if the
data is available in a more useable form.

Carpe weekend,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, 1 Mar 2014, Thomas Kellerer wrote:

It's not clear to me what exactly you are trying to do.
Or are you just trying to copy the data into the Postgres database?

If the latter you could try one of the JDBC based query tools to export
or copy the data using the UCanAccess JDBC driver for MS Access:

Well, if I had any Microsoft software on a system that might work well.
However, I've used nothing but F/OSS on linux since 1997.

Or, are you saying that from within psql on my linux system I can access
the data in the .mdb?

What I want to do is extract the data from the JetDB as SQL or otherwise
get it out of the proprietary format so I can store and manipulate it in
postgres and analyze it with R.

I'll grab the latest mdbtools and see if that works. I did not realize
before that the JetDB has the same file format as Access.

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 28 Feb 2014, Adrian Klaver wrote:

Well JET is the database engine for Access and mdb is one of the file
format extensions for Access database files. JET has been used in other MS
products and Access has used different file formats(extensions) over the
years. So you may be in for a chore depending on what files you actually
have. Might be worth it to do some exploring and see if the data is
available in a more useable form.

Adrian,

I'll try my mdbtools and see if there's a more current version.

The data came from the USGS and they have it in only one format,
unfortunately.

Thanks,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Rich Shepard wrote on 01.03.2014 01:33:

On Sat, 1 Mar 2014, Thomas Kellerer wrote:

It's not clear to me what exactly you are trying to do.
Or are you just trying to copy the data into the Postgres database?

If the latter you could try one of the JDBC based query tools to export
or copy the data using the UCanAccess JDBC driver for MS Access:

Well, if I had any Microsoft software on a system that might work well.
However, I've used nothing but F/OSS on linux since 1997.

Or, are you saying that from within psql on my linux system I can access
the data in the .mdb?

No, not within psql, but from within a Java/JDBC based query tool (such as my SQL Workbench)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2014-03-01 9:34 GMT+09:00 Rich Shepard <rshepard@appl-ecosys.com>:

On Fri, 28 Feb 2014, Adrian Klaver wrote:

Well JET is the database engine for Access and mdb is one of the file
format extensions for Access database files. JET has been used in other MS
products and Access has used different file formats(extensions) over the
years. So you may be in for a chore depending on what files you actually
have. Might be worth it to do some exploring and see if the data is
available in a more useable form.

Adrian,

I'll try my mdbtools and see if there's a more current version.

The data came from the USGS and they have it in only one format,
unfortunately.

Not sure if this will be of use, but there are a couple of links here:

https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_Access

I'll post it anyway in case someone comes across this thread in the future.

Regards

Ian Barwick

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 28 Feb 2014, Adrian Klaver wrote:

Well JET is the database engine for Access and mdb is one of the file
format extensions for Access database files. JET has been used in other MS
products and Access has used different file formats(extensions) over the
years. So you may be in for a chore depending on what files you actually
have. Might be worth it to do some exploring and see if the data is
available in a more useable form.

Adrian,

I'll make life easier for myself and find a different data set. I need
example data to analyze and it doesn't matter what data set I use.

Thanks,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, 1 Mar 2014, Thomas Kellerer wrote:

No, not within psql, but from within a Java/JDBC based query tool (such as my
SQL Workbench)

Oh. OK.

Thanks,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, 1 Mar 2014, Ian Lawrence Barwick wrote:

Not sure if this will be of use, but there are a couple of links here:

https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_Access

I'll post it anyway in case someone comes across this thread in the future.

Ian,

Thanks. I'll look at it.

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/28/2014 04:10 PM, Thomas Kellerer wrote:

Rich Shepard wrote on 01.03.2014 00:21:

I just downloaded two scientific data files from a federal agency's
Web
site. Both are in M$ JetDB format. I run only linux and keep all my
scientific dat in postgres.

My Web search did not turn up anything useful; the closest was a
thread
from this mail list in 2000 on how to send a postgres query through
odbc to
an Access database.

Is there a filter I can use to get the data from these files?

It's not clear to me what exactly you are trying to do.

Are you trying to query the Access database from within Postgres?

Or are you just trying to copy the data into the Postgres database?

If the latter you could try one of the JDBC based query tools to export
or copy the data using the UCanAccess JDBC driver for MS Access:

http://sourceforge.net/projects/ucanaccess/

That is one I missed. Thanks for the heads up.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/28/2014 04:34 PM, Rich Shepard wrote:

On Fri, 28 Feb 2014, Adrian Klaver wrote:

Well JET is the database engine for Access and mdb is one of the file
format extensions for Access database files. JET has been used in
other MS
products and Access has used different file formats(extensions) over the
years. So you may be in for a chore depending on what files you actually
have. Might be worth it to do some exploring and see if the data is
available in a more useable form.

Adrian,

I'll try my mdbtools and see if there's a more current version.

The data came from the USGS and they have it in only one format,
unfortunately.

What dataset?

My Dad is a geologist and I remember downloading datasets from the USGS
for him, in a variety of formats.

Thanks,

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 28 Feb 2014 16:03:39 -0800 (PST)
Rich Shepard <rshepard@appl-ecosys.com> wrote:

And, I discovered 'jet-tool' on code.google.com. That'll do the job, I
believe.

Well, it won't work: it's a M$ .exe file and the source is apparently in
some M$-type of Pascal.

I've had success with mdbtools, but on older version of Jet database files and small files. I've haven't tested it on Access 2007+.

I understand you run linux, but if you can get hold of a Windows machine with Access on it, or find someone who does, you can always export to text and use that. You can use recent versions of the software to open older files usually.

Perl's DBI.pm is also handy to extract data, on a Windows machine with the proper drivers.

--
http://libremen.com
Gestión de litigios y de expedientes de seguros de siniestros para el servicio jurídico

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, 1 Mar 2014, Vincent Veyron wrote:

I've had success with mdbtools, but on older version of Jet database files
and small files. I've haven't tested it on Access 2007+.

Vincent,

I suspect this file is in the current Access version.

I understand you run linux, but if you can get hold of a Windows machine
with Access on it, or find someone who does, you can always export to text
and use that. You can use recent versions of the software to open older
files usually.

Of course. :-) But, I don't know anyone (other than my sister) who uses
Windoze so I decided to look for a different data set.

The specific data do not matter, I need them for sample analyses for a
short course I'll be offering. My only criteria are the data be
environmental rather than economic, medical, or social and that I do not use
any client's data.

There's lots of public domain data on the Web so I'll find something
useful.

Much appreciated,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 28 Feb 2014, John McKown wrote:

Is this data generally available? If so, then can you tell us how to get a
copy of the data? If we can get a copy, it might be able to figure out how
to read it.

John,

Here's the URL:
<http://www.streamnet.org/datastore_search.cfm?id=410&amp;keywords=&gt;

Rich

--
Richard B. Shepard, Ph.D. | Have knowledge, will travel.
Applied Ecosystem Services, Inc. |
<http://www.appl-ecosys.com&gt; Voice: 503-667-4517 Fax: 503-667-8863

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/01/2014 06:41 AM, Rich Shepard wrote:

On Fri, 28 Feb 2014, John McKown wrote:

Is this data generally available? If so, then can you tell us how to
get a
copy of the data? If we can get a copy, it might be able to figure out
how
to read it.

John,

Here's the URL:
<http://www.streamnet.org/datastore_search.cfm?id=410&amp;keywords=&gt;

FYI in the Summary / Abstract at the above link there is this:

"If you would like these data in a different format or would like help
in using this file, please contact.." <contact info>

Rich

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, 1 Mar 2014, Adrian Klaver wrote:

FYI in the Summary / Abstract at the above link there is this:

"If you would like these data in a different format or would like help in
using this file, please contact.." <contact info>

I missed seeing that. Thanks.

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general