Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need help to write a shell script to convert text 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 Need help to write a shell script to convert text file to excel file.
# 1  
Old 10-12-2015
Need help to write a shell script to convert text file to excel file.
Hi Everyone,
I want your help to write a script which will take text file as input and on the basis of delimiter ":"script will create excel sheet.

Example input:
Code:
IpAdress:InstanceName:Port:ServerName
10.255.255.1:abc:2232:xyz_abc

Output should be an excel sheet like below:
Code:
Column 1	Column2		Column3 	Column4
IpAdress	InstanceName	Port		ServerName
10.255.255.1	abc		2232		xyz_abc


I have tried to convert it in .csv but in that case the whole output is coming in a single column while I want separate columns.
Last edited by Franklin52; 10-12-2015 at 11:37 AM.. Reason: Please use code tags
# 2  
Old 10-12-2015
What have tried so far?
# 3  
Old 10-12-2015
Hi,


The requirement is read a configuration file, get all the information and make a spread sheet. I am able to get all above given information with ":" delimeter but when I am creating .csv file I am getting all data in a single column while I want separate column for each field. I searched over internet but did not get any solution.
# 4  
Old 10-12-2015
You still havent shown us what you did nor its output so how do you want us to guess?
What OS are you using and what shell?
# 5  
Old 10-12-2015
Quote:
Originally Posted by akabhinav18
but when I am creating .csv file I am getting all data in a single column while I want separate column for each field.
It might help you to know that "CSV" stands for "comma separated value", not "colon separated value". Replace the colon separators with comma (or semicolon) separators and Excel should have no problems separating the fields into separate cells.

You might want to read RFC 4180 ("Common Format and MIME Type for Comma-Separated Values (CSV) Files") for more information about delimiter-separated file formats.

I hope this helps.

bakunin
# 6  
Old 10-12-2015
Hi VBE,
I am using the below commands to get the relevant information and to convert in .csv
Code:
find  <Path> -name abc.conf |xargs egrep -i "Virtual|ServerName" | tr '\n' ' '|\
                                  sed 's/Virtual>/\n/g'|\
                                  awk '{print $2 $3 $4 }'|\
                                  sed 's/>./:/g' |\
                                  sed 's/[/]tp/tp/g'|\
                                  sed 's/[/]abc.conf//g'|\
                                  sed 's/ServerName//g' > test.csv

by using this I am able to generate the file but all the data is in same column.
Outout:

Code:
Column1 
10.255.255.1:abc:2232:xyz_abc

Note: I have tried coma as well instead of colon.

Moderator's Comments:
Mod Comment edit by bakunin: Please use CODE-tags for code, terminal output and file contents. It is easier to read that way.
Last edited by bakunin; 10-12-2015 at 01:49 PM..
# 7  
Old 10-12-2015
Quote:
Originally Posted by akabhinav18
Code:
find  <Path> -name abc.conf |xargs egrep -i "Virtual|ServerName" | tr '\n' ' '|\
                                  sed 's/Virtual>/\n/g'|\
                                  awk '{print $2 $3 $4 }'|\
                                  sed 's/>./:/g' |\
                                  sed 's/[/]tp/tp/g'|\
                                  sed 's/[/]abc.conf//g'|\
                                  sed 's/ServerName//g' > test.csv

Ohmigod!

I haven't bothered to try to understand this, it is definitely wrong. sed ... | sed ... is never corect because it could be written as a single command, same goes for awk .. | sed ...

Provide a sample output of one of these "abc.conf" files you are searching for and explain how it should be transformed to what you want to achieve. Most probably a single awk- or sed-command will suffice.

I hope this helps.

bakunin
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 Excel File (xls) to tab delimited text file on AIX

Hi i have a problem in my job i try to convert an excel file (xls extention) to text file (tab delimited), but no result with this comand cat xxx.xls > xxx.txt Do you have eny idea? PS: sorry for my english Thanks!! (4 Replies)
Discussion started by: frisso
4 Replies

2. 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

3. Shell Programming and Scripting

Need to convert delimited text file in UNIX to an Excel file

Dear Users , Need to convert delimited text files in UNix server to an Excel file and move the excel file to Windows environment. Am trying to automate the whole process. Can anyone share the ideas,if they have done similar ones before...Thanks -Meera (1 Reply)
Discussion started by: meerakrish
1 Replies

4. Shell Programming and Scripting

Shell Script for copying text file to Excel Sheet

Hi, I want to write a program to copy a log file to Excel sheet. Excel sheet has four columns MethodName , Code , Description, Details and Time. I want to pick these info from text file and put it in excel sheet. here is how the text file looks - 04.17.2014 08:06:12,697... (1 Reply)
Discussion started by: hershey
1 Replies

5. Shell Programming and Scripting

Convert excel file to PDF file using shell script

Hi All, Is it possible to convert the excel file to PDF file(Without loosing any format) using unix shell scripting ??? If yes Kindly help me on the code Thanks in advance!!! (5 Replies)
Discussion started by: Balasankar
5 Replies

6. Shell Programming and Scripting

How to write text file data to excel using UNIX shell script?

Hi All, I have the requirement in unix shell script. I want to write the "ls -ltr" command out put to excel file as below. Input :text file data : drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x 2 apx aim 4096 Nov 29 18:40 drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x... (10 Replies)
Discussion started by: Balasankar
10 Replies

7. Shell Programming and Scripting

Need help on inserting data from text file to excel using shell script

Hi, Please help me on this. I want to insert data from text file to excel using shell script nawk -v r=4 -v c=4 -v val=$a -F, 'BEGIN{OFS=","}; NR != r; NR == r {$c = val; print}' "file.csv" I used above one to insert $a value in 4th row, 4th column in an excel file.csv and it... (3 Replies)
Discussion started by: suman.frnz
3 Replies

8. Shell Programming and Scripting

How to convert excel file to csv file or text file?

Hi all, I need to find a way to convert excel file into csv or a text file in linux command. The reason is I have hundreds of files to convert. Another complication is the I need to delete the first 5 lines of the excel file before conversion. so for instance input.xls description of... (6 Replies)
Discussion started by: johnkim0806
6 Replies

9. Shell Programming and Scripting

a shell script to generate an excel sheet from a text file..

hi, i have a text file that looks like this! i want to generate an excel sheet out of it, removing all the junk data except the addresses that look like . Arrow Electrical Services Rotating Machinery, Electrical Contracting & Mining Specialists Onsite maintenance, breakdown... (8 Replies)
Discussion started by: vemkiran
8 Replies

10. 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
Login or Register to Ask a Question