Thread: converting .xls to delimited file

Is there any way to convert an Excel file (".xls")
to a delimited file? Maybe using sed?

            Bill

On Wed, Jan 10, 2001 at 11:22:46AM -0500, William Staniewicz wrote:
> Is there any way to convert an Excel file (".xls")
> to a delimited file? Maybe using sed?

You can export an excel file, from within excel, to tab delimited,
comma delimited, and many other formats. xls is a binary format, so
sed will not do the job.

--
Dr. David C. Merrill                     http://www.lupercalia.net
Linux Documentation Project                dmerrill@lupercalia.net
Collection Editor & Coordinator            http://www.linuxdoc.org
                                       Finger me for my public key

Three from the hall beneath the tree
    Is, Was, and Shall Be
Come Wyrd Sisters swoop to the ground
Loosen the web that binds us down
Join with the hands of the Weavers Three
    Is, Was, and Shall Be
        -- Is, Was, and Shall Be, Beverly Frederick

The easiest way to convert an Excel file to a
delimited file would be to open the file in Excel and
save it as "Formatted Text (Tab Delimited)" or "CSV
(Comma Delimited)" file.  If you need to make the
conversion on a *NIX box take a look at Gnumeric.  It
does a very good job (in my experience) of reading
Excel files, and it can then save those files in a
variety of text-based formats.

Once the file is in some sort of text based format you
can use a wide variety of tools to further process it
(including sed).

I hope this is helpful,
Jason Earl

--- William Staniewicz <wstan@localhostnl.demon.nl>
wrote:
> Is there any way to convert an Excel file (".xls")
> to a delimited file? Maybe using sed?
>
>             Bill
>


__________________________________________________
Do You Yahoo!?
Yahoo! Photos - Share your holiday photos online!
http://photos.yahoo.com/

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

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@iteso.mx>
Cc: <pgsql-general@postgresql.org>; <pgsql-novice@postgresql.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
>
>