Why does PowerShell not want to save my file as a CSV

Question

$Path = 'D:/ETL_Data/TwitchTVData.xlsx'
$csvPath = 'D:/ETL_Data/TwitchTVData2.csv'

# Open the Excel document and pull in the 'Sheet1' worksheet
$Excel = New-Object -Com Excel.Application
$Workbook = $Excel.Workbooks.Open($Path)
$page = 'Sheet1'
$ws = $Workbook.Worksheets | Where-Object {$_.Name -eq $page}
$Excel.Visible = $true
$Excel.DisplayAlerts = $false

# Set variables for the worksheet cells, and for navigation
$cells = $ws.Cells
$row = 1
$col = 4
$formula = @"
=NOW()
"@

# Add the formula to the worksheet
$range = $ws.UsedRange
$rows = $range.Rows.Count
for ($i=0; $i -ne $rows; $i++) {
    $cells.Item($row, $col) = $formula
    $row++
}

$ws.Columns.Item("A:D").EntireColumn.AutoFit() | Out-Null
$ws.Columns.Range("D1:D$rows").NumberFormat = "yyyy-MM-dd hh:mm"

$Excel.ActiveWorkbook.SaveAs($csvPath)
$Excel.Quit()

[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

https://www.experts-exchange.com/questions/27996530/How-to-convert-and-xlsx-spreadsheet-into-CSV.html#answer38780402-20

I was attempting to follow that, but for some reason for me the SaveAs() doesn't work. It gives me an error

cannot access the file 'D://ETL_Data/E567DF00

What do I have to do to get this to save over to CSV?

Edit:

Exact error without the fileformat parameter 6 as suggested in the comments:

Microsoft Excel cannot access the file 'D:\//ETL_Data/8011FF00'. There are
several possible reasons:
o The file name or path does not exist.
o The file is being used by another program.
o The workbook you are trying to save has the same name as a currently open
  workbook.
At D:\PS_Scripts\twitchExcelAddSnapShot.ps1:32 char:1
+ $Excel.ActiveWorkbook.SaveAs($csvPath)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Exact error with fileformat parameter 6:

The file could not be accessed. Try one of the following:
o Make sure the specified folder exists.
o Make sure the folder that contains the file is not read-only.
o Make sure the file name does not contain any of the following characters:
  <  >  ?  [  ]  :  | or  *
o Make sure the file/path name doesn't contain more than 218 characters.
At D:\PS_Scripts\twitchExcelAddSnapShot.ps1:32 char:1
+ $Excel.ActiveWorkbook.SaveAs($csvPath,6)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Mathias R. Jessen · Accepted Answer · 2017-04-08 13:39:42Z

4

While PowerShell is pretty forgiving when it comes to path separators, COM+ servers (like Excel.Application) might not be.

Change the $csvPath variable value to use \ instead of /:

$csvPath = 'D:\ETL_Data\TwitchTVData2.csv'

edited Apr 8, 2017 at 13:39

answered Apr 8, 2017 at 10:36

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

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

Comments

mklement0 · Accepted Answer · 2020-06-19 16:28:01Z

To complement Mathias R. Jessen's helpful answer with background information:

It seems that Excel's application-specific behavior is the cause of your problem, unrelated to the underlying foundational subsystems or APIs used.
(Excel's automation API happens to be a COM server.)
I'm unclear on why Excel acts this way - it also does so in interactive use, though you could argue that at least in programmatic use it should allow / too.

To offer generalized advice:

On Windows, to be safe, use \, especially when dealing with application-level automation APIs, though at the level of system APIs / should work as well - see below.
In cross-platform code, use / (but watch out for the exceptions above; [System.IO.Path]::DirectorySeparatorChar reports the platform-appropriate (primary) character).

Though rarely used, Windows at the API level allows interchangeable use of \ and / (which apparently goes back to the DOS 2.0 days), when support for directories was introduced), and that is also reflected in higher-level subsystems, as the following examples demonstrate.

# PS: OK
# Should output c:\windows\win.ini
(Get-Item c:/windows/win.ini).FullName

# .NET: OK
# Should return the 1st child dir.'s path.
# Note that the child directory names will be appended with "\", not "/"
[System.IO.Directory]::GetDirectories('c:/windows/system32') | Select-Object -First 1

# COM (see Excel exception below): OK
# Should return $true
(New-Object -ComObject Scripting.FileSystemObject).FileExists('c:/windows/win.ini')

# Windows API: OK
# Should return a value such as 32.
(Add-Type -PassThru WinApiHelper -MemberDefinition '[DllImport("kernel32.dll")] public static extern uint GetFileAttributes(string lpFileName);')::GetFileAttributes('c:/windows/win.ini')

# cmd.exe: INCONSISTENTLY SUPPORTED
# Note: *quoting matters* here, so that tokens with / aren't mistaken for options.
#     attrib: works
cmd /c 'attrib "c:/windows/win.ini"'  
#     dir: works with DIRECTORIES, but fails with FILES
cmd /c 'dir /b "c:/windows/system32"'  # OK
cmd /c 'dir /b "c:/windows/win.ini"'  # FAILS with 'File not found'
cmd /c 'dir /b "c:/windows\win.ini"'  # Using \ for the FILE component (only) works.

Here's a minimal example that demonstrates Excel's problem with /:

# Create temporary dir.
$null = mkdir c:\tmp -force

$xl=New-Object -Com Excel.Application
$wb = $xl.Workbooks.Add()

# OK - with "\"
$wb.SaveAs('c:\tmp\t1.xlsx')

# FAILS - with "/":
# "Microsoft Excel cannot access the file 'C:\//tmp/F5D39400'"
$wb.SaveAs('c:/tmp/t2.xlsx')

$xl.Quit()

# Clean up temp. dir.

Collectives™ on Stack Overflow

Why does PowerShell not want to save my file as a CSV

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related