create a excel file using shell script

Question

I have a bunch of text files in a directory and i need to read them and extract information and keep in an excel or text file

name1_1.txt

count: 10
totalcount: 30
percentage:33
total no of a's: 20
total no of b's: 20
etc...

name2_2.txt

count: 20
totalcount: 40
percentage:50
total no of a's: 10
total no of b's: 30
etc...

etc...

output

             name1        name2
 count        10           20
 totalcount   30           40
 percentage   33           50

I want the output to keep in file called(example.txt or .csv) in the same directory. can i get help in this?

here what i tried in writing a shell script,but can't create tab separated and output to file what i needed

 #$ -S /bin/bash


 for sample in *.txt; do
    header=$(echo ${sample} | awk '{sub(/_/," ")}1'| awk '{print $1}')
    echo -en $header"\t"
 done
 echo -e ' \t '
 echo "count"
 for sample in *.txt; do
    grep "count:" $sample | awk -F: $'\t''{print $2}'
 done
 echo "totalcount"
 for sample in *.txt; do
    grep "totalcount:" $sample | awk -F: $'\t''{print $2}'
 done
 echo "percentage"
 for sample in *.txt; do
    grep "percentage:" $sample | awk -F: $'\t''{print $2}'
 done

n0741337 · Accepted Answer · 2014-01-03 23:22:03Z

1

You can see if this does what you want:

awk -F":" 'BEGIN { DELIM="\t" } \
    last_filename != FILENAME { \
        split( FILENAME, farr, "_" ); header = header DELIM farr[1]; \
        last_filename = FILENAME; i=0 } \
    $1 ~ /count/ || $1 ~ /totalcount/ || $1 ~/percentage/ \
        { a[i++]= NR==FNR ? $1DELIM$2 : a[i]DELIM$2 } \
    END { print header; for( j in a ) { print a[j] } }' name*.txt

where I've tried to break it up into multiple lines for "easier" reading. You can just remove the trailing "\" from each line and concat each line to re-make it as a one-liner. If I edit this anwswer one more time, I'll just make it an executable awk file.

The awk is setting a DELIM for the output to tab in the BEGIN block.
The FILENAME is cleaned up and appended to the header
It takes the column names from the first file, as well as the data and puts that into an array at i. For each next file, it just appends the data.
At the END, the header is output, and then the contents of the array are output.

I get the following output then:

        name1   name2
count    10      20
totalcount       20      40
percentage      33      50

This will now only take the columns indicated in the data, provided $1 is an exact match for the count, totalcount and percentage.

edited Jan 3, 2014 at 23:22

answered Jan 3, 2014 at 22:44

n0741337

2,5242 gold badges16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

abh Over a year ago

i have some other lines in the text file,which i don't want to take(like for example which i edited in the original files) @n0741337

n0741337 Over a year ago

Okay - I think I can deal with that too - one mo'

n0741337 Over a year ago

@abh - Add a > example.txt or > example.csv to the end of the awk command I posted ( your choice depending on how you want the file interpreted by other programs ). It's currently outputting to stdout. You can change the DELIM value if you want something other than tab.

Collectives™ on Stack Overflow

create a excel file using shell script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related