0

I'm trying to query a database, get a value, and then use that value with a string with Export-Csv. However, while I can see that my variable is being populated when I'm trying to concat with strings it changes to System.Data.DataRow

Example Code:

$Server = "TestServer"
$Database = "msdb"
$ID = 1

while ([int]$ID -le 4) {
    $DatabaseNameQuery = "select Name from Sys.Databases Where Database_ID = $ID"
    $DatabaseName = Invoke-Sqlcmd -Query $DatabaseNameQuery -Database $Database -ServerInstance $Server

    $FilePath = "C:\$DatabaseName.csv"
    $DatabaseName
    $FilePath

    #Invoke-Sqlcmd -Query $ResultsQuery -Database $Database -ServerInstance $Server | Export-Csv $FilePath
    $ID++
}

My actual code will be doing other stuff, getting data back and writing it to the files, I'm just struggling in dynamically naming the files.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
pix1985
  • 208
  • 1
  • 8
  • Asimple fix would be to use "string" + $Variable + "string" , notice that the variable is outside the quotes. – Shamus Berube Mar 27 '19 at 17:10
  • 4
    You should use `$filepath = "c:\$($DatabaseName.name).csv"` to set your `$FilePath` variable. The reason is because $DatabaseName is an object with a property called Name. That property contains the value you want. The `$()` syntax evaluates what is inside of the parentheses. – AdminOfThings Mar 27 '19 at 17:14
  • your problem is the result of how PoSh expands $Vars in strings. [*grin*] for a nifty overview of the problem & some solutions, you may want to read this ... Powershell: Everything you wanted to know about variable substitution in strings — https://powershellexplained.com/2017-01-13-powershell-variable-substitution-in-strings/ – Lee_Dailey Mar 27 '19 at 17:25
  • FTR: you're using interpolation in your code (`"C:\$DatabaseName.csv"`), not concatenation (`"C:\" + $DatabaseName + ".csv"`). – Ansgar Wiechers Mar 27 '19 at 17:36
  • To add to @AnsgarWiechers comment: Powershell thinks your looking for a variable of $($DatabaseName.csv). If you make clear the variable name as "$($database).csv" you should be successful. –  Mar 27 '19 at 20:36
  • @Jean-ClaudeD. No. `"C:\$DatabaseName.csv"` is not equivalent to `"C:\$($DatabaseName.csv)"`. If anything they should do what AdminOfThings already suggested. – Ansgar Wiechers Mar 27 '19 at 22:59
  • @AnsgarWiechers - I didn't saying anything about equivalent. I said that PS looked at the variable as being "database.csv" instead of just "database". That by adding the $() around it, it made clear to PS that it was not "database.csv", but just "database". The danger of course is that it will evaluate $($database) and look for a .csv object within it. Again, it seems you jump to comment before reading as I didn't disagree with you, and tried only to better explain what you had said since not everyone will understand the term "interpolation". –  Mar 28 '19 at 12:33
  • @AdminOfThings This has done the trick thanks! – pix1985 Mar 28 '19 at 13:19
  • @Jean-ClaudeD. And I told you that PowerShell doesn't do that. Not when you put the variable in a string. `"x$var.y"` is the same as `"x$($var).y"`, not `"x$($var.y)"`. Dot-access (or index access for that matter) does not work in strings unless you're using subexpressions. A dot also does not become part of a variable name (inside or outside strings) unless you put the variable name in curly brackets (`${var.ia.ble}`). I'm not making this up. – Ansgar Wiechers Mar 28 '19 at 16:34

0 Answers0