Python: Issue converting csv to xlsx. Numbers stored as text

Question

Similar to this post: Conversion text to number in python

I am having issues trying to convert a csv file to xlsx while maintaining cell format as numbers.

For the most part, my code works. It converts SOME text cells back to numbers, but any numbers with commas (like any number >= 1,000) remain as cells formatted as text.

Attached is my code and the result of the completed task

import pandas as pd

# Rename files
file1 = pd.read_csv(path0 + files[0])
Nfile1 = pd.ExcelWriter(path0 + 'Z Out Report.xlsx', engine = 'xlsxwriter', options = {'strings_to_numbers': True})
file1.to_excel(Nfile1, index = False)
Nfile1.save()

xlsx file after conversion from csv

As you can see, some numbers are still stored as text with the green arrow error.

I am trying to use this new xlsx file to read and write with, but I need those cells as numbers and not text.

Thanks for any help the community can provide!

Edit 1: Adding the csv file Csv file

Why would you get pandas involved in this at all? It makes no sense. Excel can read CSV files directly, and it is perfectly happy to read numbers with commas. — Tim Roberts
– Tim Roberts, Commented Jul 23, 2021 at 18:35
Tim, the reason for Pandas was converting csv to xlsx. The reason for doing it out xlsx and not csv was because I was having a lot of issues performing excel formulas in the csv and then writing the final value from the formula into another xlsx workbook. I would also like to add that I am very new to programming. When i save this file manually and change the file type to xlsx, everything works perfectly. — obros269
– obros269, Commented Jul 23, 2021 at 18:39
How exactly is the CSV you want to convert structured? From the posted image it doesn't appear to be structured in any sort of 'table' format that would be suitable for use with Pandas. — norie
– norie, Commented Jul 23, 2021 at 18:55
No, you can't save formulas in CSV. So, start from the raw data, load that CSV into Excel, then save it as an XLSX and keep it there. — Tim Roberts
– Tim Roberts, Commented Jul 23, 2021 at 18:57
Is the image you added what you get when you open the CSV file directly in Excel? — norie
– norie, Commented Jul 23, 2021 at 19:01

norie · Accepted Answer · 2021-07-23 20:03:17Z

1

If you really need to do this with pandas you could use this to replace all the commas in the dataframe file1.

file1 = file1.replace(',','', regex=True)

answered Jul 23, 2021 at 20:03

norie

9,9372 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python: Issue converting csv to xlsx. Numbers stored as text

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related