Thread: Moving data from M$ JetDB file to Postgres on Linux

   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?

TIA,

Rich

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

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
Access2007+.  

I understand you run linux, but if you can get hold of a Windows machine with Access on it, or find someone who does,
youcan 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

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

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&keywords=>

Rich

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

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&keywords=>

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

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

FYI,
Pretty sure Apache Office Base has native support to open Access.mdb
files on a linux box