Help with script to convert rows to columns

Shell Programming and Scripting
1

Hello
I have a large database with the following structure:

Headword=Gloss1;Gloss2;Gloss3

The Glosses are separated by a

;

What I need is to reduce the multiple glosses on each row to columns

Headword=Gloss1
Headword=Gloss2
Headword=Gloss3

I had written the following script in awk to do the job

BEGIN{FS="="}
{for (i=2;i<=NF;i++) {printf $1"=";print $i}}

However it does not provide the necessary result. I wonder where the awk script has gone wrong
A small sample is appended below for testing

=;
=;
=;
=;
=;
=;
=;
=;
=;
=;;
=;;
=;;
=;
=;
=;
=
=;
=;
=;
=;
=;
=;
=;
=;
=;
=;
=;
=;
=;
=;
=
=;;
=;
=;
=;
=;
=;
=;
=;
=;
=;
=;;

Please note that it is possible that headword in the database may map to a single gloss as in

=

Many thanks in advance

2

You weren't too far off. Try FS="[;=]" .

1 Like
3

Thanks a lot. It worked
How stupid of me!!!

4

Note that although your printf happens to work with the data you're using, it is dangerous to assume that no characters in data you're printing will ever be interpreted as format string control characters. You might want to consider one of the following as an alternative to your current code:

BEGIN {	FS = "[=;]"
}
{	for(i = 2; i <= NF; i++)
		printf("%s=%s\n", $1, $i)
}

or:

BEGIN {	FS = "[=;]"
	OFS = "="
}
{	for(i = 2; i <= NF; i++)
		print $1, $i
}

or:

BEGIN {	FS = "[=;]"
}
{	for(i = 2; i <= NF; i++)
		print $1 "=" $i
}
2 Likes
5

Many thanks for the warning. The solutions you have provided are elegant and will ensure data integrity.
I tested all three and the results are excellent.
Thanks for taking time off to write these little gems.

6

Just as a different perspective, if it is in the database, can you not use SQL for the task too?