Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Converting txt file to Excel file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Converting txt file to Excel file
# 1  
Old 05-25-2012
Converting txt file to Excel file
Hi All,

I have a text file with below content.

Code:
TradeDate Name    SecurityMnc  ReasonDesc        
=======================================
20120501   Robin     ABC      FO System Defect  
20120502   Robin     ABC      FO System Defect

I would want this in an excel file in 4 columns, am trying some thing with AWK but problem is am getting like below. This is causing excel file to display it in 6 columns.. last field is considering as 3 columns Smilie

Code:
TradeDate,Name,SecurityMnc,ReasonDesc        
=======================================
20120501,Robin,ABC,FO,System,Defect  
20120502,Robin,ABC,FO,System,Defect


But i need like below.
Code:
TradeDate,Name,SecurityMnc,ReasonDesc        
=======================================
20120501,Robin,ABC,FO System Defect  
20120502,Robin,ABC,FO System Defect


Any help would be greately appreciated. Thanks.
Last edited by Franklin52; 05-25-2012 at 06:43 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 05-25-2012
it will come like that only because is has space between that field. either separate that file with valid separator or use double quotes for the last field.

another way you can do is -

store first three fields in three variables and for 4th field try -

Code:
`cut -d" " -f4-`

then try to put every variable in excel file. hope that will work.
This User Gave Thanks to donadarsh For This Post:
robinbannis
# 3  
Old 05-26-2012
Try this ...
Code:
awk '{
if(NR>2){
if(NF==4) { print $1,$2,$3,$4 } else {
printf $1","$2","$3",";
for(i=4;i<=NF;i++) {
printf i==NF?$i"\n":$i FS
}
}} else {print} }' OFS="," filename

This User Gave Thanks to pravin27 For This Post:
robinbannis
# 4  
Old 05-26-2012
Assuming you can remove the ===, as I'm not really sure why they're there in a csv. Would make your headers 2+ spaces apart as well for consistency:

Code:
$ cat test
TradeDate Name    SecurityMnc  ReasonDesc
20120501   Robin     ABC      FO System Defect
20120502   Robin     ABC      FO System Defect

$ cat test | sed 's/\ \ \ */~/g'
TradeDate Name~SecurityMnc~ReasonDesc
20120501~Robin~ABC~FO System Defect
20120502~Robin~ABC~FO System Defect

$ cat test | sed 's/\ \ \ */~/g' | awk -F"~" '{print $1","$2","$3","$4}'
TradeDate Name,SecurityMnc,ReasonDesc,
20120501,Robin,ABC,FO System Defect
20120502,Robin,ABC,FO System Defect

Could make ~ whatever character or whatnot, I just used that arbitrarily.
This User Gave Thanks to Vryali For This Post:
robinbannis
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy txt file into excel vbscript

Hi everybody, I am working on Windows 10 and using cygwin. I have a vbscript as follows: Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set wb1 = objExcel.Workbooks.open("\\files\share\path\file1.xlsx") wb1.Worksheets("Sheet1").Range("A1:CR89").Clear Set... (0 Replies)
Discussion started by: supernono06
0 Replies

2. Shell Programming and Scripting

Shell Script for converting file to Excel or CSV

Hi I have a dat file which has "n" number of columns. The file is delimited. The number of columns keep varying as the file is generated out of DB queries. Could you please help me in writing a script which will generate a XLS or CSV file out of the dat file. (5 Replies)
Discussion started by: Vee
5 Replies

3. Shell Programming and Scripting

Converting specific Excel file tabs to CSV in Python

Hi list, This is probably something really simple, but I am not particularly familiar with Python so I thought I would ask as I know that python has an excel module. I have an excel document with multiple tabs of data and graphs. One of the tabs is just data which I require to have dumped to... (8 Replies)
Discussion started by: landossa
8 Replies

4. Shell Programming and Scripting

KSH - Text from input file truncated while converting it to excel

Dear Members, I am using the attached script to convert a input file delimited by '|' to excel. However, while processing the attribute change_reason, the whole content of the text under change_reason is not displayed completely in the cell in excel. It is truncated after only first few words.... (1 Reply)
Discussion started by: Yoodit
1 Replies

5. Shell Programming and Scripting

Converting txt file in csv

HI All, I have a text file memory.txt which has following values. Average: 822387 7346605 89.93 288845 4176593 2044589 51883 2.47 7600 i want to convert this file in csv format and i am using following command to do it. sed s/_/\./g <... (3 Replies)
Discussion started by: mkashif
3 Replies

6. UNIX for Dummies Questions & Answers

Converting txt file to csv file

Hi, Using rsync, I've sent the output to a text file. This is the text file : Pls help me on converting this text file to a csv file. Probably a script or sth to convert the text file to a csv file. (3 Replies)
Discussion started by: anaigini45
3 Replies

7. UNIX for Dummies Questions & Answers

Save Excel file as .txt in UNIX format

I have some files created in Excel that have to be saved as .txt files in order to load them into our accounting system. I can save the files as .txt files through Excel, but I then have to open them in TextPad and do a save as to change the Format from PC to UNIX. Is there a way to skip this step... (2 Replies)
Discussion started by: jroyalty
2 Replies

8. UNIX for Advanced & Expert Users

Problem in converting password protected excel file to csv file in unix

I need to convert a password protected excel file which will be in UNIX server to a comma separated file. For this I need to open the excel file in UNIX box but the UNIX box doesn't prompt for password instead it is opened in an encrypted manner. I could manually ftp the excel file to local... (2 Replies)
Discussion started by: Devivish
2 Replies

9. UNIX for Advanced & Expert Users

converting a .txt file to comma delimeted file

Dear all, I have a file with 5L records. one of the record in the file is as shown below. MARIA THOMAS BASIL 1000 FM 1111 MD ... (1 Reply)
Discussion started by: OSD
1 Replies

10. Shell Programming and Scripting

converting .txt to comma delimeted file

Dear all, I have a file with 5L records. one of the record in the file is as shown below. MARIA THOMAS BASIL 1000 FM 1111 MD GHANA YY 77354 4774 99999999 1234567 I need to convert this record in below format "","","","","MARIA","THOMAS","BASIL","","1000 FM 1111 MD","STE... (1 Reply)
Discussion started by: OSD
1 Replies
Login or Register to Ask a Question