1
\$\begingroup\$

I am currently using the following query to add a specific row (with 10 columns) from an Excel spreadsheet (~1500 rows and hosted on SharePoint - I've also tested locally with the same issue) to an array in PowerShell.

$connection = New-Object System.Data.OleDb.OleDbConnection

$connectstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$Source;Extended Properties='Excel 12.0 Xml;HDR=YES'"; $connection.ConnectionString = $connectstring

$connection.open()

$cmdObject = New-Object System.Data.OleDb.OleDbCommand

$query = "Select * from [Sheet1$] 
WHERE [Sheet1$].[Test] = '$Test'"
$cmdObject.CommandText = $query
$cmdObject.CommandType = "Text"
$cmdObject.Connection = $connection

$oReader = $cmdObject.ExecuteReader()

[void]$oReader.Read()
    $Global:oData = New-Object PSObject
    $oData | Add-Member NoteProperty List1    $oReader[0]
    ...
    $oData | Add-Member NoteProperty List10   $oReader[9]

$oReader.Close()
$cmdObject.Dispose()
$connection.Close()
$connection.Dispose()

This works absolutely fine, however, it is often quite slow. Is there any way in which I could speed up the query? I can't add the entire Excel sheet into an array, as the data changes throughout the day and is queried regularly.

I've found other questions, such as this one. However, that doesn't seem relevant to this particular issue.

\$\endgroup\$
  • \$\begingroup\$ The dots is supposed to be understood as you truncating 8 lines from you code? Don't do things like that when you need a review. Code you remove makes means you are not including the complete solution and can obfuscate improvements we could make to it. Also I don't see how this code adds data to a spreadsheet \$\endgroup\$ – Matt May 1 '17 at 1:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.