CODEWITHSUNDEEP
powershell
AHR
AHR

Reputation: 65

Why is PowerShell not saving to working folder?

I have a PowerShell script that converts .xls files in a folder to .csv files. I have eight .xls files (although the number doesn't matter) to be converted to .csv, and they are located in C:\Users\UserName\Documents\ABC\RawData\CSV\WorkingFolder. The script works fine as far as converting .xls to .csv goes, but the problem is that the .csv files [after being created] are getting saved to C:\Users\UserName\Documents, which is the wrong location! I would like the .csv files to be saved to the same folder where the original .xls files are located: C:\Users\UserName\Documents\ABC\RawData\CSV\WorkingFolder, which is also the folder in which I am running the script. I have tried -path . to no avail. Any help would be appreciated! The script is as follows.

$excel = new-object -ComObject "Excel.Application"
$excel.DisplayAlerts = $True
$excel.Visible = $False
$src_dir = $(get-location).Path # this obtains the source location, but not sure how I might
# be able to use it
foreach ($file in get-childitem *.xls) {
$wb = $excel.Workbooks.Open($file.FullName)
$wb.SaveAs($file.Name + ".csv", 6) # 6 -> csv
$wb.Close($True)
}
$excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
get-childitem *.xls | foreach { remove-item -path $_.fullname }
# last line of script deletes the .xls files after they have been converted to .csv,
# more to unclutter than for anything else

Upvotes: 3

Views: 1014

Answers (2)

TJR
TJR

Reputation: 3732

$file.Name represents the file name, not the full path to the file

When you are calling

$wb.SaveAs($file.Name + ".csv", 6) # 6 -> csv

You are telling Excel to save nameofmyfile.xls.csv, and it is saving it to the default folder location which is your documents folder.

What you need to call is

$wb.SaveAs($file.FullName + ".csv", 6) # 6 -> csv

Which will tell Excel to save C:\Users\UserName\Documents\ABC\RawData\CSV\WorkingFolder\nameofmyfile.xls.csv which should save the file to the expected folder location.

Upvotes: 2

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

you can get current location and give it as path 

$localPath=(Get-Location).toString()
$wb.SaveAs($localPath + "\" + $file.Name + ".csv", 6) # 6 -> csv

Upvotes: 1

Related Questions