Find and replace variables using a csv table


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Find and replace variables using a csv table
# 1  
Old 12-06-2012
Find and replace variables using a csv table

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:
Code:
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  
Old 12-06-2012
try:
Code:
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.
This User Gave Thanks to rdrtx1 For This Post:
# 3  
Old 12-06-2012
Wow, that's cool
I guess I can pipe to sed and do the magic now Smilie
Many thanks for your help !
# 4  
Old 12-06-2012
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  
Old 12-06-2012
Hi Don,
Thanks for your offer. Would be great to get it all in awk.
Here's an example template file:
Code:
                    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:
Code:
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:

Code:
                    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  
Old 12-06-2012
try:
Code:
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


Last edited by rdrtx1; 12-06-2012 at 07:49 PM..
# 7  
Old 12-06-2012
Unfortunately I get an error:
Code:
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.

Last edited by Franklin52; 12-07-2012 at 10:45 AM.. Reason: Please use code tgas
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

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

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... (3 Replies)
Discussion started by: apalex
3 Replies

2. Shell Programming and Scripting

Update the table using values from a csv file

i want to run update query for oracle which is in up.sql taking values from a.csv. I have implemented shell script to do it. extn="perl" ls -1 | while read file do echo "$file,$extn" > a.csv done up.sql contains update file_list set filename=$1 where extn=$2; The code to update is... (2 Replies)
Discussion started by: millan
2 Replies

3. UNIX for Dummies Questions & Answers

Find & Replace identifiers using a conversion table

Hi ! I have input.tab with one column containing Item IDs under a number format (the second column is the Location of this item): Location Item ID rack1 12; 35; 43 rack35 23; 894; 5478; 98 etc... (The number of Items per row is variable. Item IDs in a same field are... (17 Replies)
Discussion started by: lucasvs
17 Replies

4. Shell Programming and Scripting

Convert file in csv or table

Hi there, i have a file like that in attachment (PLEVA3_280711_SAP.txt), i would extract some basic information from it and report in a new file or table like this: i try to use bash and i extract the single object in this way (see attach scriptino.sh), but i receive a strange... (5 Replies)
Discussion started by: alen192
5 Replies

5. UNIX for Dummies Questions & Answers

Storing data from a table into a csv file

Hi I need to write a bash script to take the data stored in 3 oracle tables .. and filter them and store the results in a csv file. It is an Oracle database Thank you (1 Reply)
Discussion started by: ladyAnne
1 Replies

6. Shell Programming and Scripting

Converting .xls into .csv and find & Replace

Hi All, Please give me the solution to the following ASAP. 1) Converting the .xls into .csv Script i tried, mv hello.xls hello.csv The above given script converting the .xls file into .csv successfully. But after i run the below unix command I am no able to open the .csv file, its giving... (4 Replies)
Discussion started by: velava
4 Replies

7. Shell Programming and Scripting

find & replace comma in a .csv file.

HI, Please find the text below. I receive a .csv file on server. I need the comma(,) in the second column to be replaced by a semi-colon( ; ). How to do it. Please help. Sample text: "1","lastname1,firstname1","xxxxxx","19/10/2009","23/10/2009","0","N","Leave"... (2 Replies)
Discussion started by: libin4u2000
2 Replies

8. Shell Programming and Scripting

csv to table one-liner

I've googled a lot on this, but could not fine a simple one-liner to do this. I have a .csv file that looks like this: Header one Header two Header three col1,col2,col3 short data, very long data, dataIf I use sed and change the comma to tab, being the colums of variable length I don't get a... (6 Replies)
Discussion started by: ahsog
6 Replies

9. UNIX for Dummies Questions & Answers

How do I read/find/replace fields in a csv datafile?

hello. I'm somewhat a novice here so please be patient. My stumbling block when loading csvs into ORACLE tables is this: I need to read a csv datafile, check several fields in each line, and if any of stated fields contain A ZERO only then replace it with a null/blank character. I had a... (9 Replies)
Discussion started by: MrCarter
9 Replies

10. Shell Programming and Scripting

HTML table to CSV

Hi !! I have HTML Tables through which i want to generate graphs, but for creating graphs i need the file in CSV format so can anyone can please help me in how can i convert my HTML table file to CSV format. Thanks in Advance (2 Replies)
Discussion started by: i_priyank
2 Replies
Login or Register to Ask a Question