Python – openpyxl python – writing csv to excel gives ‘number formatted as text’

csvexcelopenpyxlpythonpython-2.7

I have written a code to import a .csv file (containing numbers) into an excel file through openpyxl. It works, however, all the cells have written the numbers to the excel file as text. I then have to manually correct the error in excel: "Numbers formatted as text (displaying little green triangles in the corner of the cell).

Is there a way to prevent this from happening? It occurs with any csv file, even if I make it with just numbers. Thanks

#!python2
# Add csv file to an xlsx

import os, csv, sys, openpyxl
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.cell import get_column_letter


#Open an xlsx for reading
wb = load_workbook('Test.xlsx')
ws = wb.get_sheet_by_name("RUN")

dest_filename = "Test_NEW.xlsx"
csv_filename = "csvfile.csv"

#Copy in csv
f = open(csv_filename)
reader = csv.reader(f)
for row_index, row in enumerate(reader):
    for column_index, cell in enumerate(row):
        column_letter = get_column_letter((column_index + 1))
        ws.cell('%s%s'%(column_letter, (row_index + 1))).value = cell


wb.save(filename = dest_filename)

print "new Cashflow created"

*****UPDATE***

Thanks, that helps. My problem was that my csv file had a mixture of text and numbers without any defining quotes. So I implemented the below to change it from a string to float as long as there isn't an error.

#Copy in csv
f = open(csv_filename,'rb')
reader = csv.reader(f)
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

Best Solution

You need to convert the value from the CSV file to what you need. All values in CSV files are strings. ws.cell('%s%s'%(column_letter, (row_index + 1))).value = int(cell) ought to do it.

BTW. you might want to look at the ws.append() method.