PowerShell - SQL tables as input to foreach command to corresponding files - 'System.Data.DataRow'

Question

I am working on an array of files that match the names of tables in our SQL Server database. I want to query a subset of those tables, and use those table names to provide commands to their corresponding files.

Example, there is a customer table in SQL Server, so I have a customer.dat datafile.

I have queried 3 tables from SQL Server, and would like to use them to give commands to their 3 corresponding datafiles, but this is the output I get:

$Tables = Invoke-Sqlcmd -Inputfile $inputfile `
    -ServerInstance $server `
    -Database $Database `
    -OutputAs DataRows

$Tables.ForEach({Write-Host $Tables.Item(0)})

Output:

For some reason, the output for all 3 tables comes out as 'System.Data.Datarow'. How do I get the values from this data?

I eventually want to put the desired output into a string for a dynamic filepath. Something like:

$Tables.ForEach({Get-ChildItem -Path $filepath\$_.dat})

But it gives me an error with a similar problem:

Get-ChildItem : Cannot find path 'filepath\System.Data.DataRow.dat' because it does not exist.

You see, it is inserting 'System.Data.DataRow' into the file name instead of the intended table name. Any help, or overview of the concepts I am attempting to use would be greatly appreciated!

Thank you

If you have a collection of DataRow instances then $Tables.ForEach({Write-Host $Tables.Item(0)}) should just be $Tables.ForEach({ $_ }) to enumerate each row and $Tables.ForEach({ Get-ChildItem -Path "$filepath\$($_.dat)" }) for the other part, assming .dat is an actual column — Santiago Squarzon
– Santiago Squarzon, Commented Nov 1, 2022 at 20:44
Thank you for your response, the Get-ChildItem command you provided gives me the entire directory of files. I need only the 3 files corresponding with the 3 SQL tables defined in $Tables — Clifford Piehl
– Clifford Piehl, Commented Nov 1, 2022 at 20:52
This only output the implied ToString() result of the first DataRow. If you changed $Tables.Item(0) to $_ you would see the same text, but at least then it would be the implied ToString() result of each row. — Joel Coehoorn
– Joel Coehoorn, Commented Nov 1, 2022 at 21:01

Clifford Piehl · Accepted Answer · 2022-11-01 20:58:03Z

1

Thank you Santiago for helping with with some subtle adjustments with the syntax. I was able to adjust it further with the following:

$Tables.ForEach({Get-ChildItem -Path "$filepath\$($_.item(0)).dat"})

The addition of .item(0) let the table values appear into the command.

Output:

answered Nov 1, 2022 at 20:58

Clifford Piehl

5451 gold badge4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PowerShell - SQL tables as input to foreach command to corresponding files - 'System.Data.DataRow'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related