Beautifulsoup4, parsing Tableau XML file, and writing to file

user_11606388 0
Viewed 296

I'm having an issue where I'm using beautifulsoup to parse the xml generated from a Tableau workbook and when I write the results to file it doesn't behave as expected. Chose bs4 and it's standard XML parser, because I find it easiest for my brain to comprehend and I don't need the speed of the lxml parser/package.

Background: I have a calculated field in my Tableau workbook that will programmatically change during publish depending on the server and site location that template workbook will go to. I've already gone through and built some functions and scripted out everything I need to get the data to do this, but when my script writes the xml to file it adds some encodings for ampersand. This results in the file being valid and able to be opened in Tableau, but the field is considered invalid, despite looking like it is valid. I'm thinking the XML is some how getting malformed somewhere in my process.

Code so far for where I think the issue is occuring:

import bs4 as bs

twb = open(Script_config['local_file_location'], 'r')
bs_content = bs(twb, 'xml')

# formula_final below comes from another script that handles getting the data I need to programmatically generate the formula I need.
# Here is what I use to generate the bulk of the formula for Tableau
# 'When '[{}]' then {} '.format(rows['Column_Name'], rows['Formatted_ColumnName']))
# Does some other stuff and slaps together the formula I need as a string that can be written into my XML
# Verified that my result is coming over correctly and only changes once I do the replacement here and/or the writing of the file. 

for calculation in bs_content.find_all('column', {'caption': 'Group By', 'datatype':'string', 'name':'[Calculation_12345678910]'}):
     calculation.find('calculation')['formula'] = formula_final

with open('test.twb', 'w') as file:
  file.write(str(bs_content))

Sample XML:

<?xml version="1.0" encoding="utf-8"?>
<workbook source-build="2021.1.4 (20211.21.0712.0907)" source-platform="win" version="18.1" xml:base="https://localhost" xmlns:user="http://www.tableausoftware.com/xml/user">
...

<column caption="Group By" datatype="string" name="[Calculation_12345678910]" role="dimension" type="nominal">

<calculation class="tableau" formula="Case [Parameters].[Location External ID Parameter] When &amp;apos;[Territory]&amp;apos; then [Territory] End"/>
</column>

Problem: In the sample XML, Tableau is expecting the XML to be formatted without the &amp; in front of the apos;. It should just be reading as &apos;.

What I've tried: Thinking that I could just escape the & character I put the necessary slashes in place to escape it before the apos; portion, but to no avail I can't figure out how to get my XML to be formed so that it doesn't always put the ampersand code as part of the other special characters in my XML.

Any help would be much appreciated!

tableau-api beautifulsoup xml python
1 Answers

Good problem description.

Your problem is known as 'double escaping'. Your program is reading data which has already been serialized by an XML processor. That's why it contains &apos;[{}]&apos; and not '[{}]'

I think your program reads that XML value from a file as a simple string and assigns it to the value of a tag. But when BeautifulSoup's XML processor encounters the & in the tag value it must replace it with &amp;. So you end up with &amp;apos;' instead of &apos; in the XML output.

The quick and dirty solution is to write some code to replace all XML entities with the equivalent text. A better solution would be to read the XML data using an XML parser - that way, your program will receive the intended string value automatically.

user_55519380
Related