【文件操作】csv批量转换Excel，然后汇总

Sub test()
Dim str()
Dim i As Integer
Dim wb, wb1 As Workbook
Dim sht As Worksheet

On Error Resume Next '这一句上课时候没加，加上以后防止点了取消发生的错误
Set wb1 = ActiveWorkbook
Set sht1 = ActiveSheet
On Error Resume Next
str = Application.GetOpenFilename("Excel数据文件,*.xls*", , , , True)

    For i = LBound(str) To UBound(str)
        Set wb = Workbooks.Open(str(i))
        For Each sht In wb.Sheets
            sht.Copy after:=wb1.Sheets(wb1.Sheets.Count)
            wb1.Sheets(wb1.Sheets.Count).Name = Split(wb.Name, ".")(0) & sht.Name
        Next
        wb.Close
    Next


End Sub

把乱码的csv转换成Excel，然后把Excel，整理成一个工作簿的多个工作表

今天又试了一下，如果csv是空的会报错

import pandas as pd
import os
dir1 = r"D:\test\csv"
for root,dirs,files in os.walk(dir1):
    for file in files:
        fpth = os.path.join(root,file)
        df = pd.read_csv(fpth)
        fp1 = fpth.replace('.csv', '.xlsx')
        print(fp1)
        df.to_excel(fp1,encoding = 'utf-8',sheet_name)
        print(fname)