0

The conversion of xml to csv file, this is done by some code and the specifications that I have added.

As as result I get a csv file, once I open it I see some weird numbers that look something like this

1,25151E+21

Is there any way to eliminate this and show the whole numbers. The code itself that parses xml to csv is working fine so I’m assuming it is an excel thing.

I don’t want to go and do something manually every time I am generating a new csv file

Additional The entire code can be found HERE and I have only long numbers in Quality 

for qu in sn.findall('.//Qualify'):
                repeated_values['qualify'] = qu.text
  • 1
    When you say "some code and the specifications that I have added", you mean this is VBA code in an Excel spreadsheet and a CSV exported from Excel? Or is it Python code, and the generated CSV has all 22 digits but when you load that into Excel you see the number in 'E' format? And you don't know how to get Excel to show all the digits (a number formatting option?) - or you do know, but don't want to have to do it manually for every CSV? (i.e. you need to export something other than CSV) – Rup Jan 15 '20 at 16:05
  • If you replace the `comma` with a `period`, you can pass it to `int()` and Python will handle it just fine. Something like this `int(1.25151E+21)` will result in `1251510000000000065536`. – accdias Jan 15 '20 at 16:07
  • How are you opening the CSV? Are you seeing these "weird numbers" in Excel or in a text editor? – Mark Loeser Jan 15 '20 at 16:07
  • Please provide a snippet of the first several lines of the actual csv file that causes problems. – piRSquared Jan 15 '20 at 16:09
  • Does this answer your question? [Converting number in scientific notation to int](https://stackoverflow.com/questions/32861429/converting-number-in-scientific-notation-to-int) – accdias Jan 15 '20 at 16:10
  • @piRSquared, I have added the link to where I adapted the code and the part of it that is causing the problem (because those elements in the xml have very long numbers) –  Jan 16 '20 at 08:19

3 Answers3

1

CSV doesn't pass any cell formatting rules to Excel. Hence if you open a CSV that has very large numbers in it, the default cell formatting will likely be Scientific. You can try changing the cell formatting to Number and if that changes the view to the entire number like you want, consider using the Xlsxwriter to apply cell formatting to the document while writing to Xlsx instead of CSV.

Chris
  • 15,819
  • 3
  • 24
  • 37
  • I have added more to the question. I really don't want to work with xlsx files, I was told to stick with csv files –  Jan 16 '20 at 08:21
0

I often end up running a lambda on dataframes with this issue when I bring in csv, fwf, etc, for ETL and back out to XLSX. In my case they are all account numbers, so it's pretty bad when Excel helpfully overrides it to scientific notation.

If you don't mind the long number being a string, you can do this:

# First I force it to be an int column as I import everything as objects for unrelated reasons
df.thatlongnumber = df.thatlongnumber.astype(np.int64)
# Then I convert that to a string
df.thatlongnumber.apply(lambda x: '{:d}'.format(x))

Let me know if this is useful at all.

CSure
  • 36
  • 4
  • I have added the location to where I got the code, Would mean a lot if you could help me implement your approach there. thank you –  Jan 16 '20 at 08:24
0

Scientific notation is a pain, what I've used before to handle situations like this is to cast it into a float and then use a format specifier, something like this should work:

a = "1,25151E+21"

print(f"{float(a.replace(',', '.')):.0f}")

>>> 1251510000000000065536
marcos
  • 4,473
  • 1
  • 10
  • 24
  • I have added the link to the code and the snippet that is causing the problem. would appreicate if you can help me implement your approach there –  Jan 16 '20 at 08:22
  • Can you please have a look at the code and help me implement your approach to the code [Code](https://stackoverflow.com/a/59616238/11916876) –  Jan 18 '20 at 14:13