Invoke-ASCmd with Export-Csv returns rows, but no column data

PowerShell Help
1

Attempting to execute this function. It pulls the dataset names from a PowerBI Workspace. The function completes, and returns the correct number of rows but each with a single comma.

The command must be correct because 76 rows are returned which corresponds to what is returned in DAX Studio. What is causing the blank columns?

function Get-BackendDataTest{
param(
	\[string\]$server,
	\[string\]$Loc
)
$sys = @'
$SYSTEM
'@
$qry = $qry = "<Statement>SELECT '" +$env+ "' as \[Database\_ID\], \[Catalog\_Name\] from " +$sys+ ".DBSCHEMA\_CATALOGS</Statement>"
Write-Host "server = "$server
Write-Host "Loc = " $Loc
Set-Location -Path $Loc
\#EXPORT THE DBSCHEMA\_CATALOGS
Write-Host "Exporting DBSchemaCatalogs..."
Write-Host "qryCatalogs = " $qry
$data = \[xml\] (Invoke-ASCmd -Server $server -Query $qry)
$data.return.root.row | select Database\_ID, Catalog\_Name | Export-Csv -Path .\\Catalogs.csv -NoTypeInformation
}

Top 3 lines of Catalogs.csv:
“Database_ID”,“Catalog_Name”
,
,

2

Is this code formatted properly? \[string\]$server isn’t valid PS as far as I know. I see it happening several more times. I feel like something may have gotten goofed up in a copy/paste perhaps? Obviously sounds like your code works so I’m guessing it must’ve just copied/pasted weird or something.

Also to confirm the count of $data.return.root.row is 76, which matches what you see in dax studio? I presume you see the same blank rows in the $Data.return.root.row as well before the export? I’d think with the two rows the count would be off by 2. In any case, I’d be curious what it the data looks like before you export. Wonder if it’s something funky with how that query is ran and getting translated when it gets back the XML. Haven’t messed with that cmdlet or running dax queries myself. Haven’t used PowerBI much so not sure how much help i’m gonna be but figured asking a few questions might get the ball rolling.

3

Thank you for your suggestions. The commands were simplified and I can finally see the data (in XML format)! It would appear that the original syntax is incorrect and I need to find alternative syntax to translate the XML to CSV.

$data = Invoke-ASCmd -Server $server -Query $qry   
Write-Host $data
4

It turns out that the XML renamed to columns to C0, C1, C2. Would anyone know how to prevent that?
<xsd:complexType name=“row”>
xsd:sequence
<xsd:element sql:field=“Env” name=“C0” type=“xsd:string” minOccurs=“0” />
<xsd:element sql:field=“Database_ID” name=“C1” type=“xsd:string” minOccurs=“0” />
<xsd:element sql:field=“Catalog_Name” name=“C2” type=“xsd:string” minOccurs=“0” />
</xsd:sequence>
</xsd:complexType>

5

You could just use PowerShell and calculated properties to name them back appropriately if it consistently does that: about Calculated Properties - PowerShell | Microsoft Learn

In essence, when you do a Select-Object (there’s a few commands you can do it with actually) you provide a ‘property’ you calculate to rename it. Quick Example of using calculated properties to rename a column:

Get-Childitem | Select-Object @{Name = 'RealName'; Expression = {$_.Name}}

This just says, I want to name a column ‘RealName’ but the value will be whatever was in the name column.

so your example would probably, look something like this for a single property (just add a comma to do more)

$Data | Select-Object @{Name = 'Env'; Expression = {$_.C0}}

The other option is actually just creating a PowerShell object (or collection of objects) that appear precisely how you want them to based on your data.

6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.