top of page

Data Extraction and SQL using PowerShell

May 27
3 min read

When a Python environment is unavailable, PowerShell is a viable alternative for data extraction. I wanted to understand the differences between extracting data by piping a CSV file and using SQL, so I conducted a comparative test. Note: Execution times are omitted, as generating 100,000+ test data points for comparison was not feasible.











With a pipe. Age 30 or older.

PS C:\Users\T> import-csv "C:\tmp\test.csv" | Where-Object{$_.age -gt 30}


Execution result

Name Age Gender

---- --- ------

Taka 47 M

Uji 35 M

 

SQL


PS C:\Users\T> $path = "C:\tmp\test.xlsx"


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


# Modify the Extended Properties value to 'Excel 12.0;HDR=YES'

$conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$path;Extended Properties='Excel 12.0;HDR=YES';"


$conn.Open()

$query = "SELECT * FROM [Sheet1$] WHERE Age > 30"


$cmd = $conn.CreateCommand()

$cmd.CommandText = $query


$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $cmd

$table = New-Object System.Data.DataTable


$adapter.Fill($table) | Out-Null


$conn.Close()


$table


Execution result

Name Age Gender

---- --- ------

Taka 47 M

Uji 35 M

 

 

Out-Null

  • "I don't need the return value, so I'll discard it."


Through this experiment, I realized how cumbersome the implementation can be. However, the choice between piping and SQL clearly depends on the volume of data. There is a distinct threshold: pipes are suitable for a few thousand rows, while SQL is better for tens of thousands or more. Conceptually, the difference lies in the approach: the former involves sequential scanning of all entries, whereas the latter leverages indexing for efficient data retrieval.


The following is what AI said

The crucial difference between the two methods


1. How memory is consumed (in-memory vs. delegation to processes)

  • Pipe (Import-Csv):

    This process reads all rows of the specified CSV file into memory at once, converts them into an array of objects, and then filters each row individually. When the data volume reaches hundreds of thousands of rows, the PowerShell process alone consumes gigabytes of memory, and in the worst case, it will stop due to insufficient memory (OOM).

  • SQL (OLEDB):

    By having the database engine (ACE OLEDB) handle the data filtering (processing of the WHERE clause) instead of PowerShell, only the results that match the conditions (a few to a few hundred records, etc.) are returned to PowerShell, dramatically reducing the memory consumption of PowerShell itself.


2. Indexing and Full Scan (Complete Scan)

  • Pipe (Import-Csv):

    It always performs a "full scan" (scanning all records). If there are 100,000 records, the condition check will be performed 100,000 times.

  • SQL (OLEDB):

    When connecting to Excel files via OLEDB, explicit indexes (indexes) like those used in typical relational databases (such as SQL Server) are not available. However, because the engine optimizes scanning only the necessary column data internally and optimizes memory transfers, it operates faster with large amounts of data than Import-Csv, which performs pure text analysis.


Guidelines for actual coexistence

As Taka-san anticipated, there is a clear division of roles based on the amount of data (number of rows).

method

Guideline for appropriate data volume

merit

Disadvantages

Pipe (CSV)

Several thousand lines to 10,000 lines

* Can be written in one line and is intuitive


* No restrictions from external drivers or environment (32-bit/64-bit).

* Large amounts of data consume a lot of memory.


* Speed decreases

SQL (OLEDB/Excel)

Tens of thousands of lines to over 100,000 lines

* Good memory efficiency


* You can smartly retrieve only the data that has been filtered according to the specified conditions.

* The code is redundant and cumbersome.


* Depends on the bit version of Office (x86/x64).


Note: We are using an older environment, Excel 2007, so we have set the connection settings above (Excel 12.0). However, when handling .xlsx files in newer environments such as Office 365, specify Excel 12.0 XML as follows (a space is required before each property).

PowerShell

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

*Depending on your environment, the provider name may be Microsoft.ACE.OLEDB.16.0.


Furthermore, if the Excel version on your PC is 32-bit, you will need to run PowerShell using a 32-bit version as well (such as Windows PowerShell (x86) or the 32-bit version of PowerShell ISE). Running it from a 64-bit version of PowerShell will result in an error message stating "Provider not registered," so please be aware of this.

 
 
 

Comments


bottom of page