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)
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.