AWK - create a single field from many 1

[mzilikazi]

I need to convert a .xls file to html. Please do not suggest File>Save As HTML.
I've dumped the .xls file to .txt:

3300-1504-000	short item# description	 $51.00 	 $22.46 	 $12.44   $6.32 
0640-0219-000	a longer different item# description  	 $903.00 	 $296.58 	 $215.09 
0640-0219-003	Yet another item# description of a different length	 $177.52

I wish to add tags to the beginning and end of each field on each line like so:

<tr><td>0640-0003-03A</td><td>Description of item# with variable length</td><td>$478.00</td><td>$362.30</td><td>$95.00</td><td>$185.00</td></tr>

The problem:
I wish to refer to Description of item# with variable length as a single field. Let's call it $DESCRIPTIONFIELD
Constants:
# The beginning of $DESCRIPTIONFIELD is always after the end of the first field.
# The end of $DESCRIPTIONFIELD always precedes a dollar sign '$'

How to set $DESCRIPTIONFIELD ? Thanks alot!

 

[Annihilannic]

How did you "dump" it to a text file? If you simply copy and paste the contents of an Excel spreadsheet into a text file, the fields are normally separated by tabs, so it should be a simple matter to search and replace tabs with "</td><td>", put "<tr><td>" on the beginning of the line and "</td></tr>" on the end.

Annihilannic.

 

[mzilikazi]

Brilliant! Thanks Annihilannic my immediate issue is resolved.

awk '{sub(/^/,"<tr><td>");gsub("\t","</td><td>");print $0,"</td></tr>"}' file.tx

Still, I wonder...what if it wasn't tab separated? These variables will be useful in the future.

 

[Annihilannic]

Something like this I guess:

awk '
    {
        desc=$0
        sub(/^[^[:space:]]+[[:space:]]+/,"",desc)
        sub(/[[:space:]]+\$.*/,"",desc); print desc
    }
' file.tx

Although I wouldn't like to depend too much on what people put in a free text field. If there are a consistent number of fields after the description you could remove them by assigning nothing to $NF, $(NF-1), etc. which may be safer (after saving the values you want to keep of course).

Is mzilikazi Swahili?

Annihilannic.

 

[mzilikazi]

Is mzilikazi Swahili?

Well Zulu anyway.

http://en.wikipedia.org/wiki/Mzilikazi

 

[Annihilannic]

Ah, thanks for that...

Annihilannic.