MSAccess to postgres on Windows Currency type problem

--001517477fdaf5754104acb1a65f
Content-Type: text/plain; charset=ISO-8859-1

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

--001517477fdaf5754104acb1a65f
Content-Type: text/html; charset=ISO-8859-1

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

--001517477fdaf5754104acb1a65f--

Re: MSAccess to postgres on Windows Currency type problem

On Sun, Sep 11, 2011 at 3:08 PM, albert 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.h tml#DATATYPE-NUMERIC-DECIMAL


--
Regards,
Richard Broersma Jr.

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

Re: MSAccess to postgres on Windows Currency type problem

--001517478f3229f92d04acb3bdc3
Content-Type: text/plain; charset=ISO-8859-1

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.

2011/9/11 Richard Broersma

> On Sun, Sep 11, 2011 at 3:08 PM, albert 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.h tml#DATATYPE-NUMERIC-DECIMAL
>
>
> --
> Regards,
> Richard Broersma Jr.
>

--001517478f3229f92d04acb3bdc3
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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


Additionally, I have tried to change that column type from within acces=
s to all numeric variants, even to text type,
prior to exporting it. Acc=
ess stops half way through complaining about "not enough memory",=
which doesn't make sense.

The table has over 620.000 rows.

Re: MSAccess to postgres on Windows Currency type problem

On Sun, Sep 11, 2011 at 5:37 PM, albert 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.

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

Re: MSAccess to postgres on Windows Currency type problem

--001517448076643d0504acc3a142
Content-Type: text/plain; charset=ISO-8859-1

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

2011/9/12 aihtdikh

> Hi Albert,
>
> Is Access able to do anything with the data in that column?
> For example, exporting the data to a .csv file, or even just creating
> another table with a copy of the same data.
>
> If mdbtools can read the data, but MSAccess itself cannot, I would suspect
> some sort of corruption in the mdb.
>
> Regards,
> Harun
>
>
> On 12/09/2011 08:37, albert 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.
>
> 2011/9/11 Richard Broersma
>
>> On Sun, Sep 11, 2011 at 3:08 PM, albert 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.h tml#DATATYPE-NUMERIC-DECIMAL
>>
>>
>> --
>> Regards,
>> Richard Broersma Jr.
>>
>
>
>

--001517448076643d0504acc3a142
Content-Type: text/html; charset=ISO-8859-1

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

Re: MSAccess to postgres on Windows Currency type problem

albert 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

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