1

I am writing a shell script which takes a text file as input and generate a spreadsheet as output. I have single text file similar to the one shown below.

Name:John
Age:30
Gender:male
Name:Bob
Age:65
Gender:Male

and the pattern goes on repeating several times. I have to create a spreadsheet with name, age and gender as fields as shown below.

Name Age Gender
John 30  Male
Bob  65  Male

Help would be appreciated.

1

Create a tab delimited text file ready to import into excel:

printf "Name\tAge\tGender\n" > outfile
cut -d: -f2- <infile | paste - - - >> outfile
  • printf creates the tab delimited header
  • cut from the second field to end of line
  • paste formats the output into three columns which are tab delimited and amends the outfile

You could also group the commands and redirect only once:

{
printf '%s\t%s\t%s\n' Name Age Gender
cut -d: -f2- <infile | paste - - -
} > outfile
  • @fdo your code is working fine with fixed rows. I realized this later that the number of rows in my text are always varying. Basically I am using xidel command to extract text from html page. If html page changes, text changes and so does the number of rows. So I have to match the field 1 with the header first, if match is found then insert field 2 in the spreadsheet under that header. I have been trying to do this but couldn't code it by now. – Swatesh Pakhare Nov 17 '15 at 17:51
0

Well the following works if the file always has three lines for each record.

echo "Name Age Gender" >>OUTPUT.txt; xargs -n 3 < INPUTFILE.txt |sed 's/Name:\|Age:\|Gender://g' >> OUTPUT.txt

I'm certain there are more eloquent ways of handling this.

  • @don_crissti yes man the output is distorted if there is a space between field values as you illustrated above. Is there any other way to do it. – Swatesh Pakhare Nov 10 '15 at 21:01
  • @SwateshPakhare why don't you use another character as the delimiter? e.g '|' or ','? – faadi Nov 10 '15 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.