如何使用PowerShell将多个xls文件转换为csv？

I'm trying to convert multiple Excel files (xls) to csv using the following powershell script:

我正在尝试使用以下powershell脚本将多个Excel文件（xls）转换为csv：

$excel = new-object -ComObject "Excel.Application"
$excel.DisplayAlerts=$True
$excel.Visible =$false
foreach ($file in get-childitem $src_dir) {
    $wb = $excel.Workbooks.Open($file.FullName)
    $wb.SaveAs($dst_dir + $file.Name + ".csv", 6)# 6 -> csv
    $wb.Close($True)
}
$excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

In principle this works e.g. I get csv files. However, for a few files (varying per run) I get an exception:

原则上这有效，例如我得到了csv文件。但是，对于一些文件（每次运行不同），我得到一个例外：

Exception calling "SaveAs" with "2" argument(s): "Microsoft Office Excel cannot access the file 'C:\Users\...\AppData\Local\Temp'. ...

Additionally, I get a message box asking if I want to save the changes to the source xls.

另外，我收到一个消息框，询问我是否要将更改保存到源xls。

Once I call SaveAs, $wb references the new file. So how do I save or discard the changes to the source file? Why does this happen only for a few files? Are there any other problems with this script?

一旦我调用SaveAs，$ wb就会引用新文件。那么如何保存或放弃对源文件的更改？为什么这只发生在几个文件中？这个脚本还有其他问题吗？

Update

更新

I divided the input files (ca. 200) arbitrarily (i.e. don't know the size of the groups) into 10 groups and processed each group in its own run. That worked so it is somewhat inconvenient.

我将输入文件（大约200）任意划分（即不知道组的大小）为10组，并在自己的运行中处理每个组。这样做有点不方便。

thanks in advance

提前致谢

4 个解决方案

#1

Try moving the code to launch and quit excel INSIDE your loop.

尝试移动代码以启动并退出excel INSIDE你的循环。

Yeah, it's slower that way, but it'll encourage Excel to clean up its temp files between each operation.

是的，它的速度较慢，但它会鼓励Excel在每次操作之间清理临时文件。

It won't be as slow as you think because Windows and COM are smart enough to keep Excel mostly in memory even after you quit for a few seconds so that the next time you create an Excel object it'll happen fast, exactly for situations like this.

它不会像你想象的那么慢，因为即使在你退出几秒钟之后，Windows和COM也足够聪明地将Excel保留在内存中，以便下次创建Excel对象时它会快速发生，完全适合情况喜欢这个。

#2

Maybe you can implement a timer to wait a while? Or you have a look inside the direcotry while you script is running and then remove the temp files after each cycle (one xls file conversion).

也许你可以实现一个等待一段时间的计时器？或者您在脚本运行时查看目录内部，然后在每个周期后删除临时文件（一次xls文件转换）。

#3

I believe you can avoid the message box asking if you want to save by using $wb.Close($False) instead of $wb.Close($True). That's what I use normally and I don't get any message boxes.

我相信你可以通过使用$ wb.Close（$ False）而不是$ wb.Close（$ True）来避免询问是否要保存的消息框。这就是我正常使用的，我没有得到任何消息框。

#4

My updated script with timer - may not be exact answer but will help someone looking for a script:

我的更新脚本与计时器 - 可能不是确切的答案，但将帮助寻找脚本的人：

$excel = new-object -ComObject "Excel.Application"
$excel.DisplayAlerts=$True
$excel.Visible =$false
$src_dir='D:\folderwithxlsx\'
$dst_dir='D:\folderwithcsv\'
Get-ChildItem $src_dir -Filter *.xlsx |
Foreach-Object{
  'processing '+$_.FullName
  $wb = $excel.Workbooks.Open($_.FullName)
  $dst_file=$dst_dir + $_.BaseName + ".csv"
  $wb.SaveAs($dst_file, 6)# 6 -> csv
  'saved '+$dst_file
  $wb.Close($True)
  Start-Sleep -Seconds 2
 }
$excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

#1