Tukukkk

I have a dump from our application server which contains XML of multiple strings. I am interested in the userID, which is embedded in the XML tags and in the format of (lasfir1) as in the XML examples below:

<row>

  <string></string>

  <integer>2177</integer>

  <string>assignee =lasfir1 </string>

  <string>Firstname Lastname</string>

  <integer>10</integer>

  <string xsi:nil="true"/>

  <integer>450</integer>

</row>



<row>

  <string>#ffd600</string>

  <integer>2199</integer>

  <integer>23</integer>

  <integer>474</integer>

  <string>assignee</string>

  <string>lasfir1</string>

</row>



<row>

  <integer>1536</integer>

  <string>lasfir1</string>

  <integer>235</integer>

  <string>USER</string>

</row>



<row>

  <string>#ffd610</string>

  <integer>2200</integer>

  <integer>25</integer>

  <integer>464</integer>

  <string>assignee</string>

  <string>lisfar1</string>

</row>

The requirement is to convert the string "lasfir1" only into its equivalent Email ID, which are available in another CSV (text) file which has key->value pairing of the userID and Email ID:

FirstName.LastName@abc.com,lasfir1

FarstName.ListName@abc.com,lisfar1

LastName.FirstName@abc.com,firlas1

The XML may not always be the same, but the string will be the one to search for, not the pattern of what is ahead or behind it.

Is there some simple way to read the key->value pair (in the CSV file), check if the key (userID) exists in the XML file and then replace it with the 'value' (Email ID)

This is required for a set of 300+ userID and Email ID combinations, all of which might not be in the XML.

Check out this Perl one liner solution:

$ cat gagneet.csv

FirstName.LastName@abc.com,lasfir1

FarstName.ListName@abc.com,lisfar1

LastName.FirstName@abc.com,firlas1



$ cat gagneet.xml

<row>

  <string></string>

  <integer>2177</integer>

  <string>assignee =lasfir1 </string>

  <string>Firstname Lastname</string>

  <integer>10</integer>

  <string xsi:nil="true"/>

  <integer>450</integer>

</row>



. . . . 

. . . . 



$ perl -ne 'BEGIN { %kv=map{chomp;(split(",",$_))[1,0] } qx(cat gagneet.csv) ; $content=qx(cat gagneet.xml);while($content=~/(<row>)(.*?)(</row>)/smg) { $xml=$2;forea

ch $y (keys %kv) { $xml=~s/${y}/$kv{$y}/gm; } print "$1$xml$3n"; } exit } '

<row>

  <string></string>

  <integer>2177</integer>

  <string>assignee =FirstName.LastName@abc.com </string>

  <string>Firstname Lastname</string>

  <integer>10</integer>

  <string xsi:nil="true"/>

  <integer>450</integer>

</row>

<row>

  <string>#ffd600</string>

  <integer>2199</integer>

  <integer>23</integer>

  <integer>474</integer>

  <string>assignee</string>

  <string>FirstName.LastName@abc.com</string>

</row>

<row>

  <integer>1536</integer>

  <string>FirstName.LastName@abc.com</string>

  <integer>235</integer>

  <string>USER</string>

</row>

<row>

  <string>#ffd610</string>

  <integer>2200</integer>

  <integer>25</integer>

  <integer>464</integer>

  <string>assignee</string>

  <string>FarstName.ListName@abc.com</string>

</row>

If you want edit only between tags, then

$ perl -ne 'BEGIN { %kv=map{chomp;(split(",",$_))[1,0] } qx(cat gagneet.csv) ; $content=qx(cat gagneet.xml);while($content=~/(<row>)(.*?)(</row>)/smg) { $xml=$2;forea

ch $y (keys %kv) { $xml=~s/<string>${y}</string>/<string>$kv{$y}</string>/gm; } print "$1$xml$3n"; } exit } '

Created a script using Python3, which takes in the input as the CSV and the XML file and outputs an XML file with the changes. The command is:

python xml_converter.py –csvfile file.csv –xmlfile file.xml –outfile output_file.xml

Not totally optimized as I would want it to be and running on a single thread, and assumption is that the files are utf-8 encoded.

usage: Replace username to user email of a given xml file

       [-h] --csvfile CSVFILE --xmlfile XMLFILE --outfile OUTFILE



optional arguments:

  -h, --help         show this help message and exit

  --csvfile CSVFILE  csv file that provide user name and email pair

  --xmlfile XMLFILE  xml file that to be searched and replaced

  --outfile OUTFILE  output file name

The basic script is:

class XMLConvert:

    def __init__(self, csv, xml, out):

        self._csv = csv

        self._xml = xml

        self._out = out



        self._kv_dict = self.prepare_kv_dict()



    def prepare_kv_dict(self):

        with open(self._csv, newline='', encoding='utf-8') as f:

            reader = csv.reader(f)

            result = dict()

            for row in reader:

                result[row[1]] = row[2]

        return result



    def convert(self):

        with open(self._xml, 'r', encoding='utf-8') as f:

            for line in f:

                _line = self.convert_line(line)

                yield _line



    def convert_line(self, line):

        # self._kv_dict = {'lasfir1': 'First.Name@abc.com'}

        for k, v in self._kv_dict.items():

            if k.lower() in line:

                # print(line)

                return re.sub(r'{}'.format(k), v, line)

        return line



    def start(self):

        with open(self._out, 'w', encoding='utf-8') as f:

            for line in self.convert():

                f.write(line)





if __name__ == '__main__':

    csv_file, xml_file, out_file = parse_args()

    converter = XMLConvert(csv_file, xml_file, out_file)

    converter.start()

I am trying to add threads and modify it accordingly to optimize the running of it. If anyone has a better way then please do inform.