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 ... $oData | Add-Member NoteProperty List10 $oReader $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.