How to correctly parse as text numbers separated by mixed commas and dots in excel file using Python?
<p>I'm importing data coming from excel files that come from another office.
In one of the columns, for each cell, I have lists of numbers used as tags. These were manually inserted, by different people and (my guess) using computers with different thousands settings, so the result is very heterogeneous.</p>

<p>As an example I have:</p>

<pre><code>tags= ['205', '306.3', '3,206,302','7.205.206']
</code></pre>

<p>If this was a CSV file (I tried converting one single file to check), using</p>

<pre><code>pd.read_csv(my_file,sep=';')
</code></pre>

<p>would give me exactly the above mentioned list.</p>

<p>Unfortunately as said, we're talking about excel files (plural) and I have to deal with it, and using</p>

<pre><code>pd.read_excel(my_file,sheetname=my_sheet,encoding='utf-16',converters{'my_column':str})
</code></pre>

<p>what I get instead is:</p>

<pre><code>tags= ['205', '306.3', '3,206,302','7205206']
</code></pre>

<p>As you see, whenever the number can be expressed <strong>logically</strong> in thousands (so, not the second number in my list) the dot is recognised as a thousands separator and I get a single number, instead of three.</p>

<p>I tried reading documentation, and searching on stackoverflow and google, but the keywords to describe this problem are too vague and I didn't find a viable solution, yet.</p>

<p>How can I get the right list using excel files?</p>

<p>Thanks.</p>