Haypoh

Fix

Fix Allergan in your CSV, re-save, then run this. Save as Desktop\upload_final.ps1 in Notepad (Save as type: All Files) rather than pasting:

powershell

$csv      = "C:\Users\HU346SK\OneDrive - EY\Desktop\mapping_multi.csv"
$source   = "C:\Users\HU346SK\OneDrive - EY\Desktop\Sonnet upload"
$destRoot = "C:\Users\HU346SK\OneDrive - EY\Vitality Acute Program - 17. Outside Counsel Folder Structure"
$dryRun   = $true

function Get-Files($cell) {
    ($cell -split "`n") | ForEach-Object {
        $t = $_.Trim() -replace '^[\u2022\-\*]\s*',''
        if ($t -match '^(.+?\.(pdf|docx?|msg|xlsx?))') { $Matches[1] }
    } | Where-Object { $_ }
}

$log = foreach ($row in Import-Csv $csv) {
    $vendorPath = Join-Path $destRoot $row.Vendor.Trim()
    $master     = $row.Master.Trim()
    if (-not (Test-Path $vendorPath)) {
        [pscustomobject]@{Vendor=$row.Vendor; File=$master; Target=""; Status="VENDOR FOLDER NOT FOUND"}
        continue
    }
    $base = [IO.Path]::GetFileNameWithoutExtension($master)
    $subs = Get-ChildItem $vendorPath -Directory -ErrorAction SilentlyContinue
    $m = @($subs | Where-Object { $_.Name -eq $base -or $_.Name -eq $master })
    if ($m.Count -eq 0) {
        $m = @($subs | Where-Object { $_.Name -like "$base*" })
        if ($m.Count -ne 1) { $m = @() }
    }
    if ($m.Count -eq 0) {
        [pscustomobject]@{Vendor=$row.Vendor; File=$master; Target=""; Status="NO SUBFOLDER MATCH"}
        continue
    }
    $target = $m[0].FullName
    $files  = @($master) + (Get-Files $row.RolledUp) | Select-Object -Unique
    foreach ($f in $files) {
        if (Test-Path (Join-Path $target $f)) {
            [pscustomobject]@{Vendor=$row.Vendor; File=$f; Target=$m[0].Name; Status="ALREADY THERE"}
            continue
        }
        $src = Get-ChildItem $source -Recurse -File -Filter $f -ErrorAction SilentlyContinue | Select-Object -First 1
        if (-not $src) {
            [pscustomobject]@{Vendor=$row.Vendor; File=$f; Target=$m[0].Name; Status="FILE NOT DOWNLOADED"}
        } else {
            $st = "OK"
            if (-not $dryRun) {
                try { Copy-Item $src.FullName -Destination $target -ErrorAction Stop }
                catch { $st = "COPY FAILED: $($_.Exception.Message)" }
            }
            [pscustomobject]@{Vendor=$row.Vendor; File=$f; Target=$m[0].Name; Status=$st}
        }
    }
}

$log | Export-Csv "$env:USERPROFILE\Desktop\upload_log_final.csv" -NoTypeInformation

$rowStatus = $log | Group-Object Vendor, Target | ForEach-Object {
    $done = @($_.Group | Where-Object Status -in "OK","ALREADY THERE").Count
    $miss = @($_.Group | Where-Object Status -notin "OK","ALREADY THERE")
    [pscustomobject]@{
        Vendor       = $_.Group[0].Vendor
        Subfolder    = $_.Group[0].Target
        Expected     = $_.Count
        Present      = $done
        Missing      = $miss.Count
        RowStatus    = if ($miss.Count -eq 0) { "COMPLETE" } elseif ($done -eq 0) { "NONE" } else { "PARTIAL" }
        MissingFiles = ($miss.File -join "; ")
    }
}
$rowStatus | Export-Csv "$env:USERPROFILE\Desktop\row_status_final.csv" -NoTypeInformation

$log | Group-Object Status | Select Name, Count
$rowStatus | Group-Object RowStatus | Select Name, Count

Run it:

powershell

powershell -ExecutionPolicy Bypass -File "$env:USERPROFILE\Desktop\upload_final.ps1"

ALREADY THERE covers last week’s uploads — those get skipped, not re-copied. Run the dry run first and send me the counts; with 717 rows the file scan will take a few minutes since it searches the source tree per file.

I am sharing this code with my colleague so Give instructions on what to change

Here’s a note you can send along with the script.

Before running: three lines to change at the top

powershell

