LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 09-13-2014, 01:27 PM   #1
dani1234
LQ Newbie
 
Registered: Sep 2014
Posts: 5

Rep: Reputation: Disabled
Script to generate EXCEL sheet by shell script


Hi ,

i am generating some data by firing sql query with connecting to the database by my solaris box.

I am getting data as below...

TO_CHAR(C. CURR_EMP_NO
---------- ---------------
LST_NM
--------------------------------------------------------------------------------
FST_NM
--------------------------------------------------------------------------------
DOJ_EMp QVD_CD PLC_CD
----------- -------- --------
DESC_TXT
--------------------------------------------------------------------------------
13-09-2014 34653436
John
Sendrie


TO_CHAR(C. CURR_EMP_NO
---------- ---------------
LST_NM
--------------------------------------------------------------------------------
FST_NM
--------------------------------------------------------------------------------
DOJ_EMp QVD_CD PLC_CD
----------- -------- --------
DESC_TXT
--------------------------------------------------------------------------------
16-DEC-2008 SO MPCHG1
PCHG1 (Continuous PVR excess - more than 180 days)


But i am expecting in the excel format like as below
TO_CHAR(C.DTD_ACY_START_DTM_UTC+8/24,'DD-MM-YYYY') CURR_EMP_NO LST_NM FST_NM DOJ_EMP QV_CD PL_CD DESC_TXT

13-09-2014 34653436 John sendrie 23-APR-2007 MCH1 Hospitalised (Continous hospitalised 1 month or more and long Medical)

13-09-2014 4736543 michal thomas 16-DEC-2008 SO MPCHG1 PCHG1 (Continuous PVr excess - more than 180 days)


In short each row means each column and bold one is the header of my excel sheet (containg hundreds of records), so i need data in each corresponding coulmn.


whatever query iam firing by shell script result should be in EXcel format and then iam ftpying this excel to my superior.
can anyone assist me on this.
 
Old 09-13-2014, 02:04 PM   #2
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian i686 (solaris)
Posts: 8,104

Rep: Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267Reputation: 2267
this is the right forum to get help (so someone will surely help you). But first you need to tell us your problem (to be able to assist). Do you have a script already? How does your query work?
 
Old 09-13-2014, 02:57 PM   #3
Ser Olmy
Senior Member
 
Registered: Jan 2012
Distribution: Slackware
Posts: 2,403

Rep: Reputation: Disabled
You can't really make the SQL server return data in "Excel format", as that is a proprietary Microsoft file format, but I suspect you mean a list of comma-separated values, which Excel (and other spreadsheet software) is able to interpret directly.

Which SQL server are we talking about here, and what does the SQL query look like, exactly?
 
1 members found this post helpful.
Old 09-13-2014, 03:30 PM   #4
dani1234
LQ Newbie
 
Registered: Sep 2014
Posts: 5

Original Poster
Rep: Reputation: Disabled
Hi Ser/pan,
Thanks for reply, Yes script is ready to extract the data , here only i need data to be in a proper format may be comma separated format so i can have a file with xls and send to the superior one.

The result should be the excel on whatever going at back end dosnt matter,boss should be able to read data...

SQL query is look like below(query is being fully altered)


Query result is perfectly correct
SELECT TO_CHAR(C.PVR_ACY_START_DTM_UTC+8/24,'DD-MM-YYYY'),D.CURR_EMP_NO,D.LST_NM,D.FST_NM,D.DOJ_EMP,C.VTP_CD,A.VPA_CD,B.DESC_TXT FROM HFG_ACYS A,VMX_USER.JDS_EMP_PLX_TYPES B,EMP E,EMP1 D
And C.Dtd_asdb_Start_Dtm_Utc+8/24 <= Sysdate + 1
(fdmf_ACY_END_DTM_UTC+8/24,'DD-MM-YYYY');

Problem is Data format, its in line by line format as shown below.
 
Old 09-13-2014, 03:51 PM   #5
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 17,921

Rep: Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690
Quote:
Originally Posted by dani1234 View Post
Hi Ser/pan,
Thanks for reply, Yes script is ready to extract the data , here only i need data to be in a proper format may be comma separated format so i can have a file with xls and send to the superior one.

The result should be the excel on whatever going at back end dosnt matter,boss should be able to read data...

SQL query is look like below(query is being fully altered)


Query result is perfectly correct
SELECT TO_CHAR(C.PVR_ACY_START_DTM_UTC+8/24,'DD-MM-YYYY'),D.CURR_EMP_NO,D.LST_NM,D.FST_NM,D.DOJ_EMP,C.VTP_CD,A.VPA_CD,B.DESC_TXT FROM HFG_ACYS A,VMX_USER.JDS_EMP_PLX_TYPES B,EMP E,EMP1 D
And C.Dtd_asdb_Start_Dtm_Utc+8/24 <= Sysdate + 1
(fdmf_ACY_END_DTM_UTC+8/24,'DD-MM-YYYY');

Problem is Data format, its in line by line format as shown below.
Post your script, so we can see what's going on. Also, you don't say what version/distro of Linux, or what SQL server you're using, but many SQL engines (like Oracle or MySQL), can output comma-separated values already, as part of the output query.

So, post your script, tell us about your environment, and we can try to help.
 
Old 09-13-2014, 04:04 PM   #6
dani1234
LQ Newbie
 
Registered: Sep 2014
Posts: 5

Original Poster
Rep: Reputation: Disabled
Below is the script which is actually contain nothing only the query.

Operating system :SUNOS/Solaris
Database : oracle

Current script giving me the correct but inform of data , not inform of record/table/excel.

#!/bin/ksh
sqlplus -s /nolog << EOF
CONNECT test/test@IP

alter session set nls_date_format= 'DD-MON-YYYY';
SELECT TO_CHAR(C.PVR_ACY_START_DTM_UTC+8/24,'DD-MM-YYYY'),D.CURR_EMP_NO,D.LST_NM,D.FST_NM,D.DOJ_EMP,C.VTP_CD,A.VPA_CD,B.DESC_TXT FROM HFG_ACYS A,VMX_USER.JDS_EMP_PLX_TYPES B,EMP E,EMP1 D
And C.Dtd_asdb_Start_Dtm_Utc+8/24 <= Sysdate + 1
(fdmf_ACY_END_DTM_UTC+8/24,'DD-MM-YYYY');


end;
/
EOF
 
Old 09-13-2014, 04:09 PM   #7
Ser Olmy
Senior Member
 
Registered: Jan 2012
Distribution: Slackware
Posts: 2,403

Rep: Reputation: Disabled
You should probably use the LISTAGG function; see the Oracle documentation for details.

You didn't provide the Oracle version number. LISTAGG is not available in older versions, but the documentation provides alternatives. Please heed the warnings and do NOT under any circumstances use WM_CONCAT.
 
Old 09-13-2014, 04:35 PM   #8
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 17,921

Rep: Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690
Quote:
Originally Posted by dani1234 View Post
Below is the script which is actually contain nothing only the query.
Operating system :SUNOS/Solaris
Database : oracle

Current script giving me the correct but inform of data , not inform of record/table/excel.
Code:
#!/bin/ksh
sqlplus -s /nolog << EOF 
CONNECT test/test@IP

alter session set nls_date_format= 'DD-MON-YYYY';
SELECT TO_CHAR(C.PVR_ACY_START_DTM_UTC+8/24,'DD-MM-YYYY'),D.CURR_EMP_NO,D.LST_NM,D.FST_NM,D.DOJ_EMP,C.VTP_CD,A.VPA_CD,B.DESC_TXT FROM HFG_ACYS A,VMX_USER.JDS_EMP_PLX_TYPES B,EMP E,EMP1 D
And C.Dtd_asdb_Start_Dtm_Utc+8/24 <= Sysdate + 1
(fdmf_ACY_END_DTM_UTC+8/24,'DD-MM-YYYY');
 
end;
/
EOF
So your 'script' is only a SQL query...haven't you done or tried to do any formatting to get what you need? You still don't provide any examples of what your query is giving you...there are several ways to get the output formatted in Unix/Linux, but AGAIN...Oracle has the ability to give you CSV formatted data on the fly. Have you tried contacting Oracle support, since you're paying for it, or checking the Oracle knoweldgebase?
https://community.oracle.com/thread/2222442?tstart=0
 
Old 09-13-2014, 05:24 PM   #9
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,027

Rep: Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845
i do this sometimes in db2:
Quote:
Originally Posted by dani1234 View Post
...
#!/bin/ksh
sqlplus -s /nolog << EOF
CONNECT test/test@IP

alter session set nls_date_format= 'DD-MON-YYYY';
SELECT TO_CHAR(C.PVR_ACY_START_DTM_UTC+8/24,'DD-MM-YYYY'),char(","),D.CURR_EMP_NO,char(","),D.LST_NM,char(","),D.FST_NM,char(","),D.DOJ_EMP,char(","),C.VTP_CD,char(","),A.VPA_CD,char(","),B.DESC_TXT,char(",") FROM HFG_ACYS A,VMX_USER.JDS_EMP_PLX_TYPES B,EMP E,EMP1 D
And C.Dtd_asdb_Start_Dtm_Utc+8/24 <= Sysdate + 1
(fdmf_ACY_END_DTM_UTC+8/24,'DD-MM-YYYY');


end;
/
EOF...
 
1 members found this post helpful.
Old 09-14-2014, 09:26 AM   #10
dani1234
LQ Newbie
 
Registered: Sep 2014
Posts: 5

Original Poster
Rep: Reputation: Disabled
Nope, data is getting sepaerated by comma and in same way it is displaying in excel no columnar put which is expected.

emp_no:002-00,ndsbnfbd:01-OCT-2014,ndbnbfs:01-OCT-201
4,EMP_Type:B,FD:345,PVB:PN,Duty_Number:1,Item_Number:1,COMPANY_CODE:HG,1

i
 
Old 09-14-2014, 11:08 AM   #11
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 17,921

Rep: Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690
Quote:
Originally Posted by dani1234 View Post
Nope, data is getting sepaerated by comma and in same way it is displaying in excel no columnar put which is expected.

emp_no:002-00,ndsbnfbd:01-OCT-2014,ndbnbfs:01-OCT-2014,EMP_Type:B,FD:345,PVB:PN,Duty_Number:1,Item_Number:1,COMPANY_CODE:HG,1

i
Ok, so AGAIN, can you post:
  • An EXACT sample of what your 'script' is putting out?
  • An EXACT sample of what you WANT to get from the script?
  • ...and what you've done/tried to get there?
If it's in CSV format, it's ready for Excel...not sure what else you need, and we can't guess.
 
Old 09-14-2014, 12:03 PM   #12
dani1234
LQ Newbie
 
Registered: Sep 2014
Posts: 5

Original Poster
Rep: Reputation: Disabled
This is the exact output sample for one row getting after executing the script

--------------------------------------------------------------------------------
Code:
EMPLOYEE_Number:002-00,CTC_Date_Utc:01-OCT-2014,DTC_Date_Local:01-OCT-201
4,EMP_Type:H,PVR:56C,CVD:KYD,Duty_Number:1,Item_Number:1,COMPANY_Code:CM,FC
EMP_Number:2,EMP_type:220,CD_Registration:845784,CTC_Date_Utc:01-OCT-2
014 00:00:00,Departure_Station:KUL,Arrival_Station:LHR,Start_Date_Utc:01-OCT-201
4 15:40:00,End_Date_Utc:02-OCT-2014 04:50:00,Start_Date_Local:BGL Time:01-OCT-20
14 23:40:00,End_Date_Local:PVR Time:02-OCT-2014 05:50:00,Work_type:OP,Service_IA
TA_type:J,Transit_Time:
Expectation : excel should show data as below , means in multiple column not in a single one


Code:
EMPLOYEE_Number   CTC_Date_Utc   DTC_Date_Local    EMP_Type  PVR   Duty_Number Item_number Company Code
002-00             01-OCT-2014   01-OCT-201         H        56C    1            1            CM
What I did:

I have added a "," as after each column as suggested in query.
 
Old 09-14-2014, 12:21 PM   #13
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 17,921

Rep: Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690Reputation: 3690
Quote:
Originally Posted by dani1234 View Post
This is the exact output sample for one row getting after executing the script
Code:
EMPLOYEE_Number:002-00,CTC_Date_Utc:01-OCT-2014,DTC_Date_Local:01-OCT-201
4,EMP_Type:H,PVR:56C,CVD:KYD,Duty_Number:1,Item_Number:1,COMPANY_Code:CM,FC
EMP_Number:2,EMP_type:220,CD_Registration:845784,CTC_Date_Utc:01-OCT-2
014 00:00:00,Departure_Station:KUL,Arrival_Station:LHR,Start_Date_Utc:01-OCT-201
4 15:40:00,End_Date_Utc:02-OCT-2014 04:50:00,Start_Date_Local:BGL Time:01-OCT-20
14 23:40:00,End_Date_Local:PVR Time:02-OCT-2014 05:50:00,Work_type:OP,Service_IA
TA_type:J,Transit_Time:
Expectation : excel should show data as below , means in multiple column not in a single one
Code:
EMPLOYEE_Number   CTC_Date_Utc   DTC_Date_Local    EMP_Type  PVR   Duty_Number Item_number Company Code
002-00             01-OCT-2014   01-OCT-201         H        56C    1            1            CM
What I did:
I have added a "," as after each column as suggested in query.
Ok, so all you've done is add the "," as was given to you. Again, what have YOU written/tried to make this work on your own? Again, the Oracle documentation has plenty of information on how to omit field names in output:
https://community.oracle.com/thread/2389479
http://docs.oracle.com/cd/B10501_01/...a90842/ch5.htm

You were already directed to the Oracle documentation...did you read it yet? And if you don't want to use Oracle to do this (which you can), you can easily strip out anything from the comma to the colon (such as ,Departure_Station, using a simple SED statement. Again, that is overkill, since you can tell Oracle to leave off the column headers, and just return the data.
 
Old 09-14-2014, 01:05 PM   #14
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,027

Rep: Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845Reputation: 845
every rdbms i used (mysql, db2, udb, sqlite, ...), the output would be column names on the top with space-padded data in the following rows.
is oracle really that wierd ?

maybe something like[untested]:
Code:
 ... | tr ',' '\n' | cut -d : -f 2 | tr '\n' ','
would get you closer to what you need ?

Last edited by schneidz; 09-14-2014 at 01:13 PM.
 
Old 09-14-2014, 01:57 PM   #15
keefaz
LQ Guru
 
Registered: Mar 2004
Distribution: Slackware
Posts: 5,387

Rep: Reputation: 397Reputation: 397Reputation: 397Reputation: 397
Maybe no script needed, just mysql query like SELECT ... INTO OUTFILE 'file.csv'

And with options like FIELDS TERMINATED BY ',' ... ENCLOSED BY '"' ... LINES TERMINATED BY '\n'
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Can i extract values from a excel file using a shell script VijayB Linux - Newbie 1 08-01-2014 01:55 AM
script to create excel sheet from this file perumal07 Linux - Server 2 01-09-2014 07:08 AM
How to generate shell script for 1 3 2 4 3 5 4 6..100 learngeek Linux - Newbie 5 12-06-2012 05:25 PM
Generate Items for a case shell script Snake_Eyes_ Linux - General 8 12-12-2009 07:56 AM


All times are GMT -5. The time now is 02:33 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration