Converting .CSV file into .XLS file

[Karthika_2]

Hi All,

I wrote a script to extract data from Oracle DB and place it in a text file , and I have coverted .txt file into comma seperated .csv file and I sent it to my mail box .

I can get .xls file in my inbox.I am getting all data in same column and in different rows , without column name.

but my requirement is to get data in different cells with column names.

I am using, ( uuencode /test.csv /test.xls |mailx -s "test mial" abscd@in.ab.com ) to send the file.

Note : In my script I set heading off because , every 10 records once I am getting the headings , I don't want like that I need the headings to be printed once .

Please someone guide me.

Regards,
Karthi

Example files:

my .txt file looks like,

asmdev1 karthi
asmdev2 ganesh

my .csv file looks like ,

asmdev1,karthi
asmdev2,karthi

I want to get asmdev in one column and karthi in second column with heading's .

[Paul McCleary]

I'm not quite sure what you're doing. Are you using uuencode to send your .csv file as an attachment to your mail account, which is then restored with a .xls extension. You then open this file in Excel and it has all the data in one column - not split into columns as you would like. Is that correct?

If so, I would have thought you should be sending this file as a .csv and opening it in Excel - then if needed specifying that your field delimiter is a ,

Hope this helps, Paul

[H.Merijn Brand (procura]

$ cpan Spreadsheet::Read
:
:

Answer "yes" to the optional install questions

$ cd /tmp
$ csv2xls file.csv
$ ls -l file.xls

That what you want?

Enjoy, Have FUN! H.Merijn

[Karthika_2]

Paul,

Yes, that is correct ..I want all the data in different columns.

I want to setup a cron which does all these conversions and send a full completed excel sheet to my inbox.

Please guide me !!!

Merijn,

I tried your suggestion,

I got the message,
csv2xls:not found

Regards,
Karthi

[H.Merijn Brand (procura]

Ahhgr, I thought is was in Spreadsheet::Read, but it is in Text::CSV_XS

$ cd /usr/local/bin
$ wget --output-document=csv2xls 'http://repo.or.cz/w/Text-CSV_XS.git?a=blob_plain;f=examples/csv2xls;hb=HEAD'
$ ex -v csv2xls

-- edit the first line to point to your perl

$ chmod 755 csv2xls

Now try again.

Enjoy, Have FUN! H.Merijn

[Paul McCleary · Accepted solution]

I thought a .csv file was already recognised by Excel as a valid extension - if not you could set files with that extension to be opened by Excel. Excel should be able to open a comma separated .csv file without you needing to do anything.

I would change your approach so that uuencode has the remotedest arguement of /test.csv and does not change the extension to .xls

[Karthika_2]

Merjin,

ksh : wget : not found.

Please tell me anything should be installed to use csv2xls command ?

regards,
Karthi.

[H.Merijn Brand (procura]

Paul, Excel tries to pretend it recognizes CSV, but it is extremely depending on the *current* locale.

If you "open" a .CSV file in Excell, it will expect the current list-separator from the current locale to be the field sep. In e.g. Dutch, windows defaults that to a semi-colon, causing open actions to fail.
You can still open Excel first and then import the data, which is much more reliable, but still fails for any field that Excel thinks that it looks like a date, even if it is quoted, so a numer like 20090910 will be converted to '9 Oct 2009' even in Dutch locale. Hatefull. Maybe it is better in post-Excel-2003, but still very very unreliable

Enjoy, Have FUN! H.Merijn

[Hein van den Heuvel]

>> In my script I set heading off because , every 10 records once I am getting

Don't set teh heading off... how can excel make up column names?!?
Using SQLplus just set pagesize to zero and it will only print the heading once. The defaults pagesize is just 14, explaining the repeats.

>>> I wrote a script to extract data from Oracle DB and place it in a text file , and I have coverted .txt file into comma seperated .csv file and I sent it to my mail box .

Google for "+oracle +csv +output"
- Using SQLplus?

You'll want to disable a lot of the formatting:

set echo off
set feedback off
set linesize 10000
set pagesize 0
set sqlprompt ''
set trimspool on
set space 0
set truncate on

And start with sqlplus -s

- Using AWK or PERL or SHELL for post process?

Should be trivial to recognize a repeated header line and not output that.


You may want to "Ask Tom" this question.

For example:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:88212348059

Or the intersting sylk alternative through the (not standard provided) Owa_sylk utility.

http://asktom.oracle.com/pls/asktom/f?p=100:11:2327399712566997::::P11_QUESTION_ID:769425837805


Regards,
Hein van den Heuvel
HvdH Performance Consulting

[Karthika_2]

Hey Paul,

Its working :)

uuencode /test.csv /test.csv | mailx -s "test" karthimu@in.abc.com

I am receiving excel in my inbox with data in different columns.

Thanks a lot !!
Regards,
Karthi