Visit The New, Modern Unix Linux Community


Pass some data from csv to xml file using shell/python


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Pass some data from csv to xml file using shell/python
# 1  
Error Pass some data from csv to xml file using shell/python

Hello gurus,
I have a csv file with bunch of datas in each column. (see attached)

Now I have an .xml file in the structure of below:
Code:
?xml version="1.0" ?>
<component id="root" name="root">
    <component id="system" name="system">
        <param name="number_of_A" value="8"/>
        <param name="number_of_B" value="0"/>
        <param name="number_of_C" value="0"/>
        <param name="number_of_D" value="4"/> <!-- This number means how many L2 clusters in each cluster there can be multiple banks/ports -->
        <param name="number_of_E" value="0"/> <!-- This number means how many L3 clusters -->
        <param name="number_of_F" value="1"/>
        <param name="homogeneous_G" value="1"/><!--1 means homo -->
        <param name="homogeneous_H" value="1"/>
       <component id="system.core0" name="core0">
             <param name="RAS_size" value="32"/>                        
            <!-- general stats, defines simulation periods;require total, idle, and busy cycles for senity check  -->
            <!-- please note: if target architecture is X86, then all the instrucions refer to (fused) micro-ops -->
               <stat name="total_instructions" value="3765298576.0"/> <!-- CONX -->
               <stat name="N" value="3700000000.0"/> 
               <stat name="fp_instructions" value="64312000.0"/> 
            <stat name="branch_instructions" value="0"/>
            <stat name="branch_mispredictions" value="0"/>
               <stat name="load_instructions" value="1129589573.0"/> 
               <stat name="store_instructions" value="376529857.6"/> 
               <stat name="committed_instructions" value="3765298576.0"/>
               <stat name="committed_int_instructions" value="3700986576.0"/>
               <stat name="committed_fp_instructions" value="64312000.0"/> 
            <stat name="pipeline_duty_cycle" value="0.6"/>
            <stat name="L" value="100000"/>
            <stat name="idle_cycles" value="0"/>
            <stat name="busy_cycles"  value="100000"/>
            <stat name="ROB_reads" value="263886"/>
            <stat name="ROB_writes" value="263886"/>
            
            <stat name="rename_accesses" value="263886"/>
            <stat name="fp_rename_accesses" value="263886"/>
            
            <stat name="inst_window_reads" value="263886"/>
            <stat name="inst_window_writes" value="263886"/>
            <stat name="inst_window_wakeup_accesses" value="263886"/>
            <stat name="fp_inst_window_reads" value="263886"/>
            <stat name="fp_inst_window_writes" value="263886"/>
            <stat name="fp_inst_window_wakeup_accesses" value="263886"/>
            <!--  RF accesses -->
            <stat name="int_regfile_reads" value="1600000"/>
            <stat name="float_regfile_reads" value="40000"/>
            <stat name="int_regfile_writes" value="800000"/>
            <stat name="float_regfile_writes" value="20000"/>
            
            <stat name="function_calls" value="5"/>
            <stat name="context_switches" value="260343"/>
          
               <stat name="M" value="753059715.3"/> 
               <stat name="fpu_accesses" value="57880800.0"/> 
               <stat name="mul_accesses" value="57880800.0"/>
            <stat name="cdb_alu_accesses" value="1000000"/>
            <stat name="cdb_mul_accesses" value="0"/>
            <stat name="cdb_fpu_accesses" value="0"/>

My goal is to from the .csv, match the node name to this xml (truncate the "system." part if necessary) and then change the coresponding "value" in the xml and generate the new xml for each row in the csv.

I have tried to look into different posts in the net, but couldn't get them to work for my case.

Is the xml file indentation sensitive like python?

Thanks in advance.
# 2  
Can you show expected output for your sample data.
This User Gave Thanks to Chubler_XL For This Post:
# 3  
Expected new xml:

Code:
?xml version="1.0" ?> <component id="root" name="root">     
<component id="system" name="system">        
 <param name="number_of_A" value="$colum1"/>  
  <param name="number_of_B" value="$colum2"/>        
 <param name="number_of_C" value="$colum3"/>       
  <param name="number_of_D" value="$colum4"/> <!-- This number means how many L2 clusters in each cluster there can be multiple banks/ports -->      
   <param name="number_of_E" value="$colum5"/> <!-- This number means how many L3 clusters -->     
    <param name="number_of_F" value="$colum6"/>        
 <param name="homogeneous_G" value="$colum7"/><!--1 means homo -->       
  <param name="homogeneous_H" value="1"/>
