CODEWITHSUNDEEP
pythonpython-3.xexport-to-excelglobxlsxwriter
Nantourakis
Nantourakis

Reputation: 147

How to convert multiple CSV files to XLXS in Python?

I'm trying to convert all CSV files within a directory into one XLXS file with each csv file becoming a separate worksheet.

The code below works except when I provide the input path in this Line "for filename in glob.glob(InputPath + "*.csv"):" I get this error - InvalidWorksheetName: Invalid Excel character '[]:*?/' in sheetname

Does anyone have a suggestion how I can get around this? Full code is below - Thanks!

import xlsxwriter
import glob
import csv

InputPath = r"C:\\Users\\.spyder-py3\\"

workbook = xlsxwriter.Workbook(r"C:\\Users\\.spyder-py3\\Output\\compiled.xlsx") 
for filename in glob.glob(InputPath + "\*.csv"):
ws = workbook.add_worksheet(str(filename.split('.')[0]))
spamReader = csv.reader(open(filename, 'r'), delimiter=',',quotechar='"')
row_count = 0
print(filename)
for row in spamReader:
    for col in range(len(row)):
        ws.write(row_count,col,row[col])
    row_count +=1

workbook.close()

Upvotes: 0

Views: 811

Answers (1)

Vovin
Vovin

Reputation: 760

Try this:

import xlsxwriter
import glob
import csv

InputPath = r"C:\Users\.spyder-py3"

workbook = xlsxwriter.Workbook(r"C:\Users\.spyder-py3\Output\compiled.xlsx") 
for filename in glob.glob(InputPath + r"\*.csv"):
    ws = workbook.add_worksheet(str(filename.split('.')[0]))
    spamReader = csv.reader(open(filename, 'r'), delimiter=',',quotechar='"')
    row_count = 0
    print(filename)
    for row in spamReader:
        for col in range(len(row)):
            ws.write(row_count,col,row[col])
        row_count +=1

workbook.close()

Upvotes: 1

Related Questions