How to get column entries in a list

Question

I have 2 PowerShell scripts that I want to combine, I have this for creating data dumps

---

    $query = "use [ISTABLocalDB]
    SELECT  
        Item.[ID] as PartIdDB
        ,Item.[ItemNumber] as Varenummer

        ,ImageFile.[ResourceFile_ID] as ImageID
        ,ImageFile.[Description] as ImageName
            , CASE WHEN ImageFile.[ResourceFile_ID] is null 
            THEN ''
            ELSE 
                CONCAT('F:\App\ISTAB.Data\pictures_global\P\', 
                SUBSTRING(CONVERT(varchar, Item.[ID]), 1, 3), '\', 
                SUBSTRING(CONVERT(varchar, Item.[ID]), 4, 3), '\', 
                SUBSTRING(CONVERT(varchar, Item.[ID]), 7, 3), '\', 
                ImageFile.[ResourceFile_ID],'-g')
            END as PathOnDrive
        ,Item.[ItemType_ID]"

   $extractFile = "$path $date.csv"


$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $query
$command.Connection = $connection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

$DataSet.Tables[0] | Export-Csv $extractFile  -NoTypeInformation

and this one for copying images, where I manually paste into the path that I get from PathOnDrive in the request, in $imagesList

$targetFolderName = "C:\test"
$sourceFolderName = "F:\App\ISTAB.Data\pictures_global\P"

$imagesList = (
"F:\App\ISTAB.Data\pictures\P\122\338\7\1326647",
"F:\App\ISTAB.Data\pictures\P\179\924\0\1678117"
)

foreach ($itemToCopy in $imagesList)
{
    $targetPathAndFile =  $itemToCopy.Replace( $sourceFolderName , $targetFolderName )
    $targetfolder = Split-Path $targetPathAndFile -Parent

    if (!(Test-Path $targetfolder -PathType Container)) {
        New-Item -Path $targetfolder -ItemType Directory -Force
    }

    Copy-Item -Path $itemToCopy -Destination   $targetPathAndFile 
}

---

so my question is how to automatically get all the entries from the PathOnDrive column in my $imagesList

Answer 1

I get it

foreach ($Columns in $DataSet.Tables[0].Rows) {       
      $imagesList = "$($Columns.PathOnDrive)"
      write-Output "$($imagesList)"


$targetFolderName = "D:\DataFeed\Pictures\Parts"
$sourceFolderName = "F:\App\ISTAB.Data\pictures_global\P"


foreach ($itemToCopy in $imagesList)
{
    $targetPathAndFile =  $itemToCopy.Replace( $sourceFolderName , $targetFolderName )
    $targetfolder = Split-Path $targetPathAndFile -Parent

    if (!(Test-Path $targetfolder -PathType Container)) {
        New-Item -Path $targetfolder -ItemType Directory -Force
    }

    Copy-Item -Path $itemToCopy -Destination   $targetPathAndFile 
}
}