Обсуждение: MSAccess to postgres on Windows Currency type problem

Hi all,

I am exporting a MSAccess database to Postgres under windows using ODBC.
An Access table has a column with Currency data type.
When trying to export that table I get a popup message saying the ODBC call failed,
"the input syntax is invalid for type double precision <40868,8227>" .

(it's not a verbatim error message as I am running Spanish windows version)

I have tried to export that same table using mdbtools' mdb-schema program and it works correctly, translating it to postgres "money" data type.

Any ideas on how to fix this? I have not found any related settings on the ODBC driver applet.

I am running Microsoft Access 2010 and postgres 8.4.8-1

Thanks
Albert

On Sun, Sep 11, 2011 at 3:08 PM, albert <skarbat@gmail.com> wrote:
> I am exporting a MSAccess database to Postgres under windows using ODBC.
> "the input syntax is invalid for type double precision <40868,8227>" .
> I have tried to export that same table using mdbtools' mdb-schema program
> and it works correctly, translating it to postgres "money" data type.

the postgresql data type you want is NUMERIC.
http://www.postgresql.org/docs/9.1/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL


--
Regards,
Richard Broersma Jr.

The problem is how can I get MSAccess translate that column type properly?
Why would I need to change that manually? I have a bunch of columns of such type.
The process just stops half way through there's no way I can export that table.

Additionally, I have tried to change that column type from within access to all numeric variants, even to text type,
prior to exporting it. Access stops half way through complaining about "not enough memory", which doesn't make sense.
The table has over 620.000 rows.

On Sun, Sep 11, 2011 at 5:37 PM, albert <skarbat@gmail.com> wrote:
> The problem is how can I get MSAccess translate that column type properly?
> Why would I need to change that manually? I have a bunch of columns of such
> type.
> The process just stops half way through there's no way I can export that
> table.
>
> Additionally, I have tried to change that column type from within access to
> all numeric variants, even to text type,
> prior to exporting it. Access stops half way through complaining about "not
> enough memory", which doesn't make sense.
> The table has over 620.000 rows.

Have you considered using a ETL such as Kettle.  It can read access
files just fine and can make any data-type translations you need.

I'll need to wait until monday before I can take a look at the ODBC
drive for Postgresql to see if it has any parameters that may be of
help.


--
Regards,
Richard Broersma Jr.

Thanks Harun,

I could csv-export the table correctly, so no corruption apparently. I found a registry key called "LocksPerPage" that belongs to MS Office, after increasing it I was able to translate MSAcces Currency type to Integer successfully. Exporting through ODBC right after that was also successful. However that doesn't ring me a bell on how one can automagically export an MSAccess table through postgres odbc when having a column with Currency data type.

Thanks Richard for your suggestion.

Regards,
Albert

albert <skarbat@gmail.com> writes:
> I could csv-export the table correctly, so no corruption apparently. I found
> a registry key called "LocksPerPage" that belongs to MS Office, after
> increasing it I was able to translate MSAcces Currency type to Integer
> successfully. Exporting through ODBC right after that was also successful.
> However that doesn't ring me a bell on how one can automagically export an
> MSAccess table through postgres odbc when having a column with Currency data
> type.

It looks like you're using a locale in which the decimal separator is
comma rather than period.  Perhaps changing some locale setting on the
MSAccess side would help?

            regards, tom lane