How to convert text to csv or xls?

[Geoff Wild]

I have a tab delimited file (in Unix of course) - is there a "free" way to convert it to csv or xls format?

This has to be done at the Unix level - not from Windows...needs to be part of a script...

Thanks...Geoff

[Sandman!]

Hi Geoff...without a sample input file here's my construct:

# tr "\t" "," outfile

cheers!

[Kent Ostby]

awk '{FS="\t"; for (idx=1;idx output.csv

[Geoff Wild]

Here's part of the file...

Basically, I want to open in Excel (or management does) and not have to do a "data" -> "Text to columns" every time....

Rgds...Geoff

[A. Clay Stephenson]

This is actually far from trivial. String fields must be enclosed in quotes. Because the string field might contain comma's the comma's need to be quoted as well. You must compose a valid header line.

I would look hard at the Text::CSV Perl module.

[someone_4]

Hi

I took your data file and imported in excel.
Was this what you where trying to do?

open excel

file
open - pick your file
Deleminited is checked
Next
Tab
Finish.

Richard

[James R. Ferguson]

Hi Geoff:

Try this:

# perl -pe 's/(.+?)\t+/"$1",/g;s/,([^"]+?)$/,"$1"/' filename

Regards!

...JRF...

[Patrice Le Guyader]

Hi,

I agree with Clay, I would look hard to Text::CSV Perl module and/or after I would use Spreadsheet::WriteExcel to create this file.

http://search.cpan.org/~alancitt/Text-CSV-0.01/CSV.pm
http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.16/lib/Spreadsheet/WriteExcel.pm

Hope this helps
Kenavo
Pat

[Geoff Wild]

Richard - yes - that's what I want it to look like - but without having to do in Excel...

Sandmans tr works fine.

Clay - yes - I've heard of that module - through google - I may have to look at it further..

James - yours works as well....

Kent - yours dropped the last column for some reason?

Rgds...Geoff

[Geoff Wild]

Wow - those perl modules are intense!

That is going to take me a wee bit to disect....

Is there a Perl Doctor in the house? :)

Here's my script so far:

#!/bin/sh
#
# EC-Inventory-Data-Collection script
# uses data gathered by /opt/AssetCentre/asset_reporting.ksh
# which is stored in /var/opt/AssetCentre/reports
#
#

RPTDIR=/var/opt/AssetCentre/reports
TMPFIL=/tmp/EC-Inventory-Data.txt
CSV=/htdocs/mambo/dmdocuments/Infrastructure/Server-Planning/EC-Inventory-Data-Collection.csv

if [ -f $CSV ] ;
then
cp -p $CSV $CSV.old
fi

cat $RPTDIR/label > $TMPFIL

for SERVER in `ls $RPTDIR/*.value`
do
cat $SERVER >> $TMPFIL
done

tr "\t" "," <$TMPFIL >$CSV



The SERVER.value (tab delimited) files are created from another script - all I want to do is gather all that individual info and put it in a spreadsheet...

Thanks...Geoff

[A. Clay Stephenson]

Note that you little tr stuff has done nothing to handle embedded quotes or embedded comma's nor has it done anything to enclose the string fields in quotes. You also haven't composed a header line, dealt with date fields, or described your numeric fields. There's a reason those Perl modules are "intense".

[Patrice Le Guyader]

Sorry if it's not what you expect to have.
You wanted a free way to create excel worksheet on unix.
From CPAN :
Spreadsheet::WriteExcel will work on the majority of Windows, UNIX and Macintosh platforms.

Regards
Pat

[Ivan Ferreira]

See this also:

http://sedition.com/perl/delim-to-excel.html
http://www-128.ibm.com/developerworks/library/l-pexcel/

[Sandman!]

To punctuate fields by double-quotes try the awk construct below:

# awk -F"\t" '{
> for (i=1;i<=NF;++i) printf((i> }' q='"' infile

cheers!

[Hein van den Heuvel]

Much the same question was recently discussed in under the somewhat vague title: "AWK - FS and output" :

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1008954

>> Is there a Perl Doctor in the house? :)

Several. What nationality would you prefer? :-)

>> Basically, I want to open in Excel (or management does) and not have to do a "data" -> "Text to columns" every time....

I feel your pain... but not theirs.
When I open a .TXT with Excel 'it' suggests to interpret it as tab-seperated and all I need to do is click 'finish'.
Moreover... once I am in a such spreadsheet I can just paste in new tab-seperated text and 'it does the right thing'

Cheers,
Hein.

[Andy Torres]

I know this is a really simplistic view of this subject, Geoff, but I just took your attachment from above and just renamed it file.xls and opened it in Excel and the columns kinda worked out.

If your file is built with the output of a script, maybe ou could tweak the output so that you can just save the output file with a name of blahblah.xls and then you could just email the attachment and viola!

I couldn't tell from the content what application you are using to extract that data. But as an example, PerfView used to have an export to csv option that allowed me to save graph output to files named blahblah.xls. OVPA extracts operate much the same way. The xls files opened right up. I still use the extract command to do my management graphs - no conversion tricks necessary. You just have to watch how you transfer the file. You may expect it to be a text file, but once you treat it as an xls file you need to transfer it binary. But it looks like your file has data extracted from a script, so that all may be a moot point.

Of course, if I knew squat about Perl, I think I'd lean toward that option. But I don't, so I try crazy stuff and sometimes it works. :-) I need to get into some Perl, man...

Good luck!

[H.Merijn Brand (procura]

You look away, and see what happens :)

Have you looked at my own module Spreadsheet::Read? It comes with xlscat, a small script to convert all kinds of spreadsheet like formats to plain text or csv

http://search.cpan.org/~hmbrand/Spreadsheet-Read-0.14/

It also has a util to show csv in a perl/Tk spreadsheet form.

If you also want a script to convert CSV to XLS, I can give that on request, as I use that on a daily basis.

Enjoy, Have FUN! H.Merijn

[Peter Godron · Accepted solution]

Geoff,
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=746822
discusses conversion to xls format as well. Solution seems to have been to use perl module.

[H.Merijn Brand (procura]

WTH , here's the csv2xls stuff ...

> csv2xls --help
usage: csv2xls [-s ] [-q ] [-w ] [-d ]
[-o ] [file.csv]
-s use as seperator char. Auto-detect, default = ';'
-q use as quotation char. Default = '"'
-w use as default minimum column width (4)
-o write output to file named , defaults
to input file name with .csv replaced with .xls
if from standard input, defaults to csv2xls.xls
-f force usage of if already exists (unlink before use)
-d use as date formats. Default = 'dd-mm-yyyy'

That should be enough for you :)

Enjoy, Have FUN! H.Merijn

[Geoff Wild]

I ended up using Spreadsheet::WriteExcel.

Had to also File-Temp-0.16 and Parse-RecDescent-1.94 following John's "method 1" instructions here:

http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.14/doc/install.html

Thanks all!

Rgds...Geoff