Run PowerShell from Batch by dragging and dropping files

readme.md

About this script

You can drag-and-drop CSV file on top of the BATCH file. It will use the CSV file as an input

Drag-Drop-CSV-to-Batch.bat

@ECHO OFF
SETLOCAL
cls
@TITLE COVID-19 Test Import

IF "%1"=="" GOTO GETINPUT
REM %~dp0 will expand to "the drive letter and path and folder in which this batch file is located"
REM Example of %~dp0 - "C:\Storage\Current Folder\"

IF EXIST "%~dp0\%1" Set InputFile="%~dp0\%1"
IF EXIST "%1" Set InputFile="%1"

ECHO - %InputFile%

REM powershell.exe -Command Start-Process "powershell.exe" -ArgumentList -Verm RunAs "'-NoExit -ExecutionPolicy Bypass %~dp0\Import-Covid-Tests.ps1 -CsvFilePath %InputFile%'"
powershell.exe -NoExit -ExecutionPolicy Bypass %~dp0\Import-Covid-Tests.ps1 -CsvFilePath %InputFile%

GOTO END

:GETINPUT
powershell.exe -NoExit -ExecutionPolicy Bypass %~dp0\Import-Covid-Tests.ps1

:END

PAUSE

Drag-Drop-CSV-to-Batch.ps1

param (
    [string]$Path,
    [Parameter(Mandatory = $true)]
    [string]$CsvFilePath
)
# PREREQUISITES: Run the following PowerShell command to install the PnP module:
# Install-Module -Name PnP.PowerShell -Scope CurrentUser -Force
$Title = "Test Import"
$host.UI.RawUI.WindowTitle = $Title

$ErrorActionPreference = "Stop"
$appId = "AAA"
$appSecret = "SECRET"
$url = "https://CONTOSO.sharepoint.com/sites/COVID-19Tests"
Connect-PnPOnline -Url $url -ClientId $appId -ClientSecret $appSecret

Clear-Host
Write-Host
Write-Host "================================================================" -ForegroundColor Yellow
Write-Host "             TEST RESULTS IMPORT"                                 -ForegroundColor Yellow
Write-Host "================================================================" -ForegroundColor Yellow
Write-Host

Write-Host "Connected to https://CONTOSO.sharepoint.com" -ForegroundColor Cyan

$list = Get-PnPList -Identity "Covid-19 Tests"
Write-Host Retrieving existing tests from SharePoint. Please wait... -ForegroundColor Yellow
$existingItems = Get-PnPListItem -List $list -PageSize 500
Write-Host Found $existingItems.Count tests in SharePoint -ForegroundColor Yellow
Write-host
$date = Get-Date "1900 January 1"

$CsvFilePath = $CsvFilePath.Replace('"', '')
$rows = Import-Csv -Path $CsvFilePath

Write-host Found $rows.Count tests in $CsvFilePath -ForegroundColor Cyan
Write-host Importing... 
for ($i = 0; $i -lt $rows.Count; $i++) {
    Write-Host $($i + 1) / $rows.Count -ForegroundColor Yellow
    $row = $rows[$i]

    try {
        $Dateofbirth = $null
        $Dateofbirth = Get-Date $row.'Date of birth'
        if ($Dateofbirth -lt $date) {
            $Dateofbirth = $null
        }
    }
    catch {
        $Dateofbirth = $null
    }
    
    try {
        $Testresultdate = $null
        $Testresultdate = Get-Date $row.'Test result date'
        if ($Testresultdate -lt $date) {
            $Testresultdate = $null
        }
    }
    catch {
        $Testresultdate = $null
    }

    try {
        $Samplecollectiondate = $null
        $Samplecollectiondate = Get-Date $row.'Sample collection date'
        if ($Samplecollectiondate -lt $date) {
            $Samplecollectiondate = $null
        }
    }
    catch {
        $Samplecollectiondate = $null
    }

    $Title = $row.'Last name' + " - " + $row.'Report id'

    $matchedRow = $existingItems | Where-Object { $_["FormID"] -eq $row.'Report id' }
    if ($matchedRow) {
        if ($matchedRow.count -gt 1) {
            Write-Host [Skipped] $row.'Report id' already exists. Skipping -ForegroundColor Yellow
        }       
    }

    else {
        $Folder = $row.Site + "/" + $row.'Ordering clinician'
        $supress = Resolve-PnPFolder -SiteRelativePath $("Lists/Covid19 Tests/" + $Folder)
       
        $suppress = Add-PnPListItem -List $list  -Values @{
            Title                  = $Title
            Site                   = $row.'Site'
            FormID                 = $row.'Report id'
            Firstname              = $row.'First name'
            Lastname               = $row.'Last name'
            Dateofbirth            = $Dateofbirth
            Cellphonenumber        = $row.'Phone #'
            OhipNumber             = $row.'OHIP #'
            Email                  = $row.'Email2'
            Samplecollectiondate   = $row.'Sample collection date'
            Testresultdate         = $Testresultdate
            Orderingcliniciansname = $row.'Ordering clinician'
            TeamID                 = $row.'Team ID'
            SampleCollectionType   = $row.'Sample collection type'
            TestType               = $row.'Test type'
            Testresult             = $row.'Test result'
            Pivotree_ID            = $row.'ID'
            Report_ID              = $row.'Report ID'
        } -Folder $Folder 


        Write-Host  Added $row.'Report id' to  $Folder -ForegroundColor Green
    }

}

Write-Host [Success] All tests were imported. You may close this window. -ForegroundColor Green