如何*快速*将许多.txt文件转换为.xls文件 [英] How to *quickly* convert many .txt files into .xls files

问题描述

更新：我刚刚发现，有一个更强大的服务器的人将会处理我被分配的任务，所以没有使这个程序足够快。然而，下面的答案（自动化Excel）有助于使程序快三倍，所以我建议给具有较少（但仍然很多）文件的人。

我'尝试将许多（超过30万）.txt文件转换为.xls文件。我在这里找到了如何做：

使用VBA将TXT批量转换为XLS

但是真的很慢（在一个多小时内，它只转换了我们的300,000的文件），即使文件不是那么大。

我尝试通过关闭ScreenUpdating来加快速度，但是我无法成功关闭ScreenUpdating。有人可以解释在哪里关闭ScreenUpdating，以便我的代码运行得更快？或者，更好的是，任何有效的方案的想法？

这里是代码：

  Sub TXTconvertXLS（）
 
'变量
 Dim wb As Workbook 
 Dim strFile As String 
 Dim strDir As String 
 
 Application.ScreenUpdating = False 
'目录
 strDir ='路径到这里
 strFile = Dir（strDir&* .txt）
 
做strFile<> 
 
设置wb = Workbooks.Open（strDir& strFile）
使用wb 
 .SaveAs替换（wb.FullName，.txt，.xls） ，50 
 .Close False'& - 已经保存在
上方的行中
结束设置wb = Nothing 
 strFile = Dir'<  - 将下一个文件名填入strFile 
循环
 Application.ScreenUpdating = True 
 End Sub 
  

解决方案

几个选项应该更快。

1. 使用 Powershell （将代码保存在记事本中，如 xx.ps1 ，更新源目录并运行）


2. 在隐藏的实例中自动化Excel而不是现在的。

Powershell

在 https：/ /superuser.com/questions/875831/using-po wershell-is-it-possible-to-convert-an-xlsx-file-to-xls 和使用Powershell循环浏览Excel文件并检查电子表格名称是否存在

  $ files = Get-ChildItem C：\Temp\ * .txt 
写入加载文件...
 
 $ Excel = New-Object -ComObject Excel.Application 
 $ Excel.visible = $ false 
 $ Excel.DisplayAlerts = $ false 
 
 ForEach（$ file in $ files）
 {
 
 $ WorkBook = $ Excel.Workbooks.Open（$ file.Fullname）
 $ NewFilepath = $ file.Fullname -replace。{4} $
 $ NewFilepath = $ NewFilepath +.xls
 $ Workbook.SaveAs（$ NewFilepath，56）
 
} 
 Stop-Process -processname EXCEL 
 $ Excel.Quit ）
  

自动化Excel

  Sub TXTconvertXLS2（）
 
 Dim objExcel作为Excel.Application 
 Dim wb As Workbook 
 Dim strFile As String 
 Dim strDir As String 
 
设置objExcel =新建Excel.Application 
带objExcel 
。 Visible = False 
 .DisplayAlerts = False 
结束
 
'目录
 strDir =c：\temp\
 strFile = Dir strDir& * .txt）
 
'Loop 
尽管strFile<> 
设置wb = objExcel.Workbooks.Open（strDir& strFile）
与wb 
 .SaveAs替换（wb.FullName，.txt，.xls），50 
 .Close False'& - 已经保存在
以上的行$ End in 
设置wb = Nothing 
 strFile = Dir'<  - 将下一个文件名填入strFile 
 Loop 
 
 objExcel.DisplayAlerts = False 
 objExcel.Quit 
 Set objExel = Nothing 
 
 End Sub 
  

Update: I just found out that someone with a more powerful server is going to work on the task I was assigned, so it's fine that I didn't make this program fast enough. However, the answer below (automating Excel) helped make the program three times faster, so I'd recommend it to someone with fewer (but still many) files.

I'm trying to convert many (over 300,000) .txt files into .xls files. I found out how to do it here:

Batch Convert TXT to XLS Using VBA

But it's really slow (in over an hour, it only converted ~200 our of 300,000 of the files), even though the files aren't that big.

I tried speeding it up by turning off ScreenUpdating, but I wasn't able to turn off ScreenUpdating successfully. Can someone explain where in the to turn off ScreenUpdating so that my code will run quicker? Or, better yet, any ideas for a more efficient program?

Here's the code:

Sub TXTconvertXLS()

'Variables
Dim wb As Workbook
Dim strFile As String
Dim strDir As String

 Application.ScreenUpdating = False
'Directories
strDir = 'path went here
strFile = Dir(strDir & "*.txt")

 Do While strFile <> ""

    Set wb = Workbooks.Open(strDir & strFile)
        With wb
            .SaveAs Replace(wb.FullName, ".txt", ".xls"), 50
            .Close False   '<-already saved in the line directly above
        End With
    Set wb = Nothing
    strFile = Dir   '<- stuffs the next filename into strFile
Loop
Application.ScreenUpdating = True
End Sub

解决方案

A couple of options which should be quicker.

1. Use Powershell (saves the code below in Notepad as say xx.ps1, update your source directory and run)
2. Automate Excel in a hidden instance rather than in your current one.

Powershell

Drawing on https://superuser.com/questions/875831/using-powershell-is-it-possible-to-convert-an-xlsx-file-to-xls and Using Powershell to loop through Excel files and check if Spreadsheet name exists

$files = Get-ChildItem C:\Temp\*.txt
Write "Loading Files..."

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $false
$Excel.DisplayAlerts = $false

ForEach ($file in $files)
{

     $WorkBook = $Excel.Workbooks.Open($file.Fullname)
     $NewFilepath = $file.Fullname -replace ".{4}$"
     $NewFilepath =  $NewFilepath + ".xls"
     $Workbook.SaveAs($NewFilepath,56)   

}
  Stop-Process -processname EXCEL
  $Excel.Quit()

Automate Excel

Sub TXTconvertXLS2()

Dim objExcel As Excel.Application
Dim wb As Workbook
Dim strFile As String
Dim strDir As String

Set objExcel = New Excel.Application
With objExcel
    .Visible = False
    .DisplayAlerts = False
End With

    'Directories
    strDir = "c:\temp\"
    strFile = Dir(strDir & "*.txt")

    'Loop
     Do While strFile <> ""
        Set wb = objExcel.Workbooks.Open(strDir & strFile)
            With wb
                .SaveAs Replace(wb.FullName, ".txt", ".xls"), 50
                .Close False   '<-already saved in the line directly above
            End With
        Set wb = Nothing
        strFile = Dir   '<- stuffs the next filename into strFile
    Loop

objExcel.DisplayAlerts = False
objExcel.Quit
Set objExel = Nothing

End Sub

这篇关于如何*快速*将许多.txt文件转换为.xls文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！