Generate files and use csv data to replace multiple variables in a template

UNIX for Beginners Q & A
1

I have a source csv file consists of first field as variable name, and the rest are site-specific information (converted from excel file, where site -specific values in columns). I am trying to create a file for every site using a template and replace the multiple variables with values from the csv. I used sed but it takes too many find/replace substitutions. What is the best way to use in my script? Thanks in advance.

Sample csv:
---------------

$ cat multiple-replace-Book1.csv 
var_Site,SITE-A1,SITE-A2,SITE-A3,SITE-B1,SITE-B2,SITE-C2,SITE-C3,SITE-C4
var_Name,HOST-A1,HOST-A2,HOST-A3,HOST-B1,HOST-B2,HOST-C2,HOST-C3,HOST-C4
var_Ipaddress,11.11.11.1,11.11.11.2,11.11.11.3,22.22.22.1,22.22.22.3,33.33.33.1,33.33.33.2,33.33.33.3
var_Port,22,22,22,22,22,22,22,22
var_Location,Bay1,Bay5,Bay3,Bay1,Bay2,Bay4,Bay2,Bay3
var_City,London,London,London,Rome,Rome,Paris,Paris,Paris
var_Comment,Comment1,Comment2,Comment3,Comment4,Comment5,Comment6,Comment7,Comment8
var_Data,Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8
var_String,String1,String2,String3,String4,String5,String6,String7,String8

Template content:
-----------------------

Test connection to $var_Host from $var_Location in $var_City
Use $var_Ipaddress $var_Port to connect
Note: Apply this $var_Data and $var_String
Check: $var_Comment

Desired output file = $var_Site-file.txt
-------------------------

SITE-A1-file.txt (with site-A1 values)
SITE-A2-file.txt (with site-A2 values)
2

Where is the $var_Host info to be found?

--- Post updated at 17:39 ---

Assuming / inferring you mixed up var_Host and var_Name , how far would this:

awk -F, '
        {sub (/var_/, "", $1)
         MD = NF
         for (i=2; i<=NF; i++) DATA[$1, i] = $i
        }

END     {for (i=2; i<=MD; i++)  {print "Test connection to", DATA["Name", i], "from", DATA["Location", i], "in", DATA["City", i] ORS \
                                 "Use", DATA["Ipaddress", i], DATA["Port", i], "to connect" ORS \
                                 "Note: Apply this", DATA["Data", i], "and", DATA["String", i] ORS \
                                 "Check:", DATA["Comment", i]  > (DATA["Site", i] "-file.txt")
                                }
        }
 ' file

get you?

Be aware that your sampe file seems to be UTF-8 Unicode (with BOM) text - remove the BOM before applying this solution or adapt for the BOM.

1 Like
3

Maybe you should reverse your initial matrix : ....

$cat my
var_Site,SITE-A1,SITE-A2,SITE-A3,SITE-B1,SITE-B2,SITE-C2,SITE-C3,SITE-C4
var_Name,HOST-A1,HOST-A2,HOST-A3,HOST-B1,HOST-B2,HOST-C2,HOST-C3,HOST-C4
var_Ipaddress,11.11.11.1,11.11.11.2,11.11.11.3,22.22.22.1,22.22.22.3,33.33.33.1,33.33.33.2,33.33.33.3
var_Port,22,22,22,22,22,22,22,22
var_Location,Bay1,Bay5,Bay3,Bay1,Bay2,Bay4,Bay2,Bay3
var_City,London,London,London,Rome,Rome,Paris,Paris,Paris
var_Comment,Comment1,Comment2,Comment3,Comment4,Comment5,Comment6,Comment7,Comment8
var_Data,Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8
var_String,String1,String2,String3,String4,String5,String6,String7,String8
$

might be easier to read for further processing if you take it that way :

$tr , "\n" <my | pr -t -9 -s" "
var_Site var_Name var_Ipaddress var_Port var_Location var_City var_Comment var_Data var_String
SITE-A1 HOST-A1 11.11.11.1 22 Bay1 London Comment1 Data1 String1
SITE-A2 HOST-A2 11.11.11.2 22 Bay5 London Comment2 Data2 String2
SITE-A3 HOST-A3 11.11.11.3 22 Bay3 London Comment3 Data3 String3
SITE-B1 HOST-B1 22.22.22.1 22 Bay1 Rome Comment4 Data4 String4
SITE-B2 HOST-B2 22.22.22.3 22 Bay2 Rome Comment5 Data5 String5
SITE-C2 HOST-C2 33.33.33.1 22 Bay4 Paris Comment6 Data6 String6
SITE-C3 HOST-C3 33.33.33.2 22 Bay2 Paris Comment7 Data7 String7
SITE-C4 HOST-C4 33.33.33.3 22 Bay3 Paris Comment8 Data8 String8
$

And then just

$tr , "\n" <my | pr -t -9 -s" " | (read a; while read a; do set -- $a ; echo "Test connection to $2 from $5 in $6
> Use $3 $4 to connect
> Note: Apply this $8 and $9
> Check: $7"; done )
Test connection to HOST-A1 from Bay1 in London
Use 11.11.11.1 22 to connect
Note: Apply this Data1 and String1
Check: Comment1
Test connection to HOST-A2 from Bay5 in London
Use 11.11.11.2 22 to connect
Note: Apply this Data2 and String2
Check: Comment2
Test connection to HOST-A3 from Bay3 in London
Use 11.11.11.3 22 to connect
Note: Apply this Data3 and String3
Check: Comment3
Test connection to HOST-B1 from Bay1 in Rome
Use 22.22.22.1 22 to connect
Note: Apply this Data4 and String4
Check: Comment4
Test connection to HOST-B2 from Bay2 in Rome
Use 22.22.22.3 22 to connect
Note: Apply this Data5 and String5
Check: Comment5
Test connection to HOST-C2 from Bay4 in Paris
Use 33.33.33.1 22 to connect
Note: Apply this Data6 and String6
Check: Comment6
Test connection to HOST-C3 from Bay2 in Paris
Use 33.33.33.2 22 to connect
Note: Apply this Data7 and String7
Check: Comment7
Test connection to HOST-C4 from Bay3 in Paris
Use 33.33.33.3 22 to connect
Note: Apply this Data8 and String8
Check: Comment8
 $

Of course you would have to adapt it to your need especially if the Comment contains some space, but i gave this shot just for the idea
The first "read a" is just to skip the header line.

2 Likes
4

I was able to use the solution provided by RudiC for the required file output (yes, also after removing the BOM from source file).
I'm currently using the solution provided by ctsgnb on other projects where the data is transposed.

Thank you very much!