Search code examples
powershellcsvselect-objectexport-csv

PS Script is exporting an empty CSVs


I am having a helluva time trying to understand why this script is not working as intended. It is a simple script in which I am attempting to import a CSV, select a few columns that I want, then export the CSV and copy over itself. (Basically we have archived data that I only need a few columns from for another project due to memory size constraints). This script is very simple, which apparently has an inverse relationship with how much frustration it causes when it doesn't work... Right now the end result is I end up with an empty csv instead of a csv containing only the columns I selected with Select-Object.

$RootPath = "D:\SomeFolder"

$csvFilePaths = Get-ChildItem $RootPath -Recurse -Include *.csv | 
    ForEach-Object{
        Import-CSV $_ |
        Select-Object Test_Name, Test_DataName, Device_Model, Device_FW, Data_Avg_ms, Data_StdDev | 
        Export-Csv $_.FullName -NoType -Force
}

Solution

  • Unless you read the input file into memory in full, up front, you cannot safely read from and write back to the same file in a given pipeline.

    Specifically, a command such as Import-Csv file.csv | ... | Export-Csv file.csv will erase the content of file.csv.[1]

    The simplest solution is to enclose the command that reads the input file in (...), but note that:

    • The file's content (transformed into objects) must fit into memory as a whole.

    • There is a slight risk of data loss if the pipeline is interrupted before all (transformed) objects have been written back to the file.

    Applied to your command:

    $RootPath = "D:\SomeFolder"
    
    Get-ChildItem $RootPath -Recurse -Include *.csv -OutVariable csvFiles | 
        ForEach-Object{
            (Import-CSV $_.FullName) | # NOTE THE (...)
              Select-Object Test_Name, Test_DataName, Device_Model, Device_FW, 
                            Data_Avg_ms, Data_StdDev | 
                Export-Csv $_.FullName -NoType -Force
    }
    

    Note that I've used -OutVariable csvFiles in order to collect the CSV file-info objects in output variable $csvFiles. Your attempt to collect the file paths via $csvFilePaths = ... doesn't work, because it attempts to collects Export-Csv's output, but Export-Csv produces no output.

    Also, to be safe, I've changed the Import-Csv argument from $_ to $_.FullName to ensure that Import-Csv finds the input file (because, regrettably, file-info object $_ is bound as a string, which sometimes expands to the mere file name).


    A safer solution would be to output to a temporary file first, and (only) on successful completion replace the original file.

    With either approach, the replacement file will have default file attributes and permissions; if the original file had special attributes and/or permissions that you want to preserve, you must recreate them explicitly.


    [1] Note that in other contexts (on Windows) you may simply get an error that doesn't result in the input file's content getting erased; e.g., the following pipeline: Get-Content file.txt | ForEach-Object { "[$_]" } | Set-Content file.txt reports error The process cannot access the file '...\file.txt' because it is being used by another process, without erasing the content of file.txt