Varvaroi Gabriel Varvaroi Gabriel - 1 month ago 6
Python Question

CSV to Excel conversion using Python

I wrote a piece of code that parse a .csv and save the data in excel format.
The problem is that all my data in excel appear as "number stored as text" and i need the data starting with column 3 to be converted as number type so i can represent it using a chart.
My table is looking something like this:

C1 C2 C3
R1 BlaBla 36552.00233102 ...
R2 App1 3484.000000000 ...
R3 App2 3399.000000000 ...
.....................................


and this is the code:

f = open("csv_file")
wb = openpyxl.Workbook()
ws = wb.active
reader = csv.reader(f, delimiter=',')
for i in reader:
ws.append(i)

f.close()
wb.save("excel_file")

Answer

Does your CSV file contain numeric elements that are in quotation marks? If so, that would result in what you're seeing. For example, consider this:

import openpyxl
wb = openpyxl.Workbook()
ws = wb.active
for i in [['100', 100]]:
    ws.append(i)    
wb.save("excel_file.xlsx")

The str value is written as a text element in Excel while the int value is written as a number. If your CSV file contains

"100", 100

csv would interpret that as a string and a number.

You could convert everything in column three onwards from your csv file by adding:

i[2:] = [float(x) for x in i[2:]]

to the line right before you append i to your workbook.