Need help to write a shell script to convert text file to excel file.

Shell Programming and Scripting
1

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:

IpAdress:InstanceName:Port:ServerName
10.255.255.1:abc:2232:xyz_abc

Output should be an excel sheet like below:

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.

2

What have tried so far?

3

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

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

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

Hi VBE,
I am using the below commands to get the relevant information and to convert in .csv

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:

Column1 
10.255.255.1:abc:2232:xyz_abc

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

7

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

8

Why don't you select "colon" as the field delimiter in EXCEL's "Text Wizard"?

9

Hi Bakunin,
The scenario is; there are multiple directories and inside the directories there would be a conf file called "abc.conf". I need to get some information from the conf file and export the information to an excel file. (Note: one info in one column).
example of abc.conf:

<VirtualHost 10.251.245.1:2323>
DocumentRoot "/apps/abc/web/apache/abc"
ServerName xyz_abc
.
.
.
.
</VirtualHost>

Output should be like below:

Column 1	Column2		Column3 	Column4
IpAdress	InstanceName	Port		ServerName
10.251.2545.1	abc		2323		xyz_abc
.
.
.