Postgres Pro
Downloads
Home   >   mailing lists

Thread: Access import

Access import

From
"Marc Zandvliet"
Date:
I'm trying to import a small Access db into a linux-based psql database via
a delimited text file. I've set up the table to reflect the fields etc. The
last field of the database is boolean which Access exports as 0 or 1. When I
try to import I get this:

mydb=> copy new_table from '/tmp/exported.txt' using delimiters '|';
'RROR:  Bad boolean external representation '1
mydb=>

I've checked the source text file and the last field doesn't have the '
before the 1.

Any suggestions? This should be straight forward right?

Thanks for any assistance,

Marc

Re: Access import

From
Tom Lane
Date:
"Marc Zandvliet" <marc@auroraworks.com> writes:
> I'm trying to import a small Access db into a linux-based psql database via
> a delimited text file. I've set up the table to reflect the fields etc. The
> last field of the database is boolean which Access exports as 0 or 1. When I
> try to import I get this:

> mydb=> copy new_table from '/tmp/exported.txt' using delimiters '|';
> 'RROR:  Bad boolean external representation '1
> mydb=>

> I've checked the source text file and the last field doesn't have the '
> before the 1.

Looks to me like your source text file is probably stored with DOS
newlines, ie CR/LF.  COPY is expecting Unix newlines (LF only) and
treats the CR characters as part of the data.  The boolean input
converter doesn't like garbage characters in its input data, and when
it complains, the CR is included in the error message, causing the
overstrike behavior you show.  The message is really

ERROR:  Bad boolean external representation '1<CR>'

Convert the file to Unix newline convention and you should be OK...

            regards, tom lane

RE: Access import

From
"Sokel, John"
Date:

I don't know if you have ODBC enabled or not, but I found this little SQL utility that transfers data from one source into another.  It saved me a tremendous amount of time pulling my data out of an Oracle database and pumped it right into my Postgres database.  There was no messing around with exporting and importing issues, just click and go. 

www.indus-soft.com/winsql

The professional version is required to do DB->DB moves, but it's only $100.

John

-----Original Message-----
From: Marc Zandvliet [mailto:marc@auroraworks.com]
Sent: Wednesday, August 30, 2000 1:43 PM
To: pgsql-novice@hub.org
Subject: [NOVICE] Access import

I'm trying to import a small Access db into a linux-based psql database via
a delimited text file. I've set up the table to reflect the fields etc. The
last field of the database is boolean which Access exports as 0 or 1. When I
try to import I get this:

mydb=> copy new_table from '/tmp/exported.txt' using delimiters '|';
'RROR:  Bad boolean external representation '1
mydb=>

I've checked the source text file and the last field doesn't have the '
before the 1.

Any suggestions? This should be straight forward right?

Thanks for any assistance,

Marc

Re: Access import

From
deval
Date:
Tom Lane wrote:
>
> "Marc Zandvliet" <marc@auroraworks.com> writes:
could gawk -F(field saperator) '{print $0 }' filename>filemame2  help?

Vijay

> > I'm trying to import a small Access db into a linux-based psql database via
> > a delimited text file.
>
> Convert the file to Unix newline convention and you should be OK...
>
>                         regards, tom lane