<component id="system.core0" name="core0">            
  <param name="RAS_size" value="32"/>                                     <!-- general stats, defines simulation periods;require total, idle, and busy cycles for senity check  -->             <!-- please note: if target architecture is X86, then all the instrucions refer to (fused) micro-ops -->               
 <stat name="total_instructions" value="3765298576.0"/> 
<stat name="N" value="3700000000.0"/>            
<stat name="fp_instructions" value="64312000.0"/>             
 <stat name="branch_instructions" value="0"/>             
<stat name="branch_mispredictions" value="0"/>               
 <stat name="load_instructions" value="1129589573.0"/>               
  <stat name="store_instructions" value="376529857.6"/>                
 <stat name="committed_instructions" value="3765298576.0"/>               
 <stat name="committed_int_instructions" value="3700986576.0"/> 
<stat name="committed_fp_instructions" value="64312000.0"/>            
  <stat name="pipeline_duty_cycle" value="0.6"/>           
  <stat name="L" value="100000"/>          
   <stat name="idle_cycles" value="0"/>             
<stat name="busy_cycles"  value="100000"/>          
   <stat name="ROB_reads" value="263886"/>            
 <stat name="ROB_writes" value="263886"/>                          
<stat name="rename_accesses" value="263886"/>             
<stat name="fp_rename_accesses" value="263886"/>                          
<stat name="inst_window_reads" value="263886"/>            
 <stat name="inst_window_writes" value="263886"/>             
<stat name="inst_window_wakeup_accesses" value="263886"/>            
 <stat name="fp_inst_window_reads" value="263886"/>             
<stat name="fp_inst_window_writes" value="263886"/>            
 <stat name="fp_inst_window_wakeup_accesses" value="263886"/>     <!--  RF accesses -->             
<stat name="int_regfile_reads" value="1600000"/>             
<stat name="float_regfile_reads" value="40000"/>          
   <stat name="int_regfile_writes" value="800000"/>            
 <stat name="float_regfile_writes" value="20000"/>                          
<stat name="function_calls" value="5"/>             
<stat name="context_switches" value="260343"/>                          
 <stat name="M" value="$colum8"/>                 
<stat name="fpu_accesses" value="57880800.0"/>                 
<stat name="mul_accesses" value="57880800.0"/>             
<stat name="cdb_alu_accesses" value="1000000"/>             
<stat name="cdb_mul_accesses" value="0"/>             
<stat name="cdb_fpu_accesses" value="0"/>

The "value" in red will be filled out from the .csv for the corresponding "name"
# 4  
I posted some code in this thread: Data formatting in CSV file to EXCEL, that should directly apply here.

Firstly create a template file like this. Edit it how you see fit - note the HEADER,ROW and FOOTER sections and
the special field tags like this %%FIELD#nn%%:

