LinuxQuestions.org
Page 1 of 2 1 2
Show 50 post(s) from this thread on one page

LinuxQuestions.org (/questions/)
-   Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)
-   -   Script to generate EXCEL sheet by shell script (https://www.linuxquestions.org/questions/linux-newbie-8/script-to-generate-excel-sheet-by-shell-script-4175518474/)

dani1234 09-13-2014 12:27 PM
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.

pan64 09-13-2014 01:04 PM
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?

Ser Olmy 09-13-2014 01:57 PM
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?

dani1234 09-13-2014 02:30 PM
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.

TB0ne 09-13-2014 02:51 PM
Quote:
Originally Posted by dani1234 (Post 5237365)
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.

dani1234 09-13-2014 03:04 PM
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

Ser Olmy 09-13-2014 03:09 PM
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.

TB0ne 09-13-2014 03:35 PM
Quote:
Originally Posted by dani1234 (Post 5237376)
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

schneidz 09-13-2014 04:24 PM
i do this sometimes in db2:
Quote:
Originally Posted by dani1234 (Post 5237376)
...
#!/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...

dani1234 09-14-2014 08:26 AM
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

TB0ne 09-14-2014 10:08 AM
Quote:
Originally Posted by dani1234 (Post 5237666)
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.

dani1234 09-14-2014 11:03 AM
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.

TB0ne 09-14-2014 11:21 AM
Quote:
Originally Posted by dani1234 (Post 5237744)
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.

schneidz 09-14-2014 12:05 PM
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 ?

keefaz 09-14-2014 12:57 PM
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'


All times are GMT -5. The time now is 12:57 PM.
Page 1 of 2 1 2
Show 50 post(s) from this thread on one page