Simple 1-to-1 CSV to XLSX file conversion without enumerating/looping through the rows:
import pyexcel
sheet = pyexcel.get_sheet(file_name="myFile.csv", delimiter=",")
sheet.save_as("myFile.xlsx")
Notes:
Adding an answer that exclusively uses the pandas library to read in a .csv file and save as a .xlsx file. This example makes use of pandas.read_csv
(Link to docs) and pandas.dataframe.to_excel
(Link to docs).
The fully reproducible example uses numpy to generate random numbers only, and this can be removed if you would like to use your own .csv file.
import pandas as pd
import numpy as np
# Creating a dataframe and saving as test.csv in current directory
df = pd.DataFrame(np.random.randn(100000, 3), columns=list('ABC'))
df.to_csv('test.csv', index = False)
# Reading in test.csv and saving as test.xlsx
df_new = pd.read_csv('test.csv')
writer = pd.ExcelWriter('test.xlsx')
df_new.to_excel(writer, index = False)
writer.save()
Simple two line code solution using pandas
import pandas as pd
read_file = pd.read_csv ('File name.csv')
read_file.to_excel ('File name.xlsx', index = None, header=True)
There is a simple way
import os
import csv
import sys
from openpyxl import Workbook
reload(sys)
sys.setdefaultencoding('utf8')
if __name__ == '__main__':
workbook = Workbook()
worksheet = workbook.active
with open('input.csv', 'r') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
for idx, val in enumerate(col.split(',')):
cell = worksheet.cell(row=r+1, column=c+1)
cell.value = val
workbook.save('output.xlsx')
How I do it with openpyxl lib:
import csv
from openpyxl import Workbook
def convert_csv_to_xlsx(self):
wb = Workbook()
sheet = wb.active
CSV_SEPARATOR = "#"
with open("my_file.csv") as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
for idx, val in enumerate(col.split(CSV_SEPARATOR)):
cell = sheet.cell(row=r+1, column=idx+1)
cell.value = val
wb.save("my_file.xlsx")
First install openpyxl:
pip install openpyxl
Then:
from openpyxl import Workbook
import csv
wb = Workbook()
ws = wb.active
with open('test.csv', 'r') as f:
for row in csv.reader(f):
ws.append(row)
wb.save('name.xlsx')
With my library pyexcel
,
$ pip install pyexcel pyexcel-xlsx
you can do it in one command line:
from pyexcel.cookbook import merge_all_to_a_book
# import pyexcel.ext.xlsx # no longer required if you use pyexcel >= 0.2.2
import glob
merge_all_to_a_book(glob.glob("your_csv_directory/*.csv"), "output.xlsx")
Each csv will have its own sheet and the name will be their file name.
Source: Stackoverflow.com