CODEWITHSUNDEEP
pythonopenpyxl
scopa
scopa

Reputation: 523

Convert one column to numbers

I have a program I am building which asks for a csv file and saves it in excel. I have one column that is currency - $10.00 - I can convert other cells to number with my code but I assume since there is a '$' before the numbers it does not convert. How can I fix this for just this one column - Thanks

def open_csv():
from tkinter import Tk
from tkinter.filedialog import askopenfilename
import csv
from openpyxl import Workbook
from openpyxl.cell import get_column_letter

Tk().withdraw()
filename = askopenfilename()

open_rawdata = open(filename, 'r')

csv.register_dialect('commas', delimiter=',')

reader = csv.reader(open_rawdata, dialect='commas')

wb = Workbook()
dest_xls = r'C:\Users\sparkis\Desktop\WeeklyReport.xlsx'

ws = wb.worksheets[0]
ws.title = "TEST TITLE"

for row_index, row in enumerate(reader):
    for column_index, cell in enumerate(row):
        column_letter = get_column_letter((column_index + 1))
        s = cell
        try:
            s = float(s)
        except ValueError:
            pass

        ws.cell('%s%s'%(column_letter, (row_index + 1))).value = s



wb.save(filename = dest_xls)

Upvotes: 1

Views: 5093

Answers (1)

Charlie Clark
Charlie Clark

Reputation: 19537

In a CSV file all values are strings. openpyxl workbooks have an option that will let them do type inference but you are better managing the conversion yourself and, in this case, adding an appropriate number format.

Assuming it is the fourth column of your input that needs converting to a number. Something along the lines of this should work.

for idx, row in enumerate(reader, 1):
    row[3] = row[3][1:] # strip $
    ws.append(row)
    ws.cell(idx, 4).number_format = "$"

Upvotes: 1

Related Questions