JPC JPC - 1 month ago 18
Python Question

How to writing a python code to combine multiple csv to excel , without having number read as text ..in excel?

I'm using this csv reader to read all my csv file then combine into xls workbook. But after open excel all number from csv is now read as text.

In each csv it has both letter and number. Is there a way I can compress all number value from csv to let excel know it's number not text ?

import glob, csv, xlwt, os

wb = xlwt.Workbook()
for filename in glob.glob("d:/test/*.csv"):
(f_path, f_name) = os.path.split(filename)
(f_short_name, f_extension) = os.path.splitext(f_name)
ws = wb.add_sheet(f_short_name)
spamReader = csv.reader(open(filename, "rt"))

for rowx, row in enumerate(spamReader):
for colx, value in enumerate(row):
ws.write(rowx, colx, value)
wb.save("d:/test/compiled.xls")

Answer

I would use the pandas library, which would also give you some experience with the overall Python data science stack. Pandas will be smart enough to interpret the correct Excel data types.

import glob, os
import pandas as pd

from pandas import DataFrame, ExcelWriter

writer = ExcelWriter("d:/test/compiled.xlsx")

for filename in glob.glob("d:/test/*.csv")
    df_csv = pd.read_csv(filename)

    (_, f_name) = os.path.split(filename)
    (f_short_name, _) = os.path.splitext(f_name)

    df_csv.to_excel(writer, f_shortname, index=False)

 writer.save()

You open a spreadsheet for writing, read each CSV as pandas DataFrame, and then write it to the sheet on a separate tab. The index=False piece is there so that Python doesn't write the default DataFrame index to the sheet, which would give you an extra unwanted column.