Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

script to format rows to column and export to excel

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting script to format rows to column and export to excel
# 1  
Old 11-27-2010
script to format rows to column and export to excel
i need to write script to copy the txt file to excel. (data can be 2000+), data may not be in order
ex:
my name: abc
age: 20
add: xyz
DOB: 17-mar-2010
add1: adf
add2: guioth

my name: cat
age: 35
DOB: 11-oct-2005
city: yeshjl
add: opq
DOB: 17-mar-2010
add1: atg
add2: gth
add3:ert

my name: def
age: 21
DOB: 17-apr-2010
add: uvw
add1: adf
add2: gyuth


require format as below in excel, columnwise
abc 20 17-mar-2010 xyz
cat 35 11-oct-2005 opq yeshjl
def 21 17-apr-2010 uvw
Last edited by pjain; 11-27-2010 at 12:28 AM.. Reason: fields added
# 2  
Old 11-27-2010
hi, welcome to the forum. What have you tried so far?
# 3  
Old 11-27-2010
Code:
$ cat myscript
#!/usr/bin/ksh
[[ -n "$(tail -1 txt)" ]] && echo >>txt
while read a
do
case ${a%%:*} in
        "my name") f1=${a##*: } ;;
        "age") f2=${a##*: } ;;
        "DOB") f3=${a##*: } ;;
        "city") f5=${a##*: } ;;
        "add") f4=${a##*: } ;;
esac
if [[ -z $a ]]; then
        echo "$f1 $f2 $f3 $f4 $f5"
        f1="" ; f2="" ; f3="" ; f4="" ; f5=""
fi
done<txt

Code:
$ cat txt
my name: abc
age: 20
add: xyz
DOB: 17-mar-2010
add1: adf
add2: guioth

my name: cat
age: 35
DOB: 11-oct-2005
city: yeshjl
add: opq
DOB: 17-mar-2010
add1: atg
add2: gth
add3:ert

my name: def
age: 21
DOB: 17-apr-2010
add: uvw
add1: adf
add2: gyuth

Code:
$ ksh myscript
abc 20 17-mar-2010 xyz
cat 35 17-mar-2010 opq yeshjl
def 21 17-apr-2010 uvw
$

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Convert rows to column and print output in required format

Hi All, i am trying to print the solaris 11 packages in below required format, But i am unable to do that. Current ouput : root@abc# pkginfo -l | egrep '(BASEDIR|NAME|VERSION)' | awk '{print}' NAME: QLogic 570x/571x Gigabit Ethernet Driver VERSION: 11.11,REV=2009.11.11 ... (7 Replies)
Discussion started by: balu1234
7 Replies

2. UNIX for Beginners Questions & Answers

How to insert data into black column( Secound Column ) in excel (.XLSX) file using shell script?

Source Code of the original script is down below please run the script and try to solve this problem this is my data and I want it column wise 2019-03-20 13:00:00:000 2019-03-20 15:00:00:000 1 Operating System LAB 0 1 1 1 1 1 1 1 1 1 0 1 (5 Replies)
Discussion started by: Shubham1182
5 Replies

3. Shell Programming and Scripting

Reading a column from excel using shell script in Linux environment

Hi all I am new to shell scripting. I need to read the 1st column and last of the excel file in linux environment. Can some one help me with examples (3 Replies)
Discussion started by: Vigneshj28
3 Replies

4. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

5. Shell Programming and Scripting

Export data from database in Excel sheet with the help of Shell script and automated the report

Export data from database in Excel sheet with the help of Shell script and automated the report every day in the mornig. (1 Reply)
Discussion started by: neeraj617
1 Replies

6. UNIX for Advanced & Expert Users

How to export Result to Excel Tabular format from UNIX?

Hi I am working on a script in which I am firing a query on database through Unix and getting the result set. I want to export that in an excel file. I am able to do so nut the result are exported horizontally one below the other. Can anyone plss help me out in exporting the Result in Tabular... (4 Replies)
Discussion started by: Saritau3
4 Replies

7. Shell Programming and Scripting

Retaining the Unix CSV format in Excel format while exporting

Hi All, I have created a Unix Shell script whch creates a *.csv file and export it to Excel. The problem i am facing is that Users wants one of the AMOUNT field in comma separted values. Example : if the Amount has the value as 3000000 User wants to be in 3,000,000 format. This Amount format... (2 Replies)
Discussion started by: rawat_me01
2 Replies

8. Shell Programming and Scripting

Need to convert the content of file into COLUMN (To export into excel)

I have multiple condition in file as below. MONITOR "ALERT_INFO" DESCRIPTION "Triggered when informational Netware alert occured" MAXTHRESHOLD 95 SEVERITY Normal MONITOR "ALERT_MAJOR" DESCRIPTION "Triggered when major Netware alert occured" MAXTHRESHOLD SEVERITY Major I need to... (6 Replies)
Discussion started by: velocitnitin
6 Replies

9. Shell Programming and Scripting

Script on pattern matching and print lines and export to excel

Hi Friends, I am working on a script.. Looking forward for your expert help..... My requirement is: I have a text file where, need to search equip * RTF or end of line with RTF ,once this pattern is found then print 2nd line, 6th line, 7th line to a different file. For Ex: equip 1... (34 Replies)
Discussion started by: shaliniyadav
34 Replies

10. Shell Programming and Scripting

Export to Microsoft excel using shell script

I have requirement where i have to export the data extracted from a flat file to a microsoft excel sheet. If the awk returns multiple records then all these records should go in into different rows of same column in excel. Eg. say data returned by excel is A,B,C,D then these 4 records should go... (1 Reply)
Discussion started by: goutam_igate
1 Replies
Login or Register to Ask a Question