Thread: converting .xls to delimited file
Is there any way to convert an Excel file (".xls")
to a delimited file? Maybe using sed?
Bill
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
Excel can do this for you, if you use File -> Save As...
You will find various file types available. The list will vary according to
the completeness of your Excel installation. I believe that at minimum you
should have "tab delimited text" available, and probably csv as well (don't
use csv, it's evil). If I'm wrong, you'll need to get the MS Office CD and
reinstall Excel with more options checked...
If you want to import the result into Postgres, beware of CR characters, and
leading/trailing spaces in your fields. Excel will tolerate those spaces in
non text-type fields, but Postgres will not.
> -----Original Message-----
> From: William Staniewicz [SMTP:wstan@localhostnl.demon.nl]
> Sent: Wednesday, January 10, 2001 10:23 AM
> To: pgsql-general@postgresql.org; pgsql-novice@postgresql.org
> Subject: [GENERAL] converting .xls to delimited file
>
> 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
>
>
Excel 2000 has two options:
1. Select "Save-As" and then select "Save at type" = "Text (Tab delimited)"
2. Select "Save-As" and then select "Save at type" = "CSV (Comma delimited)"
If I remember correctly, earlier versions of Excel have similar options.
Hope this helps.
Brian E. Pangburn
"William Staniewicz" <wstan@localhostnl.demon.nl> wrote in message
news:200101101026.f0AAQfB16474@mail.postgresql.org...
> Is there any way to convert an Excel file (".xls")
> to a delimited file? Maybe using sed?
>
> Bill
>
William Staniewicz wrote:
>
> Is there any way to convert an Excel file (".xls")
> to a delimited file? Maybe using sed?
forgot to mention, if you are using static filename/one time process you
could setup excel file as odbc datasource on windows computer and access
it via unixODBC (or some equivalent) on unix box. or the other way
around, send data from excel to postgres via postgres odbc interface.
--
Martin Lillepuu | E-mail: martin.lillepuu@mail.ee | GSM: 051 56 450
William Staniewicz wrote:
>
> Is there any way to convert an Excel file (".xls")
> to a delimited file? Maybe using sed?
search for xls2csv on freshmeat. it works for simpler excel files but
tends to segfault with complicated ones (with macros, etc) :(
if anyone currently knows some better tool for the task, i'm all ears.
for one idea, it would be really cool if somebody extracted the msoffice
filters from eq. openoffice and modified them to standalone csv/xml
conversion tool.
--
Martin Lillepuu | E-mail: martin.lillepuu@mail.ee | GSM: 051 56 450
On Thu, Jan 11, 2001 at 12:13:34AM +0200,
Martin Lillepuu <martin.lillepuu@mail.ee> wrote:
> William Staniewicz wrote:
> >
> > Is there any way to convert an Excel file (".xls")
> > to a delimited file? Maybe using sed?
>
> search for xls2csv on freshmeat. it works for simpler excel files but
> tends to segfault with complicated ones (with macros, etc) :(
>
> if anyone currently knows some better tool for the task, i'm all ears.
I currently get data for my database backed web pages from some other
people that keep it in excel files. What I am using to get at the data
is xlHtml-0.2.7.2.
I have tweaked it a bit. I have problems with dates showing up as numbers
and need to check for what are effectively Julian days for dates as well
as the more normal formats.
search freshmeat.net there is a tool called something like xls2xml that was
derived from the star office package. I haven't tried it, but looks very
good.
--rob
----- Original Message -----
From: "Martin Lillepuu" <martin.lillepuu@mail.ee>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, January 10, 2001 5:13 PM
Subject: Re: converting .xls to delimited file
> William Staniewicz wrote:
> >
> > Is there any way to convert an Excel file (".xls")
> > to a delimited file? Maybe using sed?
>
> search for xls2csv on freshmeat. it works for simpler excel files but
> tends to segfault with complicated ones (with macros, etc) :(
>
> if anyone currently knows some better tool for the task, i'm all ears.
>
> for one idea, it would be really cool if somebody extracted the msoffice
> filters from eq. openoffice and modified them to standalone csv/xml
> conversion tool.
>
> --
> Martin Lillepuu | E-mail: martin.lillepuu@mail.ee | GSM: 051 56 450
>