Why is Export-CSV overwriting the entire CSV contents instead of just specified data?

Question

Part2List.csv:

Name,Date,Location
ADMRYANTEST1, 08/03/2018, 1
ADMRYANTEST2, 09/03/2018, 1

PowerShell command:

Import-Csv -Path "C:\Part2List.csv" | Where-Object {$_.Date -gt (Get-Date)} | Export-Csv -Path "C:\Part2List.csv" -NoTypeInformation

(Get-Date) is today: August 3, 2018. The command should only erase ADMRYANTEST1.

Why is my entire file being erased?

To me it is not clear if the $_.date holds `dd/MM/yyyy` or `MM/dd/yyyy`. Try `[datetime]::ParseExact()` before the compare — Theo, Aug 03 '18 at 21:20
@Theo It is mm/dd/yyyy. Not sure if this is what you meant - but I tried `Where-Object {[datetime]::ParseExact($_.Date) -gt (Get-Date)}` and it didn't fix it. — hagan10, Aug 03 '18 at 21:27
Did you get the answer you needed? If so, please accept the appropriate one; otherwise, please provide feedback. I've noticed that you've only accepted answer to 3 out of your 12 questions so far, so I encourage you to revisit your old questions too to see if answers should be accepted there. — mklement0, Oct 12 '18 at 14:11

mklement0 · Answer 1 · 2018-08-04T02:12:37.770

Why is my entire file being erased?

Because you cannot read from and write back to the same file in the same pipeline, UNLESS you make sure that the input file is read into memory, in full, before its lines are sent through the pipeline.

To that end, enclose the command that reads the input file in parentheses ((...)), which ensures that it is run to completion, with all output collected in memory and the input file closed again, before pipeline processing starts:

(Import-Csv -Path "C:\Part2List.csv") | # !! Note the (...)
  ... | 
    Export-Csv -Path "C:\Part2List.csv" -NoTypeInformation

Note that this approach bears a slight risk of data loss, if the pipeline is interrupted before writing all data back to the input file has completed.

A more robust approach is to write to a temporary file first, and, on successful completion (only), replace the original file.

With either approach, if the original file had special permissions, alternate data streams, ..., you may want to recreate these too.

As pointed out in Theo's answer, you have an additional problem: CSV column values are always imported as strings, so you must perform explicit conversions as needed:

Since you're doing a date comparison, you must convert $_.Date to a [datetime] instance; since your input is in format MM/dd/yyyy, which is the invariant culture's^[1] short date pattern, you can simply cast to [datetime].

(Import-Csv -Path "C:\Part2List.csv") | 
  Where-Object { [datetime] $_.Date -gt (Get-Date) } | 
    Export-Csv -Path "C:\Part2List.csv" -NoTypeInformation

^{[1] PowerShell uses the invariant culture ([cultureinfo]::InvariantCulture) rather than the current culture for to-and-from string conversions in most contexts. For more information, see this answer.}

score 1 · Answer 2 · answered Aug 03 '18 at 21:44

After importing the CSV, $_.Date holds a string, not a DateTime object. Later, you try and compare this string to the current date (= object). Because the format is 'MM/dd/yyyy' as you say, you should do something like this to have Powershell be able to compare this as DateTime object. That is where the DateTime]::ParseExact() function comes in:

Import-Csv -Path "C:\Part2List.csv" | Where-Object {([DateTime]::ParseExact($_.Date, "MM/dd/yyyy", [System.Globalization.CultureInfo]::InvariantCulture)) -gt (Get-Date)} | Export-Csv -Path "C:\Part2List_Output.csv" -NoTypeInformation -Force

Also, if no date greater than today is found in the CSV the output will be empty.. (can there be future dates in the csv at all?)

Note that i have changed the name of the output so you don't overwrite the original csv file

You are right, neverthless you should enclose the Import-Csv in parentheses to get the content into memory before writing to the same file name. — , Aug 03 '18 at 21:47
True, but my code saves to a new file, not the same file that is being read in. That would indeed need the parentheses. — Theo, Aug 04 '18 at 07:54

score 0 · Answer 3 · answered Aug 03 '18 at 21:35

0

Can't have the command in 1 line beacuse the file is locked when it is opened per https://stackoverflow.com/a/48326987/6402199.

This fixes it!

$new = Import-Csv -Path "C:\Part2List.csv" | Where-Object {$_.Date -gt (Get-Date)}
$new | Export-Csv -Path "C:\Part2List.csv" -NoTypeInformation

answered Aug 03 '18 at 21:35

hagan10

153
2
12

1

You can also put the `Import-Csv` part in parentheses to ensure it's fully executed before passing the results down the pipeline. – Joey Aug 03 '18 at 21:43
No it doesn't. It just happens that the string from the csv compares greater than Get-Date cast to a string. – Aug 03 '18 at 21:45

Why is Export-CSV overwriting the entire CSV contents instead of just specified data?

3 Answers3