How to convert multiple CSV files to XLXS in Python?

Question

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

Vovin · Accepted Answer · 2022-03-14 14:43:08Z

1

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

answered Mar 14, 2022 at 14:43

Vovin

7827 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nantourakis Over a year ago

Thanks for your help Vovin, I was able achieve what I was looking for by adding in this code: InputPath = "C:\\Users\\Desktop\\" os.chdir(InputPath)

Vovin Over a year ago

@Nantourakis It is recommended to use either os (and the like packages) or r-strings. Otherwise, double slashes really dizzy.

Collectives™ on Stack Overflow

How to convert multiple CSV files to XLXS in Python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related