$csv      = "C:\Users\HU346SK\OneDrive - EY\Desktop\mapping_multi.csv"
$source   = "C:\Users\HU346SK\OneDrive - EY\Desktop\Sonnet upload"
$destRoot = "C:\Users\HU346SK\OneDrive - EY\Vitality Acute Program - 17. Outside Counsel Folder Structure"

Replace HU346SK with your own user folder in all three. Easiest way to get the exact paths: open each folder in File Explorer, click the address bar, and copy what’s there.

  • $csv — your mapping CSV (see below)
  • $source — folder holding the Kira exports; the script searches subfolders too, so exports can stay in their own extracted folders
  • $destRoot — the SharePoint library. Add a shortcut to OneDrive from the “17. Outside Counsel Folder Structure” library (Add shortcut to OneDrive), wait for it to sync, then use that local path. Note the folder name gets prefixed with the site name.

Building the CSV

Copy columns C, E, G, I from the Master Agreement Tracker into a new workbook as columns A–D. Headers must be in row 1 (delete any blank row above) and named exactly: Vendor, Master, Docs, RolledUp. Paste as values, not formulas. Save as CSV UTF-8 (Comma delimited).

Running it

Save the script as a .ps1 file via Notepad (Save as type: All Files) rather than pasting into the terminal — pasting can convert quotes to curly quotes, which breaks it. Then in PowerShell:

powershell

powershell -ExecutionPolicy Bypass -File "$env:USERPROFILE\Desktop\upload_final.ps1"

Close the CSV in Excel first, or the read may fail.

Run once with $dryRun = $true (nothing is copied, it just reports), check the counts, then change to $dryRun = $false and run again.

Reading the output

Two files land on your Desktop: upload_log_final.csv (one line per file) and row_status_final.csv (one line per contract family, with expected vs present counts and any missing filenames).

Statuses:

  • OK — copied
  • ALREADY THERE — was already in the subfolder, skipped
  • FILE NOT DOWNLOADED — not found in the source; needs pulling from Kira
  • VENDOR FOLDER NOT FOUND / NO SUBFOLDER MATCH — the destination folder doesn’t exist or the name doesn’t match the tracker; flag to Akshay rather than creating folders
  • COPY FAILED — usually path length over 260 chars; workaround is subst X: "<destRoot>" then set $destRoot = "X:\"

The script never creates or renames folders — it only matches existing ones — so a mismatch always shows as a flag rather than a new folder appearing.

Then verify before running anything:

powershell

$csv = "C:\Users\HU346SK\OneDrive - EY\Desktop\mapping_multi.csv"
$rows = Import-Csv $csv
"Rows: $($rows.Count)"
$rows | Select -First 1 | Format-List

You want four properties named Vendor / Master / Docs / RolledUp, and the RolledUp value showing multiple filenames rather than just one. If you see H1 as a property name again, there’s still a blank row above the headers.

Before the dry run, check the parse count against column G across every row. Paste the function first (type the quotes rather than pasting them):

powershell

function Get-Files($cell) {
    ($cell -split "`n") | ForEach-Object {
        $t = $_.Trim() -replace '^[\u2022\-\*]\s*',''
        if ($t -match '^(.+?\.(pdf|docx?|msg|xlsx?))') { $Matches[1] }
    } | Where-Object { $_ }
}

$rows | ForEach-Object {
    $p = @(Get-Files $_.RolledUp).Count + 1
    if ($p -ne [int]$_.Docs) {
        [pscustomobject]@{Vendor=$_.Vendor; Master=$_.Master; ExcelDocs=$_.Docs; Parsed=$p}
    }
} | Format-Table -AutoSize

Boston Scientific should come back 45. Any row that prints is one where a filename didn’t parse cleanly — those are worth looking at individually rather than assuming the count is close enough.

Worth a quick look at what the missing line is before you do, though, since both being off-by-one suggests a pattern rather than two random glitches:

powershell

$r = $rows | Where-Object Vendor -eq "Allergan"
$r.RolledUp -split "`n" | ForEach-Object { $_.Trim() }

Compare that against what Get-Files returned. If it’s a filename with an unusual extension or a line wrapped oddly in the cell, you’ll know whether to just add it manually to the CSV or whether the same thing could bite elsewhere.

The Intuitive Surgical row is probably the same class of issue. Check it:

powershell

$r2 = $rows | Where-Object Vendor -like "Intuitive*"
$r2.RolledUp -split "`n" | ForEach-Object { $t = $_.Trim(); if ($t -notmatch '\.(pdf|docx?|msg|xlsx?)') { $t } }

That prints only the lines the parser rejected, so you’ll see the malformed one directly instead of scanning 24 filenames.

