Python - Iterate through CSV rows and replace in XML

Question

I have a CSV with values that I would like to replace inside an XML template. Generating XMLs for each row that have filenames based on data in the same row. These are basically just copies of the template with a find and replace. So far I have gotten the filenames to work correctly but the XML file does not replace its data according to the row instead it only replaces data from row 3, column 10. I'm looking to have it iterate through a range of rows and create new files for each. I'm stumped as to what is going wrong here.

CSV Snippet:

COLUMN_K, COLUMN_L
K02496.ai, Test
K02550.ai, Test
K02686.ai, Test
K02687.ai, Test

Existing XML Template Snippet

  <gmd:resourceFormat>
    <gmd:MD_Format>
      <gmd:name>
        <gco:CharacterString>COLUMN_K</gco:CharacterString>
      </gmd:name>

Python Code

import csv

exampleFile = open('U:\PROJECTS\Technical Graphics\metadata.csv')
exampleReader = csv.reader(exampleFile)
exampleData = list(exampleReader) #CSV as list

with open('U:\PROJECTS\Technical Graphics\COLUMN_K_edited.xml') as inputfile: #template XML
    xml = inputfile.read()

with open('U:\PROJECTS\Technical Graphics\metadata.csv') as csvfile:
    for row in reader(csvfile, delimiter=';'):
        for i in range(5): #range of 5 rows
           xml = xml.replace('COLUMN_K', exampleData[i+3][10]) 
#Only taking value from row 3, COLUMN_K- Need values from row 3 on
           xml = xml.replace('COLUMN_L', exampleData[i+3][11]) 
#Only taking value from row 3, COLUMN_L- Need values from row 3 on    
            with open('U:\PROJECTS\Technical Graphics\XXX' + str((exampleData[i+3][10])) + ".xml", 'w') as outputfile: 
 #Correctly outputs multiple filenames based on COLUMN_K value
                outputfile.write(xml) #writes multiple XMLs

Update with a [Minimal, complete, verifiable example](http://stackoverflow.com/help/mcve). As it is I can't make sense of what the input and output are supposed to be. For example, the reader is opened with delimiter of semicolon, but there is no semicolon in the examples. There is no `COLUMN_E` to replace. — Mark Tolonen, Jul 26 '16 at 20:31
Sorry about that. I'll follow that protocol from now on, edited the example now. — MapZombie, Jul 26 '16 at 21:20

score 0 · Answer 1 · edited May 23 '17 at 12:08

0

Check these examples , they are working perfectly

Python create XML from Csv within a loop

http://code.activestate.com/recipes/577423-convert-csv-to-xml/

edited May 23 '17 at 12:08

Community

1
1

answered Jul 26 '16 at 20:07

Shijo

9,313
3
19
31

Thanks. I have seen this as well and it's great code. But I'm not looking to replace tags at all or build out the entire XML schema. My code literally just needs to find and replace a few values and iterate through each row in the csv. – MapZombie Jul 26 '16 at 20:13

score 0 · Answer 2 · answered Jul 26 '16 at 20:33

0

You have mentioned 'COLUMN_F' in the xml tag and you are trying to replace 'COLUMN_E' in the code

xml = xml.replace('COLUMN_E', exampleData[i+3][10])

answered Jul 26 '16 at 20:33

Shijo

9,313
3
19
31

Just using those as examples. Will be building codes for each of the "COLUMN_X". I'll fix it now so it reads better. – MapZombie Jul 26 '16 at 20:50
Basically each of the COLUMN_X in the CSV cooresponds to a "COLUMN_X" inside the XML template. Those will be replaced with the CSV values and generate new XML files. – MapZombie Jul 26 '16 at 20:54

score 0 · Answer 3 · answered Jul 27 '16 at 16:01

I was able to resolve this simply by moving the loop to the very top of the code. The problem was it was replacing the text but then could not find the "COLUMN_*" because it had been replaced with a value. Moving the loop to the top resolved this.

import csv

    for i in range(5):  #loops through 5 rows
        exampleFile = open('U:\PROJECTS\Technical Graphics\metadata.csv')  #CSV file
        exampleReader = csv.reader(exampleFile)
        exampleData = list(exampleReader) #turns CSV into list

        with open('U:\PROJECTS\Technical Graphics\COLUMN_K_edited.xml') as inputfile:
            xml = inputfile.read() #XML template file

        with open('U:\PROJECTS\Technical Graphics\metadata.csv') as csvfile:

            for row in reader(csvfile, delimiter=';'): #defines CSV delimiter

                    with open('U:\PROJECTS\Technical Graphics\XXX' + str((exampleData[i+3][10])) + ".xml", 'w') as outputfile:
                    #Filename of XMLs based on column 10 data        
                        xml = xml.replace('COLUMN_I', str((exampleData[i+3][8])))
                        xml = xml.replace('COLUMN_K', str((exampleData[i+3][10])))


                        outputfile.write(xml) #writes 5 XML files

Python - Iterate through CSV rows and replace in XML

3 Answers3