Python loop to create csv from excel files in directory

Question

I am looping through a directory to update the external links in some workbooks and then its the goal to make csv's out of these. I have it functioning but instead of getting the name and path with .csv file suffix it just adds .csv to .xlsx

import os
import csv
import xlrd
import win32com.client

directory = (r'D:\\Dropbox (DBM Vircon)\\XX_Share Internal\\190826 - CD Refresh and Output CSVs')
suffix = '.csv'

for file in os.listdir(directory):
    filename = os.fsdecode(file)
    if filename.endswith(".xlsx"):

        wb = os.path.join(directory, filename)
        print (wb)
        clean = os.path.splitext(wb)
        xlapp = win32com.client.DispatchEx("Excel.Application")
        wb1 = xlapp.workbooks.open(wb)
        xlapp.Visible = False
        wb1.RefreshAll()
        print("refreshed")
        wb1.Save()
        xlapp.Quit()
        print("finished")


        wb1 = xlrd.open_workbook(wb)
        sh = wb1.sheet_by_index(0)
#Below just adds .csv to filename instead of getting name, then adding it 
        mycsv = os.path.join(directory, filename + suffix)
        mynewcsv = open(mycsv, 'w', newline='')
        wr = csv.writer(mynewcsv, quoting=csv.QUOTE_ALL)
        for rownum in range(sh.nrows):
            wr.writerow(sh.row_values(rownum))

        #mycsv.close()

        continue
    else:
        continue

file_obj = open("CSVUpdatecomplete.csv", 'w')

wwii · Accepted Answer · 2019-08-28 02:59:39Z

1

Separate the name from the extension with os.path.splitext:

...
name, ext = os.path.splitext(filename)
mycsv = os.path.join(directory, name + suffix)
...

edited Aug 28, 2019 at 2:59

answered Aug 28, 2019 at 2:53

wwii

23.9k7 gold badges42 silver badges80 bronze badges

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

Comments

whydoubt · Accepted Answer · 2019-08-28 02:51:16Z

0

Since filename includes .xlsx at the end, you will want to replace that with .csv rather than append.

For instance, use filename.replace('.xlsx', '.csv') instead of filename + suffix

answered Aug 28, 2019 at 2:51

whydoubt

7991 gold badge6 silver badges14 bronze badges

Comments

Ramkrushna26 · Accepted Answer · 2019-08-28 03:26:40Z

0

You can just use split function on filename:

mycsv = os.path.join(directory, filename.split(".")[0] + suffix)

answered Aug 28, 2019 at 3:26

Ramkrushna26

1251 silver badge7 bronze badges

Collectives™ on Stack Overflow

Python loop to create csv from excel files in directory

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related