我需要以自動方式將 csv 檔案轉換為 Excel 檔案。我無法使用相應 csv 檔案的名稱命名 Excel 檔案。我將 csv 檔案保存為“Trials_1”、“Trials_2”、“Trilas_3”，但使用我撰寫的 Python 代碼時出現錯誤，并要求我提供名為“Trials_4”的 csv 檔案。然后，如果我將 csv 檔案“Trials_1”重命名為“Trials_4”，程式將運行并生成一個名為“Trials_1”的 Excel 檔案。如何更正我的代碼？
'''
import csv
import openpyxl as xl
import os, os.path
directory=r'C:\\Users\\PycharmProjects\\input\\'
folder=r'C:\\Users\\PycharmProjects\\output\\'
for csv_file in os.listdir(directory):
def csv_to_excel(csv_file, excel_file):
csv_data=[]
with open(os.path.join(directory, csv_file)) as file_obj:
reader=csv.reader(file_obj)
for row in reader:
csv_data.append(row)
workbook= xl.Workbook()
sheet=workbook.active
for row in csv_data:
sheet.append(row)
workbook.save(os.path.join(folder,excel_file))
if __name__=="__main__":
m = sum(1 for f in os.listdir(directory) if os.path.isfile(os.path.join(directory, f)))
new_name = "{}Trial_{}.csv".format(directory, m 1)
k = sum(1 for file in os.listdir(folder) if os.path.isfile(os.path.join(folder, file)))
new_name_e = "{}Trial_{}.xlsx".format(folder, k 1)
csv_to_excel(new_name,new_name_e)
'''
謝謝。
uj5u.com熱心網友回復：
每個 csv 作為單獨的 excel 檔案
import glob
import pandas as pd
import os
csv_files = glob.glob('*.csv')
for filename in csv_files:
sheet_name = os.path.split(filename)[-1].replace('.csv', '.xlsx')
df = pd.read_csv(filename)
df.to_excel(sheet_name, index=False)
不同作業表中同一個excel中的所有csv
import glob
import pandas as pd
import os
# Create excel file
writer = pd.ExcelWriter('all_csv.xlsx')
csv_files = glob.glob('*.csv')
for filename in csv_files:
sheet_name = os.path.split(filename)[-1].replace('.csv', '')
df = pd.read_csv(filename)
# Append each csv as sheet
df.to_excel(writer, sheet_name=sheet_name, index=False)
writer.save()
uj5u.com熱心網友回復：
嗨 Annachiara 歡迎來到 StackOverflow，
我將僅使用熊貓來修改“csv_to_excel”函式。
在此之前，您應該使用以下命令安裝“xlsxwriter”：
pip install XlsxWriter
那么函式應該是這樣的：
def csv_to_excel(csv_file,excel_file,csv_sep=';'):
# read the csv file with pandas
df=pd.read_csv(csv_file,sep=csv_sep)
# create the excel file
writer=pd.ExcelWriter(excel_file, engine='xlsxwriter')
# copy the csv content (df) into the excel file
df.to_excel(writer,index=False)
# save the excel file
writer.save()
# print what you converted for reference
print(f'csv file {csv_file} saved as excel in {excel_file}')
只需確保正確讀取 csv：我只添加了分隔符引數，但您可能想要添加所有其他引數（如決議日期等）
然后你可以用for回圈轉換csv檔案串列（我用了更多的步驟讓它更清晰）
dir_in=r'C:\\Users\\PycharmProjects\\input\\'
dir_out=r'C:\\Users\\PycharmProjects\\output\\'
csvs_to_convert=os.listdir(dir_in)
for csv_file_in in csvs_to_convert:
# remove extension from csv files
file_name_no_extension=os.path.splitext(csv_file_in)[0]
# add excel extension .xlsx
excel_name_out=file_name_no_extension '.xlsx'
# write names with their directories
complete_excel_name_out=os.path.join(dir_out,excel_name_out)
complete_csv_name_in=os.path.join(dir_in,csv_file_in)
# convert csv file to excel file
csv_to_excel(complete_csv_name_in,complete_excel_name_out,csv_sep=';')
uj5u.com熱心網友回復：
假設您希望保持代碼的相同結構，我只是在您的代碼中修復了一些技術問題以使其正常作業（請將檔案夾路徑更改為您自己的）：
import csv
import openpyxl as xl
import glob, os, os.path
directory= 'input'
folder= '../output' # Since 'input' would be my cwd, need to step back a directory to reach 'output'
# Using your function, just passing different arguments for convinient.
def csv_to_excel(f_path, f_name):
csv_data=[]
with open(f_path, 'r') as file_obj:
reader=csv.reader(file_obj)
for row in reader:
csv_data.append(row)
workbook= xl.Workbook()
sheet=workbook.active
for row in csv_data:
sheet.append(row)
workbook.save(os.path.join(folder, f_name ".xlsx"))
def main():
os.chdir(directory) # Defining input directory as your cwd
# Searching for all files with csv extention and sending each to your function
for file in glob.glob("*.csv"):
f_path = os.getcwd() '\\' file # Saving the absolute path to the file
f_name = (os.path.splitext(file)[0]) # Saving the name of the file
csv_to_excel(f_path, f_name)
if __name__=="__main__":
main()
PS：請避免迭代函式的定義，因為您只需要定義一次函式。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/403375.html
標籤：