0

This should be a fairly simple question to answer, but I haven't been able to find a resource online. I need to remove rows from a csv with out changing the filename or path. Currently I am trying to import the file, filter the items I need and export that data to the same location with the same file name:

foreach ($csv in $csvLists){
Import-Csv $csv | Where-Object {[DateTime]$_.'Date' -ge $2WeeksOld} | Export-Csv $csv -NoType -Force
}

Also tried:

foreach ($csv in $csvLists){
Import-Csv $csv | Where-Object {[DateTime]$_.'Date' -ge $2WeeksOld} | Export-Csv $csv.PSParentPath -NoType -Force
}

I am getting access denied errors:

Export-Csv : Access to the path 'D:\Test' is denied.
At C:\script.ps1:20 char:71
+ ... .'Date' -ge $2WeeksOld} | Export-Csv $csv.PSParentPath -NoType -Force
+                               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Export-Csv], UnauthorizedAccessException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand

What am I missing here? Is the issue that powershell is reading the file and cant export over it at the same time? Should I try setting the imported data as a variable and then exporting the csv outside the import-csv pipeline?

As always thanks for any help.

Garrett
  • 617
  • 12
  • 30
  • Any chance that you happen to be viewing the file in an app? – EBGreen Jan 18 '18 at 16:47
  • Nevermind, the error looks like you are being denied access to the folder. Do you have write permissions for that folder? – EBGreen Jan 18 '18 at 16:49
  • 1
    Why `$csv.PSParentPath`? Does using `$csv.Fullname` instead resolve the issue? – G42 Jan 18 '18 at 16:49
  • @gms0ulman I was under the impression $csv would contain the file name and PSParentPath would provide the path, not sure why I didn't just use Fullname to begin with! This works, please provide your response as an answer and I will confirm it. Do you have more information on how to properly use PSParentPath? I think I may be using it wrong. – Garrett Jan 18 '18 at 16:56
  • 1
    `$csv.PSParentPath` does contain the directory of a `FileInfo` object, but most people would use `$csv.DirectoryName` or `$csv.Directory.FullName`. However if `$Csv` is `C:\Test\File.csv`, then `Export-Csv -Path $Csv.PSParentPath` is going to try to create a file in `C:\ ` called `Test`. That's not going to work. NTFS won't allow objects with the same name in the same folder. – Bacon Bits Jan 18 '18 at 17:17

2 Answers2

3

The problem here is that you're trying to do everything in one line, and consequently you're opening a file (which locks it) and trying to overwrite the same file (which needs to lock it) at the same time. That won't work.

Try:

foreach ($csv in $csvLists){
    $Data = Import-Csv $csv | Where-Object {[DateTime]$_.'Date' -ge $2WeeksOld}
    $Data | Export-Csv $csv -NoType -Force
}

Or, if $csvLists is an array of FileInfo objects (such as from the output of Get-ChildItem):

foreach ($csv in $csvLists){
    $Data = Import-Csv $csv.FullName | Where-Object {[DateTime]$_.'Date' -ge $2WeeksOld}
    $Data | Export-Csv $csv.FullName -NoType -Force
}
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Thanks for the suggestion, this is basically what I was suggesting in the last bit of my question. This works, although it adds an additional variable and line. I think the response from @gms0ulman will work better for me in this instance. – Garrett Jan 18 '18 at 18:17
1

Use $csv.FullName. This contains the full path for a file/folder.
Here are a few common properties of System.IO.FileInfo:

Property        Contents
BaseName        MyFile
Directory*      <returns System.IO.DirectoryInfo object of "C:\Path\To">
Name            MyFile.txt
FullName        C:\Path\To\MyFile.txt
PSParentPath    Microsoft.PowerShell.Core\FileSystem::C:\Path\To

I'm not aware of a use case for .PSParentPath. Looking at the content, I assume it's used when you need to specify the PSProvider as well as the string value of the parent. Otherwise you could just use .Directory.Name

You may find the class definitions for FileInfo and DirectoryInfo useful. They are the objects returned by Get-ChildItem. The links list Methods and Properties; they do not list Code/Note/ScriptProperty types.

tip: run $csv | Get-Member to see members of this object, look at MemberType.

* For System.IO.DirectoryInfo objects, use Parent. Have got some different behaviour in Win10 and am sure an alias was instroduced so that Parent works for FileInfo or Directory works for DirectoryInfo.

G42
  • 9,791
  • 2
  • 19
  • 34