Find and replace variables using a csv table

Shell Programming and Scripting
1

I have a flat file (template) where I want to replace variables based upon a value in another file (csv).
The variables in the template are named %VAR_X_z%
The values are in the csv file and X is field 0 of each line and y field 1 and up.

Example of the csv:

Badidas, 13.00, 12.00, 11.00, 10.00
Cuma, 12.00, 11.00, 10.00, 9.00
Dswiss, 23.00, 22.00, 18.00, 16.00

Now I want to replace each variable field in the template with the values of the csv file.
%VAR_Cuma_1 becomes 12.00
%VAR_Cuma_2 becomes 11.00
etcetera
What would be the best way to do this ? Awk, sed or maybe a combo of both ?

2

try:

awk '{for (i=2; i<=NF; i++) print "VAR_"$1"_"i-1"="$i}' FS=" *, *" infile

pipe to sh, use back quotes, or wrap in $(...) to set variables.

1 Like
3

Wow, that's cool
I guess I can pipe to sed and do the magic now :cool:
Many thanks for your help !

4

Hi biscayne,
If you can show us an example of your template file and a corresponding sample of the output you want to get (based on your example template and CSV files), we can probably give you an awk script that will do it all in one step.

5

Hi Don,
Thanks for your offer. Would be great to get it all in awk.
Here's an example template file:

                    Rateband prices per 01-01-2012
=========================================
PRODUCT with 5+ 10+ 50+ and 100+ prices
=========================================
Original 2GB: %VAR_USOR2GB_1% %VAR_USOR2GB_2% %VAR_USOR2GB_3% %VAR_USOR2GB_4%
Original 4GB: %VAR_USOR4GB_1% %VAR_USOR4GB_2% %VAR_USOR4GB_3% %VAR_USOR4GB_4%
Original 8GB: %VAR_USOR8GB_1% %VAR_USOR8GB_2% %VAR_USOR8GB_3% %VAR_USOR8GB_4%

Thanks,
P

And this is an example of the csv file:

USOR2GB, 10.25, 9.23, 8.47, 8.21
USOR4GB, 10.55, 9.53, 8.69, 8.58
USOR8GB, 10.75, 9.83, 8.97, 8.91

---------- Post updated at 10:41 PM ---------- Previous update was at 10:33 PM ----------

Just for the record.
This is how the example output should look like:

                    Rateband prices per 01-01-2012
=========================================
PRODUCT with 5+ 10+ 50+ and 100+ prices
=========================================
Original 2GB: 10.25 9.23 8.47 8.21
Original 4GB: 10.55 9.53 8.69 8.58
Original 8GB: 10.75 9.83 8.97 8.91

The actual template is an xml file (that becomes an sla file), so a lot more sophisticated than this example, but I suppose you get the point.

6

try:

awk 'FNR==NR {for (i=2; i<=NF; i++) a["%VAR_"$1"_"i-1"%"]=$i;next}
{for(i=1;i<=NF;i++) $i=a[$i] ? a[$i] : $i}1' FS=" *, *" csv_file FS="[ \t]" template
7

Unfortunately I get an error:

awk 'FNR==NR {for (i=2; i<=NF; i++) a["%VAR_"$1"_"i-1"%"]=$i;next}
{for(i=1;i<=NF;i++) printf a[$i]? a[$i]" " : $i" "; print ""}' FS=" *, *" prices.csv FS="[ \t]" temp-in.sla > temp-out.sla
awk: run time error: not enough arguments passed to printf("CH="%VAR_USOR2GB_2%"/> ")
	FILENAME="temp-in.sla" FNR=347 NR=354

I don't know why, the variable seems to be correct.

8

When trying rdrtx1's script in message #6 in this thread on OS X, I get errors about printf not recognizing %V as a format specifier.

The following script seems to work with your input files. (Note that I also changed the 01-01-2012 in the 1st line in your template to %VAR_DATE_1% and added a line to your .csv file for testing purposes. If you don't make this change to the template, it won't affect your output.) For testing I used the following script:

#!/bin/ksh
awk -F ', ' 'FNR==NR{
        for(i = 1; i < NF; i++)
                v["VAR_" $1 "_" i] = $(i + 1)
        next
}
{       for(i = 2; i <= NF; i += 2)
                if($i in v)
                        $i = v[$i]
        print $0
}' prices.csv FS='%' OFS="" template.txt

When I run this script given a file named prices.csv that contains:

DATE, 12-06-2012
USOR2GB, 10.25, 9.23, 8.47, 8.21
USOR4GB, 10.55, 9.53, 8.69, 8.58
USOR8GB, 10.75, 9.83, 8.97, 8.91

and a file named template.txt that contains:

                    Rateband prices per %VAR_DATE_1%
=========================================
PRODUCT with 5+ 10+ 50+ and 100+ prices
=========================================
Original 2GB: %VAR_USOR2GB_1% %VAR_USOR2GB_2% %VAR_USOR2GB_3% %VAR_USOR2GB_4%
Original 4GB: %VAR_USOR4GB_1% %VAR_USOR4GB_2% %VAR_USOR4GB_3% %VAR_USOR4GB_4%
Original 8GB: %VAR_USOR8GB_1% %VAR_USOR8GB_2% %VAR_USOR8GB_3% %VAR_USOR8GB_4%

it produces the output:

                    Rateband prices per 12-06-2012
=========================================
PRODUCT with 5+ 10+ 50+ and 100+ prices
=========================================
Original 2GB: 10.25 9.23 8.47 8.21
Original 4GB: 10.55 9.53 8.69 8.58
Original 8GB: 10.75 9.83 8.97 8.91

Note, however, that this script will not work if your template file contains any other percent characters. If it does contain percent characters that are not included in macros that are to be expanded, a more complex script would be required.

1 Like
9

Hi Don,
Many thanks.
I just ran it on the actual xml template (Scribus sla file) and it works !
Great job.
Many thanks for your help.
:b: