Convert a csv file to an xls format

Shell Programming and Scripting
1

Hi,

I have a file coming in xxx.txt(csv format) i do some work on it and i need to send out as a .xls format. Is there any way there is some code i can use in my script to convert this?

I'm struggling on this.
Thanks

2

See here

3

Excel should be able to read a file in csv format, if it's a xxx.txt file seperated by comma's you can just change the extension of the filename to xxx.csv.

Regards

4

I am still learning unix and don't know perl,python etc.?? I am using KSH and don't understand the link queries sent.

5

I need to change the extension to .xls not .csv as the outfile is formatted this way.

6

Expounding on what Franklin said, rename it to .csv, and read it in with Excel. Excel will automatically convert it when you File->Save As and select "xls" type.

7

I can do it manually but this is to be a automated process where the outfile .xls is sent via ftp. I wanted something in the script that will change the extension to .xls as we cannot manually change this extension everytime?

8

You are missing the point. You want to rename the file - somefile.txt -> somefile.xls
That is all you need to do. Excel will do the rest when a user opens it.

oldname="somefile.txt"
newname=${oldname%%.txt}".xls"
mv $oldname $newname
9

Jim,

That's not a good idea, if you rename a .txt or .csv file to an .xls file, Excel doesn't use the field separators and places every line in one column.

Regards

10

Thanks Jim,

however all info is in one column?

11

Here is a sample data file.

> cat infile.txt
joe   100.00     orange     1
steve 12.50      blue       3
bob   19.99      green      9
tony  87.13      white      2

I can then turn it into a csv file. Excel can naturally read a csv file. There will be no data formatting, but the data will be placed across the rows and columns - in this case, four columns of 4 rows of data. Below is the awk command to parse and create the csv file.

> cat make_csv
#! /usr/bin/bash

awk 'OFS="," {print $1,$2,$3,$4}' <infile.txt >outfile.csv

cp outfile.csv outfile.xls

The following csv will be opened by Excel nicely - putting into four columns and four rows. The user can keep in csv format, or save into xls format.

> cat outfile.csv
joe,100.00,orange,1
steve,12.50,blue,3
bob,19.99,green,9
tony,87.13,white,2

If the file is renamed to an xls fil extension, Excel will load the data, but will put it into only one column of four rows.

> cat outfile.xls
joe,100.00,orange,1
steve,12.50,blue,3
bob,19.99,green,9
tony,87.13,white,2
12

thanks to everyone for all there help on this.