topic Re: How to convert text to csv or xls? in Operating System - Linux

How to convert text to csv or xls?

Geoff Wild — Wed, 29 Mar 2006 16:40:04 GMT

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

Re: How to convert text to csv or xls?

Sandman! — Wed, 29 Mar 2006 16:43:38 GMT

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

# tr "\t" "," outfile

cheers!

Re: How to convert text to csv or xls?

Kent Ostby — Wed, 29 Mar 2006 16:50:11 GMT

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

Re: How to convert text to csv or xls?

Geoff Wild — Wed, 29 Mar 2006 16:50:19 GMT

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

Re: How to convert text to csv or xls?

A. Clay Stephenson — Wed, 29 Mar 2006 16:57:16 GMT

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.

Re: How to convert text to csv or xls?

someone_4 — Wed, 29 Mar 2006 16:57:40 GMT

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

Re: How to convert text to csv or xls?

James R. Ferguson — Wed, 29 Mar 2006 16:59:32 GMT

Hi Geoff:

Try this:

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

Regards!

...JRF...

Re: How to convert text to csv or xls?

Patrice Le Guyader — Wed, 29 Mar 2006 17:02:30 GMT

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

Re: How to convert text to csv or xls?

Geoff Wild — Wed, 29 Mar 2006 17:05:51 GMT

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

Re: How to convert text to csv or xls?

Geoff Wild — Wed, 29 Mar 2006 17:15:10 GMT

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

Re: How to convert text to csv or xls?

A. Clay Stephenson — Wed, 29 Mar 2006 17:24:32 GMT

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".

Re: How to convert text to csv or xls?

Patrice Le Guyader — Wed, 29 Mar 2006 17:25:57 GMT

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

Re: How to convert text to csv or xls?

Ivan Ferreira — Wed, 29 Mar 2006 17:32:34 GMT

See this also:

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

Re: How to convert text to csv or xls?

Sandman! — Wed, 29 Mar 2006 19:33:43 GMT

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!

Re: How to convert text to csv or xls?

Hein van den Heuvel — Wed, 29 Mar 2006 19:48:15 GMT

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.

Re: How to convert text to csv or xls?

Andy Torres — Thu, 30 Mar 2006 09:35:11 GMT

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!

Re: How to convert text to csv or xls?

H.Merijn Brand (procura — Thu, 30 Mar 2006 09:41:47 GMT

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

Re: How to convert text to csv or xls?

Peter Godron — Thu, 30 Mar 2006 09:49:08 GMT

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.

Re: How to convert text to csv or xls?

H.Merijn Brand (procura — Thu, 30 Mar 2006 09:59:45 GMT

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

Re: How to convert text to csv or xls?

Geoff Wild — Thu, 30 Mar 2006 11:05:21 GMT

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