Skip site navigation (1) Skip section navigation (2)

Re: converting .xls to delimited file

From: "rob" <rob(at)cabrion(dot)com>
To: "Alfonso Peniche" <alfonso(at)iteso(dot)mx>
Cc: <pgsql-general(at)postgresql(dot)org>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: converting .xls to delimited file
Date: 2001-01-11 12:20:26
Message-ID: 001901c07bc8$e1d915b0$4100fd0a@cabrion.org (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
Save it as .TXT (Tab Delimited) format.  When you copy the data into
postgres you may need to define nulls as ''.  Excel text output does not
distinguish between nulls and blanks.  By default, the copy command defines
nulls as \0 so you may need to change that.  I personally prefer that blanks
be imported as nulls.

I use the following copy command when importing:

copy mytable from '/path/input.txt' with nulls as '';

Also: watch your date formats (postgres is pretty good about this itself)
and sometimes your negative number formats.  (i.e. -5 is OK 5- is not OK!)

Finally, Excel has a tendency to export several "blank" rows at the end
(delimiters are correct, but all fields are blank).  I generally remove
these rows either before or after importing.

--rob



----- Original Message -----
From: "Alfonso Peniche" <alfonso(at)iteso(dot)mx>
Cc: <pgsql-general(at)postgresql(dot)org>; <pgsql-novice(at)postgresql(dot)org>
Sent: Wednesday, January 10, 2001 10:32 AM
Subject: Re: converting .xls to delimited file


> Try saving it as a .CSV file and then you can choose the delimiter you
> want.
>
> William Staniewicz wrote:
>
> > Is there any way to convert an Excel file (".xls")
> > to a delimited file? Maybe using sed?
> >
> >                         Bill
>
>


In response to

pgsql-novice by date

Next:From: John BurskiDate: 2001-01-11 15:08:52
Subject: Re: Comparison Operator problem
Previous:From: Brett W. McCoyDate: 2001-01-11 05:39:22
Subject: Re: Release connections in MODPERL

pgsql-general by date

Next:From: drevilDate: 2001-01-11 13:13:43
Subject: Found a bug in the procedural languages code relating to LIMIT 1
Previous:From: drevilDate: 2001-01-11 12:09:14
Subject: How to tell if that UPDATE worked?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group