topic Re: Converting .CSV file into .XLS file in Operating System - HP-UX

Converting .CSV file into .XLS file

Karthika_2 — Thu, 10 Sep 2009 09:44:45 GMT

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 .

Re: Converting .CSV file into .XLS file

Paul McCleary — Thu, 10 Sep 2009 10:33:52 GMT

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

Re: Converting .CSV file into .XLS file

H.Merijn Brand (procura — Thu, 10 Sep 2009 11:07:33 GMT

$ 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

Re: Converting .CSV file into .XLS file

Karthika_2 — Thu, 10 Sep 2009 11:21:22 GMT

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

Re: Converting .CSV file into .XLS file

H.Merijn Brand (procura — Thu, 10 Sep 2009 11:31:24 GMT

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

Re: Converting .CSV file into .XLS file

Paul McCleary — Thu, 10 Sep 2009 11:33:38 GMT

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

Re: Converting .CSV file into .XLS file

Karthika_2 — Thu, 10 Sep 2009 11:38:10 GMT

Merjin,

ksh : wget : not found.

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

regards,
Karthi.

Re: Converting .CSV file into .XLS file

H.Merijn Brand (procura — Thu, 10 Sep 2009 11:41:16 GMT

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

Re: Converting .CSV file into .XLS file

Hein van den Heuvel — Thu, 10 Sep 2009 11:45:56 GMT

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

Re: Converting .CSV file into .XLS file

Karthika_2 — Thu, 10 Sep 2009 11:51:33 GMT

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

Re: Converting .CSV file into .XLS file

H.Merijn Brand (procura — Thu, 10 Sep 2009 11:57:15 GMT

Karthika, you can also open that link in your browser, and then Ctrl-S to save the file

Enjoy, Have FUN! H.Merijn [ who has a hard time imagining people not having wget somewhere ]

Re: Converting .CSV file into .XLS file

Paul McCleary — Thu, 10 Sep 2009 13:44:14 GMT

Hi Karthika,

Glad its working, I thought it might, but had no way of testing so its good to know! :-)

Hi H.Merrjn,

Interesting point, I never thought of any locale differences. I did just try opening a comma separated .csv file in Excel with numbers like 20090910 in some fields and all the cells had a General format type - so the number is displayed as written, not as a date. This is in Excel 2003 SP3 with a UK locale. So I guess it seems to work ok for me and my configuration.

Regards, Paul