Converting any .csv file to an excel file using Python-CodePudding

I'm trying to convert any .csv file from this path to an excel file. The code works but I need to rename that .csv file manually. Is there a way to read and convert whatever .csv file without renaming it? Your input is highly appreciated. Thanks a lot!

import pandas as pd

read_file = pd.read_csv(r'C:/Processed_Report/Source1.csv')
read_file.to_excel (r'C:/Processed_Report/Source.xlsx', index = None, header=True)

CodePudding user response：

I don't sure if i understood, but this is how you can read all file inside a folder and convert them to excel files.

import os

for root, dirs, files in os.walk("C:/Processed_Report/", topdown=False):
    for name in files:
        base_name, ext = os.path.splitext(name)  #Split name, extension
        if ext in ".cvs":
            df = pd.read_csv(os.path.join(root, name))
            df.to_excel(os.path.join(root, f'{base_name}.xlsx'))

CodePudding user response：

If I understood you right, you are looking to open CSV file in a folder and you do not know it's name. This will loop through all the CSVs:

import glob
path = "path/to/dir/*.csv"
for fname in glob.glob(path):
     read_file = pd.read_csv.ext = (fname)

CodePudding user response：

Using pathlib

from pathlib import Path

import pandas as pd


file_path = r"C:/Processed_Report"
files = [x for x in Path(file_path).glob("*csv")]
[pd.read_csv(x).to_excel(f"{file_path}/{x.stem}.xlsx", index=False) for x in files]