able with a move — no re-download needed. The structure is vendor → subfolder → file even for single-doc rows, which my earlier script didn’t account for. The files are all sitting one level too high.

This finds each file at vendor level and moves it into the matching subfolder:

$csv      = "C:\Users\HU346SK\OneDrive - EY\Desktop\mapping150.csv"
$destRoot = "C:\Users\HU346SK\OneDrive - EY\Vitality Acute Program - 17. Outside Counsel Folder Structure"
$dryRun   = $true

$fix = foreach ($row in Import-Csv $csv) {
    $vendorPath = Join-Path $destRoot $row.Vendor.Trim()
    $file       = $row.Master.Trim()
    $filePath   = Join-Path $vendorPath $file
    if (-not (Test-Path $filePath)) {
        [pscustomobject]@{Vendor=$row.Vendor; File=$file; Target=""; Status="NOT AT VENDOR LEVEL"}
        continue
    }
    $base = [IO.Path]::GetFileNameWithoutExtension($file)
    $subs = Get-ChildItem $vendorPath -Directory
    $match = @($subs | Where-Object { $_.Name -eq $base -or $_.Name -eq $file })
    if ($match.Count -eq 0) {
        $match = @($subs | Where-Object { $_.Name -like "$base*" })
        if ($match.Count -ne 1) { $match = @() }
    }
    if ($match.Count -eq 0) {
        [pscustomobject]@{Vendor=$row.Vendor; File=$file; Target=""; Status="NO SUBFOLDER MATCH"}
    } else {
        $t = $match[0]
        if (-not $dryRun) { Move-Item $filePath -Destination $t.FullName }
        [pscustomobject]@{Vendor=$row.Vendor; File=$file; Target=$t.Name; Status="MOVED"}
    }
}
$fix | Export-Csv "$env:USERPROFILE\Desktop\move_log_150.csv" -NoTypeInformation
$fix | Group-Object Status | Select Name, Count

Matching goes exact-name-minus-extension first, then a prefix match only when it resolves to exactly one subfolder. Ambiguous or missing cases get flagged as NO SUBFOLDER MATCH and the file stays put rather than being moved somewhere guessed — the Settrax-style subfolders with long timestamp suffixes are where prefix matching earns its keep.

Run with $dryRun = $true first and send me the counts. The Target column in the log lets you eyeball whether the prefix matches picked sensible folders before you commit.

Those are the real-run errors, and the cause is almost certainly path length. Your destRoot is already long, and some subfolders have very long names (the Settrax one with the double timestamp is ~85 characters on its own). Add vendor + filename and you blow past the 260-character Windows limit — which surfaces exactly as “Could not find a part of the path.”

Check the ones that failed:

$fix | Where-Object Status -eq "MOVED" | ForEach-Object {
  $p = Join-Path (Join-Path (Join-Path $destRoot $_.Vendor.Trim()) $_.Target) $_.File
  if (-not (Test-Path $p)) { [pscustomobject]@{File=$_.File; Len=$p.Length} }
} | Sort Len -Descending

Anything with Len near or over 260 confirms it. Note the log says MOVED for these even though the move failed — the status was written regardless of the error, so trust this check over the log.

Fix — .NET’s file API handles long paths via the \\?\ prefix where the cmdlet can’t:

$retry = foreach ($r in $fix | Where-Object Status -eq "MOVED") {
    $vp  = Join-Path $destRoot $r.Vendor.Trim()
    $src = Join-Path $vp $r.File
    $dst = Join-Path (Join-Path $vp $r.Target) $r.File
    if (Test-Path $dst) { continue }
    if (-not (Test-Path $src)) {
        [pscustomobject]@{File=$r.File; Status="SOURCE GONE"}
        continue
    }
    try {
        [System.IO.File]::Move("\\?\$src", "\\?\$dst")
        [pscustomobject]@{File=$r.File; Status="FIXED"}
    } catch {
        [pscustomobject]@{File=$r.File; Status="STILL FAILING: $($_.Exception.Message)"}
    }
}
$retry | Group-Object Status | Select Name, Count

It skips anything already sitting correctly in its subfolder, so it’s safe to run after a partial success.

If files remain stuck after that, the practical fallback is asking Akshay whether those subfolder names can be shortened — the timestamped ones are the problem, and that’s a structure decision rather than something to change unilaterally.

The retry is reporting the source file missing, not the destination — so path length probably isn’t the story. Let’s see the actual paths rather than guess:

