将EXCEL文件批量转为CSV文件

2021年06月22日 Python 暂无评论 阅读 246 次

由于python中用pandas处理EXCEL文档速度很慢,当有大量excel文件需要处理时,可先用excel自带的VBA工具将目录内的excel文档批量转化为CSV(逗号分隔符文件),可以大幅增加pandas处理数据的速度。具体操作如下:

  1. 新建一个excel文档(带宏)
  2. 按alt+F11打开VBA编辑器
  3. 双击左侧的Sheet1,打开一个空白代码窗口
  4. 粘贴如下代码:
Sub WorkbooksSaveAsCsvToFolder()
'UpdatebyExtendoffice20181031
Dim xObjWB As Workbook
Dim xObjWS As Worksheet
Dim xStrEFPath As String
Dim xStrEFFile As String
Dim xObjFD As FileDialog
Dim xObjSFD As FileDialog
Dim xStrSPath As String
Dim xStrCSVFName As String
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    On Error Resume Next
Set xObjFD = Application.FileDialog(msoFileDialogFolderPicker)
    xObjFD.AllowMultiSelect = False
    xObjFD.Title = "Kutools for Excel - Select a folder which contains Excel files"
    If xObjFD.Show <> -1 Then Exit Sub
    xStrEFPath = xObjFD.SelectedItems(1) & ""
 
    Set xObjSFD = Application.FileDialog(msoFileDialogFolderPicker)
 
    xObjSFD.AllowMultiSelect = False
    xObjSFD.Title = "Kutools for Excel - Select a folder to locate CSV files"
    If xObjSFD.Show <> -1 Then Exit Sub
    xStrSPath = xObjSFD.SelectedItems(1) & ""
 
    xStrEFFile = Dir(xStrEFPath & "*.xls*")
 
    Do While xStrEFFile <> ""
        Set xObjWB = Workbooks.Open(Filename:=xStrEFPath & xStrEFFile)
        xStrCSVFName = xStrSPath & Left(xStrEFFile, InStr(1, xStrEFFile, ".") - 1) & ".csv"
        xObjWB.SaveAs Filename:=xStrCSVFName, FileFormat:=xlCSV
        xObjWB.Close savechanges:=False
        xStrEFFile = Dir
  Loop
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

6. 点击菜单栏 运行->运行子过程,或按F5执行。

给我留言

您必须 登录 才能发表留言!

站点定制:GY TECH. 网站设计:Ality
Copyright © 桂圆 TECH. 保留所有权利.  
耗时0.430秒执行了33次数据库检索
此破站已苟活3081天3小时10分39秒!

用户登录