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

Welcome Forums General PowerShell Q&A Best way to read and write to same CSV from multiple scripts.

This topic contains 2 replies, has 3 voices, and was last updated by

 
Participant
1 week, 1 day ago.

  • Author
    Posts
  • #176065

    Participant
    Topics: 6
    Replies: 9
    Points: -7
    Rank: Member

    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:

    $table = Import-CSV $CSVFiles
    
    Foreach ($row in $table={
    
    Do-Something $Row.Source $row.Destination
    
    $Row.Status = "Finsihed"
    
    $Table | Export-CSV $csvfile
    
    }

    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

  • #176125

    Participant
    Topics: 6
    Replies: 78
    Points: 143
    Helping Hand
    Rank: Participant

    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.

  • #176182

    Participant
    Topics: 8
    Replies: 1203
    Points: 705
    Helping Hand
    Rank: Major Contributor

    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.

You must be logged in to reply to this topic.