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 >