mdbtools-dev

[mdb-dev] Re: problems with JET4 and nonASCI characters

[Markus N. <ne...@it...>]

Hi again,

after the fix for the 64bit platform I can now export the data.
But I have the same problem as reported in
 http://sourceforge.net/mailarchive/forum.php?thread_id=3D7699603&forum_i=
d=3D5183

mdb-ver StazioniAutomatiche.mdb
JET4

mdb-export -I StazioniAutomatiche.mdb Misure
...
INSERT INTO Misure (Id Misura, Descrizione Misura, UM) VALUES (174,"Umidi=
t=C3=A0 Massima 1440 min","%")
...
INSERT INTO Misure (Id Misura, Descrizione Misura, UM) VALUES (6464,"Velo=
cit=C3=A0 vento Massima 15 min","m/s")
...

Do you see a chance to maintain the character encoding somehow or
map to ASCII? I am not sure if SQL is limited or permits accented
characters.

My intention is to convert the MDB into a PostgreSQL database.

Thanks

 Markus

Re: [mdb-dev] Re: problems with JET4 and nonASCI characters

[Brian B. <bri...@gm...>]

The source/target character sets can be selected using the MDB_ICONV
environment variable, otherwise the default is UTF-8 (this all applies
to jet4, access 97 is a different beast).  So what you're likely
seeing is UTF-8 characters on a terminal/program which is unequiped to
handle it.  Just set the target set like:

export MDB_ICONV=3DISO-8859-1

Which will work for most western european languages.  use "iconv
--list" to see the supported values.

Eventually it would be nice to grab this info from LC_ALL or LANG, but
the string returned by those doesn't always map exactly to that
required by iconv.

Brian

2005/8/5, Markus Neteler <ne...@it...>:
> Hi again,
>=20
> after the fix for the 64bit platform I can now export the data.
> But I have the same problem as reported in
>  http://sourceforge.net/mailarchive/forum.php?thread_id=3D7699603&forum_i=
d=3D5183
>=20
> mdb-ver StazioniAutomatiche.mdb
> JET4
>=20
> mdb-export -I StazioniAutomatiche.mdb Misure
> ...
> INSERT INTO Misure (Id Misura, Descrizione Misura, UM) VALUES (174,"Umidi=
t=E0 Massima 1440 min","%")
> ...
> INSERT INTO Misure (Id Misura, Descrizione Misura, UM) VALUES (6464,"Velo=
cit=E0 vento Massima 15 min","m/s")
> ...
>=20
> Do you see a chance to maintain the character encoding somehow or
> map to ASCII? I am not sure if SQL is limited or permits accented
> characters.
>=20
> My intention is to convert the MDB into a PostgreSQL database.
>=20
> Thanks
>=20
>  Markus
>=20
>=20
> -------------------------------------------------------
> SF.Net email is Sponsored by the Better Software Conference & EXPO
> September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practic=
es
> Agile & Plan-Driven Development * Managing Projects & Teams * Testing & Q=
A
> Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
> _______________________________________________
> mdbtools-dev mailing list
> mdb...@li...
> https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
>

Re: [mdb-dev] Re: problems with JET4 and nonASCI characters

[Markus N. <ne...@it...>]

On Fri, Aug 05, 2005 at 12:29:56PM -0400, Brian Bruns wrote:
> The source/target character sets can be selected using the MDB_ICONV
> environment variable, otherwise the default is UTF-8 (this all applies
> to jet4, access 97 is a different beast).  So what you're likely
> seeing is UTF-8 characters on a terminal/program which is unequiped to
> handle it.  Just set the target set like:
>=20
> export MDB_ICONV=3DISO-8859-1
>=20
> Which will work for most western european languages.  use "iconv
> --list" to see the supported values.

Phantastic - problem solved.

Would you mind to add this to the docs?

eden:doc[359.103] pwd
/hardmnt/eden0/ssi/software/mdbtools/doc
eden:doc[360.104] grep MDB_ICONV *
eden:doc[361.105]=20

=20
> Eventually it would be nice to grab this info from LC_ALL or LANG, but
> the string returned by those doesn't always map exactly to that
> required by iconv.

Ok.

Thanks, Brian.

Markus
=20
> Brian
>=20
> 2005/8/5, Markus Neteler <ne...@it...>:
> > Hi again,
> >=20
> > after the fix for the 64bit platform I can now export the data.
> > But I have the same problem as reported in
> >  http://sourceforge.net/mailarchive/forum.php?thread_id=3D7699603&for=
um_id=3D5183
> >=20
> > mdb-ver StazioniAutomatiche.mdb
> > JET4
> >=20
> > mdb-export -I StazioniAutomatiche.mdb Misure
> > ...
> > INSERT INTO Misure (Id Misura, Descrizione Misura, UM) VALUES (174,"U=
midit=E0 Massima 1440 min","%")
> > ...
> > INSERT INTO Misure (Id Misura, Descrizione Misura, UM) VALUES (6464,"=
Velocit=E0 vento Massima 15 min","m/s")
> > ...
> >=20
> > Do you see a chance to maintain the character encoding somehow or
> > map to ASCII? I am not sure if SQL is limited or permits accented
> > characters.
> >=20
> > My intention is to convert the MDB into a PostgreSQL database.
> >=20
> > Thanks
> >=20
> >  Markus
> >=20
> >=20
> > -------------------------------------------------------
> > SF.Net email is Sponsored by the Better Software Conference & EXPO
> > September 19-22, 2005 * San Francisco, CA * Development Lifecycle Pra=
ctices
> > Agile & Plan-Driven Development * Managing Projects & Teams * Testing=
 & QA
> > Security * Process Improvement & Measurement * http://www.sqe.com/bsc=
e5sf
> > _______________________________________________
> > mdbtools-dev mailing list
> > mdb...@li...
> > https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
> >
>=20
>=20
> -------------------------------------------------------
> SF.Net email is Sponsored by the Better Software Conference & EXPO
> September 19-22, 2005 * San Francisco, CA * Development Lifecycle Pract=
ices
> Agile & Plan-Driven Development * Managing Projects & Teams * Testing &=
 QA
> Security * Process Improvement & Measurement * http://www.sqe.com/bsce5=
sf
> _______________________________________________
> mdbtools-dev mailing list
> mdb...@li...
> https://lists.sourceforge.net/lists/listinfo/mdbtools-dev

--=20
Markus Neteler     <neteler itc it>       http://mpa.itc.it
ITC-irst -  Centro per la Ricerca Scientifica e Tecnologica
MPBA - Predictive Models for Biol. & Environ. Data Analysis
Via Sommarive, 18        -       38050 Povo (Trento), Italy

Re: [mdb-dev] Re: problems with JET4 and nonASCI characters

[Jeff S. <why...@ya...>]

--- Markus Neteler <ne...@it...> wrote:
> On Fri, Aug 05, 2005 at 12:29:56PM -0400, Brian Bruns wrote:
> > export MDB_ICONV=ISO-8859-1

> Phantastic - problem solved.
> 
> Would you mind to add this to the docs?

It is currently described in the 'HACKING' document, but it
really does need to go somewhere in 'user' documentation.

 -- Jeff Smith


		
____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

[mdb-dev] mdb-export and PostgreSQL

[Markus N. <ne...@it...>]

Hi,

I am currently trying to get MDB data into a PostgreSQL database.
Running

mdb-export -S -I StazioniAutomatiche.mdb Misure
...
INSERT INTO Misure (Id_Misura, Descrizione_Misura, UM) VALUES (236,"Tensione minima giornaliera batteria","V")
...

To make PostgreSQL happy, a semicolon is needed at the end of each
line (also reported:
 http://sourceforge.net/mailarchive/forum.php?thread_id=7234522&forum_id=5183
). I have patched src/util/mdb-export.c accordingly (line 185), but I feel
that this should go somehow into CVS.

A second problem is that all " (double) quotes need to be ' (single) quotes,
otherwise PostgreSQL won't insert. Not sure where to fix this one in 
the C code.

I have a shell workaround using tr and sed, but would prefer a fix in
the source code (to avoid future traps).

Thanks

 Markus

Re: [mdb-dev] mdb-export and PostgreSQL

[Brian B. <bri...@gm...>]

Hi,

The problem is that mdb-export was designed to export to CSV format in
which case " is the proper quoting character.  Somewhere along the
line somebody contributed the -I option.

There is a -R option to set the row delimiter.  So you could say something =
like:

mdb-export -I -R ';
' Northwind.mdb Shippers

and get the desired results, although I find this sort of a kludge
since mdb-export doesn't use the delimiter as a mask, everything is
taken literaly, so the preferable version:

mdb-export -I -R ';\n' Northwind.mdb Shippers

doesn't work as one would hope....that should be a simple fix, I'll
see if I can get to it this week.

Third option is to use good old sed (which you alluded to) like so:

mdb-export -I Northwind.mdb Shippers | sed -e 's/$/;/'

which will produce the desired results.

As for changing the quoting character, it should be configurable as
well.  Default should be " (double quote) when doing CSV style export
and ' (single quote) when using the -I switch.  I'll see if I can
knock this out something this week too.

Brian

2005/8/9, Markus Neteler <ne...@it...>:
> Hi,
>=20
> I am currently trying to get MDB data into a PostgreSQL database.
> Running
>=20
> mdb-export -S -I StazioniAutomatiche.mdb Misure
> ...
> INSERT INTO Misure (Id_Misura, Descrizione_Misura, UM) VALUES (236,"Tensi=
one minima giornaliera batteria","V")
> ...
>=20
> To make PostgreSQL happy, a semicolon is needed at the end of each
> line (also reported:
>  http://sourceforge.net/mailarchive/forum.php?thread_id=3D7234522&forum_i=
d=3D5183
> ). I have patched src/util/mdb-export.c accordingly (line 185), but I fee=
l
> that this should go somehow into CVS.
>=20
> A second problem is that all " (double) quotes need to be ' (single) quot=
es,
> otherwise PostgreSQL won't insert. Not sure where to fix this one in
> the C code.
>=20
> I have a shell workaround using tr and sed, but would prefer a fix in
> the source code (to avoid future traps).
>=20
> Thanks
>=20
>  Markus
>=20
>=20
>=20
> -------------------------------------------------------
> SF.Net email is Sponsored by the Better Software Conference & EXPO
> September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practic=
es
> Agile & Plan-Driven Development * Managing Projects & Teams * Testing & Q=
A
> Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
> _______________________________________________
> mdbtools-dev mailing list
> mdb...@li...
> https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
>

Re: [mdb-dev] mdb-export and PostgreSQL

[Markus N. <ne...@it...>]

On Tue, Aug 09, 2005 at 06:12:14PM -0400, Brian Bruns wrote:
> Hi,
> 
> The problem is that mdb-export was designed to export to CSV format in
> which case " is the proper quoting character.  Somewhere along the
> line somebody contributed the -I option.
> 
> There is a -R option to set the row delimiter.  So you could say something like:
> 
> mdb-export -I -R ';
> ' Northwind.mdb Shippers
> 
> and get the desired results, although I find this sort of a kludge
> since mdb-export doesn't use the delimiter as a mask, everything is
> taken literaly, so the preferable version:
> 
> mdb-export -I -R ';\n' Northwind.mdb Shippers
> 
> doesn't work as one would hope....that should be a simple fix, I'll
> see if I can get to it this week.
> 
> Third option is to use good old sed (which you alluded to) like so:
> 
> mdb-export -I Northwind.mdb Shippers | sed -e 's/$/;/'
> 
> which will produce the desired results.
> 
> As for changing the quoting character, it should be configurable as
> well.  Default should be " (double quote) when doing CSV style export
> and ' (single quote) when using the -I switch.  I'll see if I can
> knock this out something this week too.

This would be cool. For now I used

 mdb-export -S -I StazioniAutomatiche.mdb Misure | sed 's+$+;+g'| tr '"' "'" | psql meteo

(see the nice 'tr' trick  :-).

Markus  

> Brian
> 
> 2005/8/9, Markus Neteler <ne...@it...>:
> > Hi,
> > 
> > I am currently trying to get MDB data into a PostgreSQL database.
> > Running
> > 
> > mdb-export -S -I StazioniAutomatiche.mdb Misure
> > ...
> > INSERT INTO Misure (Id_Misura, Descrizione_Misura, UM) VALUES (236,"Tensione minima giornaliera batteria","V")
> > ...
> > 
> > To make PostgreSQL happy, a semicolon is needed at the end of each
> > line (also reported:
> >  http://sourceforge.net/mailarchive/forum.php?thread_id=7234522&forum_id=5183
> > ). I have patched src/util/mdb-export.c accordingly (line 185), but I feel
> > that this should go somehow into CVS.
> > 
> > A second problem is that all " (double) quotes need to be ' (single) quotes,
> > otherwise PostgreSQL won't insert. Not sure where to fix this one in
> > the C code.
> > 
> > I have a shell workaround using tr and sed, but would prefer a fix in
> > the source code (to avoid future traps).
> > 
> > Thanks
> > 
> >  Markus
> > 
> > 
> > 
> > -------------------------------------------------------
> > SF.Net email is Sponsored by the Better Software Conference & EXPO
> > September 19-22, 2005 * San Francisco, CA * Development Lifecycle Practices
> > Agile & Plan-Driven Development * Managing Projects & Teams * Testing & QA
> > Security * Process Improvement & Measurement * http://www.sqe.com/bsce5sf
> > _______________________________________________
> > mdbtools-dev mailing list
> > mdb...@li...
> > https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
> >

-- 
Markus Neteler     <neteler itc it>       http://mpa.itc.it
ITC-irst -  Centro per la Ricerca Scientifica e Tecnologica
MPBA - Predictive Models for Biol. & Environ. Data Analysis
Via Sommarive, 18        -       38050 Povo (Trento), Italy

Re: [mdb-dev] Re: problems with JET4 and nonASCI characters

[Brian B. <bri...@gm...>]

Doh!

I thought it was in the user guide, but you're right it's not.  I will
add it very soon.

The unix world will be a easier place once everyone is on utf8.

Brian

2005/8/8, Markus Neteler <ne...@it...>:
> On Fri, Aug 05, 2005 at 12:29:56PM -0400, Brian Bruns wrote:
> > The source/target character sets can be selected using the MDB_ICONV
> > environment variable, otherwise the default is UTF-8 (this all applies
> > to jet4, access 97 is a different beast).  So what you're likely
> > seeing is UTF-8 characters on a terminal/program which is unequiped to
> > handle it.  Just set the target set like:
> >
> > export MDB_ICONV=3DISO-8859-1
> >
> > Which will work for most western european languages.  use "iconv
> > --list" to see the supported values.
>=20
> Phantastic - problem solved.
>=20
> Would you mind to add this to the docs?
>=20
> eden:doc[359.103] pwd
> /hardmnt/eden0/ssi/software/mdbtools/doc
> eden:doc[360.104] grep MDB_ICONV *
> eden:doc[361.105]
>=20
>=20
> > Eventually it would be nice to grab this info from LC_ALL or LANG, but
> > the string returned by those doesn't always map exactly to that
> > required by iconv.
>=20
> Ok.
>=20
> Thanks, Brian.
>=20
> Markus
>=20
> > Brian
> >
> > 2005/8/5, Markus Neteler <ne...@it...>:
> > > Hi again,
> > >
> > > after the fix for the 64bit platform I can now export the data.
> > > But I have the same problem as reported in
> > >  http://sourceforge.net/mailarchive/forum.php?thread_id=3D7699603&for=
um_id=3D5183
> > >
> > > mdb-ver StazioniAutomatiche.mdb
> > > JET4
> > >
> > > mdb-export -I StazioniAutomatiche.mdb Misure
> > > ...
> > > INSERT INTO Misure (Id Misura, Descrizione Misura, UM) VALUES (174,"U=
midit=E0 Massima 1440 min","%")
> > > ...
> > > INSERT INTO Misure (Id Misura, Descrizione Misura, UM) VALUES (6464,"=
Velocit=E0 vento Massima 15 min","m/s")
> > > ...
> > >
> > > Do you see a chance to maintain the character encoding somehow or
> > > map to ASCII? I am not sure if SQL is limited or permits accented
> > > characters.
> > >
> > > My intention is to convert the MDB into a PostgreSQL database.
> > >
> > > Thanks
> > >
> > >  Markus
> > >
> > >
> > > -------------------------------------------------------
> > > SF.Net email is Sponsored by the Better Software Conference & EXPO
> > > September 19-22, 2005 * San Francisco, CA * Development Lifecycle Pra=
ctices
> > > Agile & Plan-Driven Development * Managing Projects & Teams * Testing=
 & QA
> > > Security * Process Improvement & Measurement * http://www.sqe.com/bsc=
e5sf
> > > _______________________________________________
> > > mdbtools-dev mailing list
> > > mdb...@li...
> > > https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
> > >
> >
> >
> > -------------------------------------------------------
> > SF.Net email is Sponsored by the Better Software Conference & EXPO
> > September 19-22, 2005 * San Francisco, CA * Development Lifecycle Pract=
ices
> > Agile & Plan-Driven Development * Managing Projects & Teams * Testing &=
 QA
> > Security * Process Improvement & Measurement * http://www.sqe.com/bsce5=
sf
> > _______________________________________________
> > mdbtools-dev mailing list
> > mdb...@li...
> > https://lists.sourceforge.net/lists/listinfo/mdbtools-dev
>=20
> --
> Markus Neteler     <neteler itc it>       http://mpa.itc.it
> ITC-irst -  Centro per la Ricerca Scientifica e Tecnologica
> MPBA - Predictive Models for Biol. & Environ. Data Analysis
> Via Sommarive, 18        -       38050 Povo (Trento), Italy
>