Best way to read and write to same CSV from multiple scripts.

PowerShell Help
1

Hi

I have a script which I run several times, it takes info from a CSV and writes a status to the same CSV.

In sudo:

[pre]$table = Import-CSV $CSVFiles

Foreach ($row in $table={

Do-Something $Row.Source $row.Destination

$Row.Status = “Finsihed”

$Table | Export-CSV $csvfile

}[/pre]

I have how ever encountered that the file is getting cut off, so I have implemented a “Lock file” so I create a lockfile and wait 1 second then do something and then wait 1 second and then deletes the lockfile again. then of cause I check for the lockfile each time I write/read from the csv file. I suspect that what’s happening is that the script sometimes reads the CSV in the same moment at a write is occuring, so it only reads half the CSV.

But I don’t thing this is the best way to do this, what would be the best solution to reading/writing to the same CSV file.

Is there anyway to check whether a file is being writen to?

Regards Lars

2

From your topic subject, it sounds like you’re running multiple scripts that need to write to the same CSV file at the same time, but I guess I don’t see that in your post. Are you running multiple copies of the same script all at once? Are they all on the same machine or multiple machines?

In my experience, your best bet is going to be staying away from multiple processes using the same file at the same time. If you can keep all of the execution within 1 script, or schedule the scripts so that they run one-at-a-time, life will be much easier for you. You’ll get some strange stuff going on as the file gets updated and reads are no longer accurate, etc., etc.

If you keep going this way, though, you should be able to catch the write failures using a try…catch block around the Export-Csv statement. That will throw an IOException that you can catch and then retry the write. The reads shouldn’t matter because it doesn’t appear that Import-Csv cares about the file being open. Not sure if it is going to care about the file being written in the same millisecond it’s being read, but in your testing you can set up an open try…catch that writes out any exception it throws, and then you can modify your script to handle the specific type of exceptions you encounter.

Depending on how the script is run, have you looked at running at running multiple threads from the same script? That would allow you to read the file once, create a bunch of threads, hold the results in memory until all threads are complete, and then write once to the output file.

3

The pseudo code you posted is not a best practice as you are taking the entire contents of a CSV, doing work on one row and then updating\overwriting the entire CSV for each row. A better approach would be something like this where you are just updating the object in memory with status and then writing all results one time:

#Locked
$table = Import-CSV $csvfile
#UnLocked

foreach ($row in $table) {
    try {
        ./Do-SomeThing.ps1 $Row.Source $row.Destination -ErrorAction Stop
        $status = "Finished"
    }
    catch {
        $status = 'Failed' 
    }

    $Row.Status = $status
}

#Locked
$Table | Export-CSV $csvfile
#UnLocked

If you are trying to have multiple scripts do work on single CSV at the same time, there are going to most likely unexpected behavior. You could also use an approach like so where you are doing multiple scripts against the same row:

#Locked
$table = Import-CSV $csvfile
#UnLocked

foreach ($row in $table) {
    

    try {
        ./Do-SomeThing.ps1 $Row.Source $row.Destination -ErrorAction Stop
        $status = "Finished"
    }
    catch {
        $status = 'Failed' 
    }

    try {
        ./Do-AnotherThing.ps1 $Row.Name -ErrorAction Stop
        $patched = $true

    }
    catch {
        $patched = $false
    }
   
    $Row.Status  = $status
    $Row.Patched = $patched

}

#Locked
$Table | Export-CSV $csvfile
#UnLocked

The goal is to minimize file operations and looping as little as possible, both operations can impact performance of your script.