$bad = $fix | Where-Object Status -eq "MOVED" | ForEach-Object {
    $vp  = Join-Path $destRoot $_.Vendor.Trim()
    $dst = Join-Path (Join-Path $vp $_.Target) $_.File
    if (-not (Test-Path $dst)) { $_ }
}
$bad | Select -First 3 | ForEach-Object {
    $vp  = Join-Path $destRoot $_.Vendor.Trim()
    $src = Join-Path $vp $_.File
    $dst = Join-Path (Join-Path $vp $_.Target) $_.File
    [pscustomobject]@{
        File    = $_.File
        SrcOK   = Test-Path $src
        DstDir  = Test-Path (Join-Path $vp $_.Target)
        SrcLen  = $src.Length
        DstLen  = $dst.Length
        Target  = $_.Target
    }
} | Format-List

That tells us which of three things it is: the file already left vendor level (SrcOK false — possibly your manual moves), the target subfolder doesn’t exist (DstDir false — bad prefix match), or lengths near 260 (genuinely path length).

Send me that output and I’ll give you the right fix rather than another guess. Worth also checking one of those vendor folders in the SharePoint browser view — seeing where the file actually sits right now is often faster than inferring it.

Yes — they’ll come through as MISSING, with vendor and filename. To list just those:

$verify | Where-Object Status -eq "MISSING" | Format-Table Vendor, File -AutoSize

That’s your download-and-redo list. Once you pull those 3 from Kira into Sonnet upload, you can copy them straight into the right subfolder rather than re-running the whole pipeline — by then you’ll know the exact target folder name from the verify output for neighbouring rows.

Worth cross-checking that list against the original upload_log_150.csv FILE NOT DOWNLOADED rows. If they match, the story is clean end to end: 3 never downloaded, everything else placed. If MISSING shows more than 3, something slipped between the copy and the move, and that’s worth tracing before you call it done.

Good idea — final verification. This checks every CSV row and reports where the file actually sits under its vendor folder:

$verify = foreach ($row in Import-Csv $csv) {
    $vendor = $row.Vendor.Trim()
    $file   = $row.Master.Trim()
    $vp     = Join-Path $destRoot $vendor
    if (-not (Test-Path $vp)) {
        [pscustomobject]@{Vendor=$vendor; File=$file; Location=""; Status="NO VENDOR FOLDER"}
        continue
    }
    $hits = @(Get-ChildItem $vp -Recurse -File -Filter $file -ErrorAction SilentlyContinue)
    if ($hits.Count -eq 0) {
        [pscustomobject]@{Vendor=$vendor; File=$file; Location=""; Status="MISSING"}
    } else {
        $h = $hits[0]
        $inSub = $h.DirectoryName -ne $vp
        [pscustomobject]@{
            Vendor   = $vendor
            File     = $file
            Location = Split-Path $h.DirectoryName -Leaf
            Status   = if ($hits.Count -gt 1) { "DUPLICATE" }
                       elseif ($inSub)        { "IN SUBFOLDER" }
                       else                   { "AT VENDOR LEVEL" }
        }
    }
}
$verify | Export-Csv "$env:USERPROFILE\Desktop\verify_150.csv" -NoTypeInformation
$verify | Group-Object Status | Select Name, Count

What you want to see: everything as IN SUBFOLDER except the 3 that were never downloaded (MISSING).

If any come back AT VENDOR LEVEL, those still need moving. DUPLICATE means the same filename exists in two places under that vendor — worth looking at rather than assuming, since it could be a genuine second copy from a manual move.

The full per-row detail lands in verify_150.csv, which is the artifact to keep alongside the upload log for Akshay.

One note: if subst X: is still active, it doesn’t affect this — $destRoot points at the real path either way. Run subst X: /D to clean up when you’re finished.

If the PowerShell session is still open, $verify is in memory:

$verify | Where-Object Status -in "MISSING","AT VENDOR LEVEL" | Format-Table Vendor, File, Status -AutoSize

If you’ve since closed it, read from the saved file instead:

Import-Csv "$env:USERPROFILE\Desktop\verify_150.csv" |
  Where-Object Status -in "MISSING","AT VENDOR LEVEL" |
  Format-Table Vendor, File, Status -AutoSize

If the list is long enough to be awkward in the terminal, drop it to its own CSV so you can work through it in Excel:

Import-Csv "$env:USERPROFILE\Desktop\verify_150.csv" |
  Where-Object Status -in "MISSING","AT VENDOR LEVEL" |
  Export-Csv "$env:USERPROFILE\Desktop\to_fix.csv" -NoTypeInformation

Worth splitting them once you see the list: MISSING means download from Kira, AT VENDOR LEVEL means the file is already there and just needs moving down a level. Different fixes, and the vendor-level ones are quick.

Leave a comment

Your email address will not be published. Required fields are marked *