Convert Excel to CSV using Python

Question

I would like to name new CSV files similar to their corresponding xlsx files

import pandas as pd

for filename in my_path.glob('*.xlsx'):

    read_file = pd.read_excel (str(filename)', sheet_name='My Excel sheet name')
    read_file.to_csv ("XLSX NAME SHOULD = CSV NAME.csv', index = None, header=True)

import os filename_without_extension = os.path.splitext(r'/path/to/some/file.xlsx')[0]) And then use os.path.join() to create whatever you want — ktw
– ktw, Commented Jan 23, 2022 at 0:06
You have a quote wrongly placed, not sure if it's a typo here on SO or in your real code.: str(filename)' — vinalti
– vinalti, Commented Jan 23, 2022 at 0:11

vinalti · Accepted Answer · 2022-01-23 00:07:40Z

1

To get the filename with path but without extension use os.path.splitext

from os import path 

path = "/path/to/file.txt"
path.splitext(path)
# -> ["/path/to/file", "txt"]

To get the filename without the path :

from os import path 

path = "/path/to/file.txt"
path.basename(path)
# -> "file.txt"

So to change the extension from xlsx to csv :

from os import path 

path = "/path/to/file.xlsx"
filename = path.splitext(path)[0] + '.csv'
# -> "/path/to/file.csv"

And if you need to change the path to save the file in another folder, then you can use basename first.

answered Jan 23, 2022 at 0:07

vinalti

1,2742 gold badges8 silver badges33 bronze badges

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

3 Comments

Greencolor Over a year ago

I have multiple files that need to be converted not sure if this helps

vinalti Over a year ago

Then you just need to put this in a loop with for filename in my_path.glob('*.xlsx'):

Greencolor Over a year ago

Thanks, also could you please take look on this stackoverflow.com/questions/70985695/…

Collectives™ on Stack Overflow

Convert Excel to CSV using Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related