CombineCSVDDB.ps1

Import-Module PSDuckDB
# Start timing
$startTime = Get-Date

# Parameters
$table = "test_table"
$sql = "SELECT * FROM $table"
$output = "Output\QueryResults.csv"

# Create a connection
$db = New-DuckDBConnection

# Change this to your directory path
$directory = "Data" 

# Get the CSV files in the directory  - optional filter for specific files
$csvFiles = Get-ChildItem -Path $directory -Filter *.csv #| Where-Object { $_.Name -like "*customer*" }

# Build the CSV file paths for the DuckDB query
$csvPaths = $csvFiles.FullName -join "', '"

# Create a view for the table
$db.sql("CREATE OR REPLACE VIEW $table AS SELECT * FROM read_csv_auto(['$csvPaths']);")

# Get a record count
$db.sql("SELECT COUNT(1) AS record_count FROM $table;")

# Delete the file if it exists
if (Test-Path $output) {
    Remove-Item $output -Force
}

# Export the results to a CSV file
$db.sql("COPY ($sql) TO '$output' (HEADER, DELIMITER ',', ENCODING 'UTF8');")

#Close the in-memory database connection
$db.CloseDb()

# Display the duration
$endTime = Get-Date
$duration = $endTime - $startTime

# Notify the user
Write-Host "Results saved to $output. Completed in $($duration.TotalSeconds) seconds." -ForegroundColor Blue