pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExport Access 97 to PostgreSQL
9+ messages / 6 participants
[nested] [flat]
* Export Access 97 to PostgreSQL
@ 2008-03-26 11:46 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 3 replies; 9+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-03-26 11:46 UTC (permalink / raw)
To: pgsql-sql
Hello
I have a db in MS Access 97 and now i have to import the data in PostgreSQL. I can create the table structure in PostgreSql but in what format can i export the table from Access so Postgresql can read it?
Thanks
Shavonne
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Export Access 97 to PostgreSQL
@ 2008-03-26 16:15 Shane Ambler <pgsql@Sheeky.Biz>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2 siblings, 0 replies; 9+ messages in thread
From: Shane Ambler @ 2008-03-26 16:15 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
Shavonne Marietta Wijesinghe wrote:
> Hello
>
> I have a db in MS Access 97 and now i have to import the data in
> PostgreSQL. I can create the table structure in PostgreSql but in
> what format can i export the table from Access so Postgresql can read
> it?
csv would be the most common and easiest.
Basically any structured text file can be used as you have the option of
specifying what characters are used to separate fields etc when you
import to postgresql.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Export Access 97 to PostgreSQL
@ 2008-03-26 16:21 Shawn <postgres@xmtservices.net>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2 siblings, 1 reply; 9+ messages in thread
From: Shawn @ 2008-03-26 16:21 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 26 March 2008 11:46:43 Shavonne Marietta Wijesinghe wrote:
> Hello
>
> I have a db in MS Access 97 and now i have to import the data in
> PostgreSQL. I can create the table structure in PostgreSql but in what
> format can i export the table from Access so Postgresql can read it?
>
> Thanks
>
> Shavonne
Hello Shavonne,
I'm no expert but I have often used either the tab or comma delimited format.
It requires that the fields in the PostgreSQL table are in the same order as
the original and that the data types are formatted similarly, but it has
worked for me. Also a sed and awk'ing of the results can clean up alot of
formatting issues.
example, one of my fields is in the timestamp with time zone format but MS-SQL
doesn't differentiate Time Zones, at least not in the database I'm pulling
from. I use this line to add the UTC to the field " sed 's/\t/ UTC\t/1'".
Shawn
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Export Access 97 to PostgreSQL
@ 2008-03-27 09:03 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Shawn <postgres@xmtservices.net>
0 siblings, 1 reply; 9+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-03-27 09:03 UTC (permalink / raw)
To: Shawn <postgres@xmtservices.net>; pgsql-sql
Thanks. I tried the following command
COPY REGIONI FROM
'C:/Inetpub/wwwroot2/FORMWebSite1/database/FormAjax/REGIONI.copy' WITH
DELIMITER '\t';
But i get this error
ERROR: relation "regioni" does not exist
Here is a sample of my file (fields seperated with a tab)
13600770000,00 848351,00 2,00 2,00 4,00 "Trentino-alto adige"
18424000000,00 1132202,00 3,00 3,00 5,00 "Veneto"
7859928000,00 746082,90 4,00 4,00 6,00 "Friuli-venezia giulia"
What can the problem be?
Shavonne
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Export Access 97 to PostgreSQL
@ 2008-03-27 09:54 Ivan Sergio Borgonovo <mail@webthatworks.it>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 9+ messages in thread
From: Ivan Sergio Borgonovo @ 2008-03-27 09:54 UTC (permalink / raw)
To: pgsql-sql; +Cc: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
On Thu, 27 Mar 2008 10:03:57 +0100
"Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
wrote:
> Thanks. I tried the following command
>
> COPY REGIONI FROM
> 'C:/Inetpub/wwwroot2/FORMWebSite1/database/FormAjax/REGIONI.copy'
> WITH DELIMITER '\t';
>
> But i get this error
>
> ERROR: relation "regioni" does not exist
You've to create the REGIONI table.
You may (or may not) get into trouble with locale. I noticed you're
using , as a decimal separator.
So if you're still encountering problems... that could be the second
thing to look at.
> Here is a sample of my file (fields seperated with a tab)
> 13600770000,00 848351,00 2,00 2,00 4,00 "Trentino-alto adige"
> 18424000000,00 1132202,00 3,00 3,00 5,00 "Veneto"
> 7859928000,00 746082,90 4,00 4,00 6,00 "Friuli-venezia giulia"
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Export Access 97 to PostgreSQL
@ 2008-03-27 10:06 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 1 reply; 9+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-03-27 10:06 UTC (permalink / raw)
To: Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-sql
Strange coz i created the table. Why doesn't it seem to find it??
CREATE TABLE "REGIONI"
(
"AREA" int4,
"PERIMETER" int4,
"REGIONI_" int4,
"REGIONI_ID" int4,
"COD_REG" int4,
"REGIONE" text
)
WITHOUT OIDS;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Export Access 97 to PostgreSQL
@ 2008-03-27 10:14 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 1 reply; 9+ messages in thread
From: Marcin Stępnicki @ 2008-03-27 10:14 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-sql
On Thu, Mar 27, 2008 at 11:06 AM, Shavonne Marietta Wijesinghe
<shavonne.marietta@studioform.it> wrote:
> COPY REGIONI FROM
(...)
> Strange coz i created the table. Why doesn't it seem to find it??
>
> CREATE TABLE "REGIONI"
You need to quote the name in your COPY command as well.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Export Access 97 to PostgreSQL
@ 2008-03-27 10:16 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Marcin Stępnicki <mstepnicki@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-03-27 10:16 UTC (permalink / raw)
To: Marcin Stepnicki <mstepnicki@gmail.com>; +Cc: pgsql-sql
Thanks.. So it was a uppercase problem ;)
Shavonne.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Export Access 97 to PostgreSQL
@ 2008-03-27 12:16 Roberto Spier <roberto.spier@conab.gov.br>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2 siblings, 0 replies; 9+ messages in thread
From: Roberto Spier @ 2008-03-27 12:16 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
Shavonne Marietta Wijesinghe escreveu:
> Hello
>
> I have a db in MS Access 97 and now i have to import the data in
> PostgreSQL. I can create the table structure in PostgreSql but in what
> format can i export the table from Access so Postgresql can read it?
>
> Thanks
>
> Shavonne
Export the data, instead! You'd connect (link) to the postgreSQL
database from within access mdb, then run an acc's INSERT INTO query.
hth
--
Spier
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2008-03-27 12:16 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-26 11:46 Export Access 97 to PostgreSQL Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-03-26 16:15 ` Shane Ambler <pgsql@Sheeky.Biz>
2008-03-26 16:21 ` Shawn <postgres@xmtservices.net>
2008-03-27 09:03 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-03-27 09:54 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2008-03-27 10:06 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-03-27 10:14 ` Marcin Stępnicki <mstepnicki@gmail.com>
2008-03-27 10:16 ` Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-03-27 12:16 ` Roberto Spier <roberto.spier@conab.gov.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox