0

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()
Nantourakis
  • 107
  • 1
  • 8

1 Answers1

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()
Vovin
  • 720
  • 4
  • 16
  • 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) – Nantourakis Mar 14 '22 at 19:32
  • @Nantourakis It is recommended to use either `os` (and the like packages) or r-strings. Otherwise, double slashes really dizzy. – Vovin Mar 14 '22 at 20:45