title: How to compare data from SQL to the one in a directory using PowerShell 5.1?
body: <p>The two columns in the database <code>MAFN</code> and <code>Version</code> which when concatenated with a dot &quot;<code>.</code>&quot; have the (almost the)same format as the folder names in the directory.
  The data in col. <code>MAFN</code> is <code>123</code>and <code>Version</code> is <code>1</code>
  The folders are named like:
  001234.001,
  000789.011
  etc.
  I was able to remove the leading zeros from the directory folder names.
  I was also able to concatenate the data from the two columns.</p>
  <p>I want to compare concatenated items from the query to the (new)folder names in the directory and if they match I want to do something.</p>
  <p>The question is how do I compare the two, the concatenated data from SQL and the folder names.</p>
  <p>I have tried storing the data from SQL in a variable but it doesn't work. Let's say the data is stored is <code>$sqlData</code> then <code>Write-Host $sqlData</code> gives the following output
  <code>System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow System.Data.DataRow Syste m.Data.DataRow</code></p>
  <p>The code is:</p>
  <pre><code> #SQL connection and concat columns
   Invoke-Sqlcmd -ServerInstance &quot;Sidney9.sidney.nii&quot; -Database &quot;NML_Sidney&quot; -Query &quot;SELECT  TOP (10) MAFN,Version,(Convert(varchar(50),MAFN)+'.'+Convert(varchar(50),Version))
  FROM [NML_Sidney].[dbo].[vNML_MAFN_CNCP_ByPartResource]&quot;
  
  #Split, trim, and join folder names to match concated data from SQL
  $aidLibPath = &quot;C:\Users\userName\Desktop\CNC_Transfer_Test_Folders\AidLib_Test\*&quot;
  Get-Item -Path $aidLibPath | ForEach-Object{
  $splitFileName = $_.Name.Split('.')
  $trimSplitFileName = $splitFileName.trimstart(&quot;0&quot;)-join(&quot;.&quot;)
  write-host $trimSplitFileName
  }
  </code></pre>
  <p>Basically, how do I store the data in a variable and loop over them to compare them?</p>
  <p>I am using MSSQL 2005 and PowerShell 5.1 with SQL Server and SQLPS modules loaded.</p>
  <p>Any help is appreciated.</p>