0

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

obros269
  • 1
  • 3
  • 1
    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 Jul 23 '21 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 Jul 23 '21 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 Jul 23 '21 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 Jul 23 '21 at 18:57
  • Is the image you added what you get when you open the CSV file directly in Excel? – norie Jul 23 '21 at 19:01
  • Tim, that is what I am trying to do. The only reason for this code is to simply convert a file from csv to xlsx so i can work with that xlsx later. – obros269 Jul 23 '21 at 19:02
  • Norie, that is correct – obros269 Jul 23 '21 at 19:02
  • @obros269 If that is the case do what Tim has suggested - save the CSV file as an XLSX file after you've opened it in Excel. – norie Jul 23 '21 at 19:03
  • The reason that some of the strings aren’t converted to numbers is that they contain commas. Remove the commas after you read the CSV and before you write to the xlsx file and you should get the output you expect. – jmcnamara Jul 23 '21 at 19:06
  • Okay, so it sounds like removing the commas after reading the csv is the best move? Alternatively, i can manually open the csv and save it as xlsx, but i have a lot of files to go through. That is why i was trying to automate the changing of file type. Thanks everyone! – obros269 Jul 23 '21 at 19:19
  • In `pd.read_csv` pass `thousands=','`. – ifly6 Jul 23 '21 at 19:35
  • ifly6, I have tried this in the past and just tried it again and the resulting file is the same as the one I’ve posted on my original post – obros269 Jul 23 '21 at 19:48

1 Answers1

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)
norie
  • 9,609
  • 2
  • 11
  • 18