Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Convert each field to excel

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert each field to excel
# 1  
Old 01-21-2008
Convert each field to excel
Hi Folks!!

I would like to convert each and every output of my query to the excel column wise. Let me give you an example

The first attachment (cr.jpg) is the one which i am getting out of a query and the fields are delimited with a '|' (pipe) symbol. I would like to convert each field into a separate row in the excel sheet.

I got inputs from some of our friends to convert using sed and tr (which worked), but where there are descriptions more than one line, the transformation to excel happens in the new line.

Assume, below is one row before transformation

10048|15144|CR 10048 QA Issue|In the below message
SWEEPER DETAILS MONITOR MI111
Sweeper Details Monitor 07/12/07

After transforming the same using sed/tr, it creates

[SWEEPER DETAILS MONITOR MI111
Sweeper Details Monitor 07/12/07

in the second and third line. But actually, it should be within the 4th column and inside one cell in continuation with "In the below message". The sample excel is shown in excel.jpg.

Should i be more precise in my explanations? Kindly let me know for any clarifications
Convert each field to excel-crjpg
Convert each field to excel-exceljpg
# 2  
Old 01-21-2008
Try using using awk to convert to html, then load into excel....
Code:
awk 'BEGIN{
  FS="|"
  cols=4
  print "<HTML><BODY><TABLE>"
}
NF==cols{
  if(NR>1)
    print "</TD></TR>"
  printf "<TR>"
  for(i=1;i<NF;i++)
    printf "<TD>%s</TD>", $i
  printf "<TD>%s", $NF
}
NF==1{
  printf "\n%s", $0
}
END{
  print "</TD></TR>\n</TABLE></BODY></HTML>"
}
' file1 > file2.xls

Tested on file:
Code:
a1|a2|a3|a4
b1|b2|b3|b
4
c1|c2|c3|c4

Output:
Code:
<HTML><BODY><TABLE>
<TR><TD>a1</TD><TD>a2</TD><TD>a3</TD><TD>a4</TD><TR>
<TR><TD>b1</TD><TD>b2</TD><TD>b3</TD><TD>b
4</TD><TR>
<TR><TD>c1</TD><TD>c2</TD><TD>c3</TD><TD>c4</TD><TR>
</TABLE></BODY></HTML>

# 3  
Old 01-22-2008
thanks Ygor!! It did the magic Smilie

Could you also throw some light, how the same file can be converted to csv.. When i open the same file as csv, it is showing the tags names too, wherein it should show the same as xls

<HTML><BODY><TABLE>
<TR><TD>Parent CR</TD><TD>ChildCR</TD><TD>Title</TD><TD>Description</TD></TR>
</TABLE></BODY></HTML>
<HTML><BODY><TABLE>
<TR><TD>10048</TD><TD>14950</TD><TD>CR 10048 QA Issue</TD><TD>The AutoSett xml message generated got rejected in dBCRis. </TD></TR>
<TR><TD>10048</TD><TD>15144</TD><TD>CR 10048 QA Issue</TD><TD>In the below message
Last edited by ganga.dharan; 01-22-2008 at 03:07 AM..
# 4  
Old 01-22-2008
hi to all !! can anyone help me out with a solution.. Thanks in advance
# 5  
Old 01-22-2008
You seem to have missed the point. The above script reads a pipe-delimited file and converts it to html. The html file is saved as an .xls file so that it can be opened in excel. To convert to csv, use "file >> save as >> csv" from excel.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert excel to tab

Hello, I have an excel sheet (.xlsx), with 11 worksheets. I need to run my bash scripts on the 1st worksheet and write the output to the 2nd worksheet. I am currently manually converting the worksheet into a tab delimited to run the script and then pasting it back to excel. Is there a way to do... (2 Replies)
Discussion started by: nans
2 Replies

2. Shell Programming and Scripting

Convert excel to csv in python date not display exactly

Hi, Anyone can help I am just converting the excel file to csv using python, I can get the conversion output but the date not display exactly. test.xlsx date format 167 1588 risks/SCS JP CAMPANA & CIE.pdf SCS JP CAMPANA & CIE 2 1 1 0 2015-03-16 16:56:25 167 1146 risks/AirBNB... (1 Reply)
Discussion started by: fspalero
1 Replies

3. Shell Programming and Scripting

Need to convert text file into Excel sheet

:cool:Hi, Iam new to this Scripts and forum too. Plz excuse if may i ask silly questions now and further.. Prob: i need to convert txt files data into excel sheet. Suppose if i have hour-wise data in txt file1 and file2 like file1 file2 00: 140 00: 235 01: 160 01:415... (1 Reply)
Discussion started by: mrudula
1 Replies

4. UNIX for Dummies Questions & Answers

Convert csv to excel

Hi All, I have a csv file in unix and I need to convert it into excel formate. Please help me out (1 Reply)
Discussion started by: Abhisrajput
1 Replies

5. Shell Programming and Scripting

How to convert Excel Files to Xls?

Hi All, I have created a BI Publisher report in Oracle apps R12. It has generated the output with *.EXCEL file extension. I have a shell script to mail this output. in my shell script I have copied this file and saved as *.xls file. But the R12 generated *.EXCEL file is not a real excel... (5 Replies)
Discussion started by: kalidoss
5 Replies

6. Shell Programming and Scripting

Convert array data to excel format

I need your help in changing the script I have data has below text file :> cat my_emp Employee array(0) Name : Albert No : 1234 Address: stationstraat City: Utrecht Employee array (1) Name : Kouwen No : 1256 Address: stationstraat City: Amsterdam Employee array (2) Name : Peter... (2 Replies)
Discussion started by: LinuxLearner
2 Replies

7. UNIX for Advanced & Expert Users

how to convert text/csv to excel

Hello All, I have a sql report with 50 columns and 1000 rows result in a file ( txt / csv). is there is any way that we can move them to excel in KSH. Thanks, Sateesh (7 Replies)
Discussion started by: kotasateesh
7 Replies

8. Shell Programming and Scripting

how to convert fields from a text file to excel columns

i have this file which has the following contents: ,-0.3000 ,-0.3000 ,-0.3000 ,-0.9000 ,-0.9000 ,-0.9000 i would like to get this: -0.3-0.9-0.3-0.9-0.3-0.9 so far i am trying: awk '{for(i=1; i<=NF; i++) {printf("%f\n",$i)}}' test1 > test2 any help... (4 Replies)
Discussion started by: npatwardhan
4 Replies

9. Shell Programming and Scripting

Convert to excel

Hi to all the tycoons of Shell programming!! I am getting a csv report out of a query and the report is delimited with '|' symbol. I would like to convert this csv report into an xls column wise.. Let me show u an example as below Say a.csv looks like below Number|Description|Cycle|Date... (6 Replies)
Discussion started by: ganga.dharan
6 Replies

10. Shell Programming and Scripting

convert Text to Excel

I have some text data generated by a perl script and want to put it into an excel sheet. Is there a perl script that can do this for me, i.e, convert text data to excel? (1 Reply)
Discussion started by: Pavankk
1 Replies
Login or Register to Ask a Question