How to convert multiple xls files to csv using powershell?

Question

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

$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:

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.

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?

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.

thanks in advance

Do you have a look to the tempory file shown in the error, just to see if there are no file remaining. I met a similar problem in a PDF conversion script and old temprary file remaining were at the origin of the trouble. — JPBlanc
– JPBlanc, Commented May 19, 2011 at 19:42
Unfortunately, the error message only names the Temp directory. I monitored the directory during a run and two files (supposably for each converted xls) with random names showed up and vanished. — wierob
– wierob, Commented May 20, 2011 at 8:33

Joel Spolsky · Accepted Answer · 2011-06-16 02:54:19Z

4

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

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

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.

answered Jun 16, 2011 at 2:54

Joel Spolsky

33.8k18 gold badges91 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cilvic · Accepted Answer · 2011-05-25 09:02:14Z

0

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).

answered May 25, 2011 at 9:02

Cilvic

3,4473 gold badges37 silver badges57 bronze badges

Comments

Camilo Estevez · Accepted Answer · 2016-06-09 22:38:27Z

0

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.

answered Jun 9, 2016 at 22:38

Camilo Estevez

6771 gold badge8 silver badges16 bronze badges

Comments

AJ. · Accepted Answer · 2017-07-21 19:13:33Z

0

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)

edited Jul 21, 2017 at 19:13

answered Jul 21, 2017 at 17:49

AJ.

415 bronze badges

Collectives™ on Stack Overflow

How to convert multiple xls files to csv using powershell?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related