Code:
--HEADER--
?xml version="1.0" ?> <component id="root" name="root">
--ROW--
<component id="system" name="system">
        <param name="number_of_A" value="%%FIELD#01%%"/>
        <param name="number_of_B" value="%%FIELD#02%%"/>
        <param name="number_of_C" value="%%FIELD#03%%"/>
        <param name="number_of_D" value="%%FIELD#04%%"/> <!-- This number means how many L2 clusters in each cluster there can be multiple banks/ports -->
        <param name="number_of_E" value="%%FIELD#05%%"/> <!-- This number means how many L3 clusters -->
        <param name="number_of_F" value="%%FIELD#06%%"/>
        <param name="homogeneous_G" value="%%FIELD#07%%"/><!--1 means homo -->
        <param name="homogeneous_H" value="1"/>
       <component id="system.core0" name="core0">
             <param name="RAS_size" value="32"/>
            <!-- general stats, defines simulation periods;require total, idle, and busy cycles for senity check  -->
            <!-- please note: if target architecture is X86, then all the instrucions refer to (fused) micro-ops -->
               <stat name="total_instructions" value="3765298576.0"/> <!-- CONX -->
               <stat name="N" value="3700000000.0"/>
               <stat name="fp_instructions" value="64312000.0"/>
            <stat name="branch_instructions" value="0"/>
            <stat name="branch_mispredictions" value="0"/>
               <stat name="load_instructions" value="1129589573.0"/>
               <stat name="store_instructions" value="376529857.6"/>
               <stat name="committed_instructions" value="3765298576.0"/>
               <stat name="committed_int_instructions" value="3700986576.0"/>
               <stat name="committed_fp_instructions" value="64312000.0"/>
            <stat name="pipeline_duty_cycle" value="0.6"/>
            <stat name="L" value="100000"/>
            <stat name="idle_cycles" value="0"/>
            <stat name="busy_cycles"  value="100000"/>
            <stat name="ROB_reads" value="263886"/>
            <stat name="ROB_writes" value="263886"/>

            <stat name="rename_accesses" value="263886"/>
            <stat name="fp_rename_accesses" value="263886"/>

            <stat name="inst_window_reads" value="263886"/>
            <stat name="inst_window_writes" value="263886"/>
            <stat name="inst_window_wakeup_accesses" value="263886"/>
            <stat name="fp_inst_window_reads" value="263886"/>
            <stat name="fp_inst_window_writes" value="263886"/>
            <stat name="fp_inst_window_wakeup_accesses" value="263886"/>
            <!--  RF accesses -->
            <stat name="int_regfile_reads" value="1600000"/>
            <stat name="float_regfile_reads" value="40000"/>
            <stat name="int_regfile_writes" value="800000"/>
            <stat name="float_regfile_writes" value="20000"/>

            <stat name="function_calls" value="5"/>
            <stat name="context_switches" value="260343"/>

            <stat name="M" value="%%FIELD#08%%"/>
            <stat name="fpu_accesses" value="57880800.0"/>
            <stat name="mul_accesses" value="57880800.0"/>
            <stat name="cdb_alu_accesses" value="1000000"/>
            <stat name="cdb_mul_accesses" value="0"/>
            <stat name="cdb_fpu_accesses" value="0"/>
</component id="system" name="system">
--FOOTER--
</component id="root" name="root">


And here is your code:

Code:
awk -F, '
FNR==NR&&/^--/ {section++; next}
FNR==NR{block[section]=(block[section]?block[section]"\n":"") $0;next}
FNR==1{print block[1]}
{ out=block[2]
  for(i=1;i<=NF;i++) gsub(sprintf("%%%%FIELD#%02d%%%%",i),$i,out);
  print out
}   
END{print block[3]}' template sample_Core0.csv


Last edited by Chubler_XL; 03-29-2015 at 11:51 PM..
This User Gave Thanks to Chubler_XL For This Post:
# 5  
Thanks @Chubler_XL

I was trying to do this in python. Started with a sample replacement of just 2 variables instead of all of them. It worked, but, I was hoping to print out each xml for all the rows in the csv file. My code is only printing out 1 xml file with the first row data from the csv. Here is the code:
Code:
 1 import csv
  2 input_file_name = "Core_0.csv"
  3 template_file = "Niagara1.xml"
  4 output_file = "{}_%s.xml" %input_file_name
  5 
  6 with open(template_file, "rb") as temp_file:
  7         template = temp_file.read()
  8 
  9 with open(input_file_name, "rU") as csv_f:
 10         my_reader = csv.DictReader(csv_f)
 11         for row in my_reader:
 12                 with open(output_file.format(row['system.total_cycles'],row['system.busy_cycles']), "wb") as current_out:
 13                         current_out.write(template.format(total_cycle=row["system.total_cycles"],busy_cycle=row["system.busy_cycles"]))

