# ERP Data Extract Script Generator (PowerShell to CSV)

> Source: https://netray.co/tools/erp-data-extract-powershell-csv-script-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Creates a scheduled-task-ready PowerShell script that runs a read-only SQL query and writes a timestamped CSV with a simple log.

## Category

developer

## Audience

- ERP administrator
- ERP developer
- Data analyst

## Tool

**Kind:** template

**Output:** code

**Language:** powershell

**Fields:**

#### SQL Server instance

**Key:** server

**Type:** text

**Default:** ERPSQL01\PROD

#### Database

**Key:** db

**Type:** text

**Default:** ERP_PROD

#### SQL query (single line)

**Key:** query

**Type:** textarea

**Default:** SELECT item, description, qty_on_hand FROM item_onhand WHERE qty_on_hand > 0

#### Output folder

**Key:** outdir

**Type:** text

**Default:** D:\Exports\ERP

#### File name prefix

**Key:** prefix

**Type:** text

**Default:** item_onhand

#### Query timeout (seconds)

**Key:** timeout

**Type:** number

**Default:** 300

#### Days of files to keep

**Key:** keep

**Type:** number

**Default:** 30

**Blocks:**

#### Extract script

**Body:** # ERP extract to CSV. Uses Windows authentication and a read-only login.
$ErrorActionPreference = 'Stop'
$Server  = '{{server}}'
$Db      = '{{db}}'
$Query   = '{{query}}'
$OutDir  = '{{outdir}}'
$Prefix  = '{{prefix}}'
$Timeout = {{timeout}}
$Keep    = {{keep}}

$Stamp   = Get-Date -Format 'yyyyMMdd_HHmmss'
$OutFile = Join-Path $OutDir ($Prefix + '_' + $Stamp + '.csv')
$LogFile = Join-Path $OutDir 'extract.log'
if (-not (Test-Path $OutDir)) { New-Item -ItemType Directory -Path $OutDir | Out-Null }

function Write-Log($msg) {
    Add-Content -Path $LogFile -Value ((Get-Date -Format 's') + ' ' + $msg)
}

try {
    $connStr = 'Server=' + $Server + ';Database=' + $Db + ';Integrated Security=True;ApplicationIntent=ReadOnly;Connect Timeout=30'
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $Query
    $cmd.CommandTimeout = $Timeout
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
    $table = New-Object System.Data.DataTable
    [void]$adapter.Fill($table)
    $conn.Close()

    $table | Export-Csv -Path $OutFile -NoTypeInformation -Encoding UTF8
    Write-Log ('OK rows=' + $table.Rows.Count + ' file=' + $OutFile)
}
catch {
    Write-Log ('ERROR ' + $_.Exception.Message)
    exit 1
}

# Remove old exports
Get-ChildItem -Path $OutDir -Filter ($Prefix + '_*.csv') |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$Keep) } |
    Remove-Item -Force

#### Schedule it with Task Scheduler

**Body:** schtasks /Create /TN "ERP Extract {{prefix}}" /SC DAILY /ST 06:00 /RU SYSTEM /TR "powershell.exe -NoProfile -ExecutionPolicy Bypass -File D:\Scripts\extract_{{prefix}}.ps1"

**Notes:** Run under a service account with SELECT only on the needed views. System.Data.SqlClient ships with Windows PowerShell 5.1; on PowerShell 7 use Microsoft.Data.SqlClient or the SqlServer module. Oracle, DB2 and MySQL ERPs need their own provider and connection string. Keep passwords out of scripts; prefer Windows authentication or a credential store. Test the query by hand first.

## Content

**Intro:** Moving ERP data to a CSV for Excel, Power BI or a finance close is one of the most common admin jobs. This generator produces a PowerShell script with error handling, logging and clean-up, so it can run unattended.

**Sections:**

#### How to use the script

**Body:** Enter your SQL Server instance, database and a single query, plus an output folder and file prefix. Copy the script into a .ps1 file, run it once manually and open the CSV to check headers and row counts. Then create the scheduled task using the second block. The script writes a timestamped file each run, appends OK or ERROR to a log, and deletes exports older than your retention window. It requests a read-only connection intent, which helps when your ERP uses an availability group.

#### Why a DataTable and Export-Csv

**Body:** Loading the result into a DataTable and piping it to Export-Csv handles quoting, commas inside text and embedded quotes for you. The -NoTypeInformation switch removes the extra type line that older PowerShell versions add. UTF8 encoding keeps accented part descriptions readable; if Excel on a legacy machine shows odd characters, open the file through Data, From Text with UTF-8 selected. For very large extracts of millions of rows, stream with a SqlDataReader instead of filling a DataTable to avoid memory pressure.

**Bullets:**

- Use views or SELECT only logins, never admin accounts
- Add a row limit while testing
- Check the log after the first scheduled run
- Keep exports on a protected share

#### Reading the log and handling failures

**Body:** A healthy run logs the row count and file path. A zero row count on a day that should have data points to a filter or date problem, not a script problem, so consider adding a check that fails when rows are zero. A timeout error means the query needs an index or a narrower filter, so raise the timeout only after you have looked at the query plan. Failed logins usually mean the scheduled task account lacks access that your own account has.

#### Common mistakes

**Body:** Embedding SQL passwords in the script is the biggest one, followed by running extracts against the live transaction database during peak posting. Multi-line queries pasted into a single-quoted string break on apostrophes, so double any single quotes inside the query. Exporting personal or export-controlled data to an open share can break your own security policy, so treat the output folder as sensitive.

**Cta:** If you maintain dozens of these scripts, we can help move them to a governed, read-only data layer with auditing and approvals, on-premises if needed.

**Faq:**

#### Can I use Invoke-Sqlcmd instead?

**Answer:** Yes, if the SqlServer module is installed, Invoke-Sqlcmd piped to Export-Csv is shorter. This script uses System.Data.SqlClient so it works on a stock Windows server with no module install, which is useful on locked-down ERP hosts.

#### How do I export from Oracle or DB2 based ERPs?

**Answer:** Use the matching .NET provider, for example Oracle.ManagedDataAccess or the IBM DB2 provider, and its connection string. The DataTable and Export-Csv part stays the same. Check the query dialect, as date and string functions differ.

#### Why does Excel drop leading zeros from my part numbers?

**Answer:** Excel converts numeric-looking text when opening a CSV directly. Import with Data, From Text, set the column to Text, or prefix values in the query. The CSV file itself keeps the zeros.

#### Is it safe to schedule as SYSTEM?

**Answer:** SYSTEM maps to the machine account for Windows authentication, which usually has no database rights. Use a dedicated service account with SELECT on the needed objects and grant it Log on as a batch job.

## Search terms

powershell sql query to csv, export sql server to csv powershell, erp data extract script, scheduled csv export sql server, powershell invoke sqlcmd export csv, powershell sql to csv task scheduler, automate erp report to csv, powershell sqlclient export

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json