Why is this code not giving me xml for all the lines in the csv, rather gives just 1..:-(
# 6  
The below python code is pretty much equivalent to the awk code from post #4, use the same template file layout as posted above:

Code:
import csv

input_file_name = "sample_Core0.csv"
template_file = "template"
output_file = "{}_%s.xml" % input_file_name

snum = 0
section=[""] * 4
current_out = open(output_file, "w")

with open(template_file, "r") as temp_file:
    for tline in temp_file:
        if (tline.startswith("--")):
           snum += 1
        else:
            section[snum] += tline

current_out.write(section[1])

with open(input_file_name, "rU") as csv_f:
    for row in csv.DictReader(csv_f):
        rowbuf = section[2]
        fnum = 0
        for col in row:
            fnum += 1
            rowbuf = rowbuf.replace("%%%%FIELD#%02d%%%%" % fnum, row[col])
        current_out.write(rowbuf)

current_out.write(section[3])
current_out.close


Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #170
Difficulty: Easy
RedHat became a subsidiary of IBM in 2019.
True or False?

10 More Discussions You Might Find Interesting

1. Programming

Python script to run multiple command and append data in output csv file

Experts, I am writing a script and able to write only small piece of code and not able to collect logic to complete this task. In input file have to look for name like like this (BGL_HSR_901_1AG_A_CR9KTR10) before sh iss neors. Record this (BGL_HSR_901_1AG_A_CR9KTR10) in csv file Now have to... (0 Replies)
Discussion started by: as7951
0 Replies

2. Programming

Python or Shell script to Grep strings from input file and output in csv format

Hi Experts, I am writing a python script to grep string from file and display output in csv file as in attached screenshot https://drive.google.com/file/d/1gfUUdfmQma33tz65NskThYDhkZUGQO0H/view Input file(result_EPFT_config_device) Below is the python script i have prepared as of... (1 Reply)
Discussion started by: as7951
1 Replies

3. Shell Programming and Scripting

Need get data from XML file through shell script..

hi all, here is the sample log file and these errors are repeated in log file.. i need all the repeated time stamp ,severity and message tags needs to print in output file.. through shell script <log-message> <timestamp>2019-03-13T04:52:49.648-05:00</timestamp> <severity>ERROR</severity>... (17 Replies)
Discussion started by: ravi
17 Replies

4. Shell Programming and Scripting

How to insert a CSV within xml element tag using Python?

Hi Team, I have a CSV file which I have to read through and needs to insert the content within an XML file using Python ONLY ( as most of the code base we have in python only). I managed to find the first part, missing how to insert to XML under "specific" tags. cat input.csv... (0 Replies)
Discussion started by: panyam
0 Replies

5. Shell Programming and Scripting

How to pass the parameter in xml file in UNIX shell script?

Hi, I have an XML file like the following... <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ONDEMAND_JOB VERSION="5.1" LOCALE="en_US"> <IMPORT_JOBSET TC_CONNECTION_NAME="default" ENVIRONMENT="PRD" USERNAME="Administrator" PASSWORD="AdminPassword" CALENDAR="Main Monthly Calendar"... (3 Replies)
Discussion started by: Debalina Roy
3 Replies

6. Shell Programming and Scripting

How to pass the parameter in xml file in UNIX shell script?

Hi, I have an XML file like the following... <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ONDEMAND_JOB VERSION="5.1" LOCALE="en_US"> <IMPORT_JOBSET TC_CONNECTION_NAME="default" ENVIRONMENT="PRD" USERNAME="Administrator" PASSWORD="AdminPassword" CALENDAR="Main Monthly Calendar"... (2 Replies)
Discussion started by: Debalina Roy
2 Replies

7. Shell Programming and Scripting

Extract data from XML file and write in CSV file

Hi friend i have input as following XML file <?xml version="1.0"?> <Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.02"> <BkToCstmrDbtCdtNtfctn> <GrpHdr><MsgId>LBP-RDJ-TE000000-130042430010001001</MsgId><CreDtTm>2013-01-04T03:21:30</CreDtTm></GrpHdr>... (3 Replies)
Discussion started by: mohan sharma
3 Replies

8. Shell Programming and Scripting

Convert XML to Data File in Shell Script

Hi All, I will be getting a huge XML file with a lot of records in it. I need to convert it into multiple data files. SAMPLE XML FILE <ABSProductCatalog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> - <ProductSalesHierachy> - <Portfolios> - <Portfolio productCode="P1"> ... (8 Replies)
Discussion started by: ragha81
8 Replies

9. Shell Programming and Scripting

Extract data from an XML file & write into a CSV file

Hi All, I am having an XML tag like: <detail sim_ser_no_1="898407109001000090" imsi_1="452070001000090"> <security>ADM1=????</security> <security>PIN1=????</security> <security>PIN2=????</security> ... (2 Replies)
Discussion started by: ss_ss
2 Replies

10. Shell Programming and Scripting

Parse XML file into CSV with shell?

Hi, It's been a few years since college when I did stuff like this all the time. Can someone help me figure out how to best tackle this problem? I need to parse a file full of entries that look like this: <eq action="A" sectyType="0" symbol="PGR" exch="CA" curr="VEF" sess="NORM"... (7 Replies)
Discussion started by: Pcushing
7 Replies

Featured Tech Videos