Configuring DataWorx P3K CSV Export with SQL Server BCP

Skip the Quick Fixes That Don't Hold Up

The request is simple: get Productivity3000 tag data into CSV files that someone can open in the morning. These are the shortcuts people usually try first, and why each one fails.

Get it running, then fix it properly. The method that works in practice is a two-stage chain. DataWorx writes tags into Microsoft SQL Server 2008 R2 Express, the free edition, and the SQL Server command-line utility bcp writes CSV files out of that database.

Understand Why the CSV Comes From the Database

DataWorx P3K moves PLC tag values into database tables. It handles polling, timestamps, and inserts. Its job ends at the table, and producing a file is a separate job. Splitting the two gives you three advantages:

• The database is the record of truth. If a CSV export fails, the data is still in the table and you can re-run the export.
• The export query decides which columns, date window, and sort order go into the file. Changing the file layout never touches the PLC or the logger configuration.
• bcp runs from a command line, so Windows Task Scheduler can run it without an operator and without SQL Server Agent.

bcp in queryout mode runs a SELECT statement and streams the result to a file. -c selects character mode, -t, sets the comma as field terminator, -S names the server and instance, and -T uses Windows authentication. It writes data rows only. It does not add a header row and does not quote text fields. Both limits affect how you build the export.

Check the Chain Before Building the Export

1. Confirm the SQL Server service is running. Open Services and look for SQL Server (SQLEXPRESS). That is the default instance name for an Express install unless someone changed it during setup.
2. Confirm DataWorx is actually inserting rows. In Management Studio, run SELECT COUNT(*) against the logging table twice, a few minutes apart. The count must rise.
3. If DataWorx runs on a different PC than SQL Server, open SQL Server Configuration Manager and confirm TCP/IP is enabled for the instance. Express installs usually leave it off, which blocks remote connections. Also confirm the SQL Server Browser service is running if you connect by instance name.
4. Confirm bcp is on the path. Open a command prompt and run bcp -v. If Windows reports the command is not found, install the SQL Server command-line utilities or call bcp.exe by its full path.
5. Decide which Windows account will run the scheduled task. That account needs a SQL login with read access to the logging database and write access to the export folder.

Stop here if step 2 fails. A CSV export of an empty or stale table only hides the real problem, which is between the PLC, DataWorx, and SQL.

Build the SQL-to-CSV Export

1. Create an export folder on a local drive, for example D:\Exports. Avoid mapped network drives, because they do not exist for a scheduled task running with no user logged on.
2. Write the SELECT first and test it in Management Studio. Restrict it to a fixed window, such as the previous day, so each file is complete and no rows are duplicated.
3. Create a one-line header file, for example D:\Exports\header.csv, containing the column names separated by commas.
4. Create the batch file below. Replace the bracketed placeholders with your database, table, and column names.
5. Run the batch file by hand from a command prompt. Fix any errors before you schedule it.
6. In Task Scheduler, create a daily task that runs the batch file. Select "Run whether user is logged on or not" and use the account you chose earlier.

@echo off
set OUT=D:\Exports
for /f %%d in ('powershell -NoProfile -Command "(Get-Date).AddDays(-1).ToString('yyyyMMdd')"') do set STAMP=%%d

bcp "SELECT <TimeColumn>, <TagColumn>, <ValueColumn> FROM <Database>.dbo.<Table> WHERE <TimeColumn> >= DATEADD(day,-1,CAST(GETDATE() AS date)) AND <TimeColumn> < CAST(GETDATE() AS date) ORDER BY <TimeColumn>" queryout "%OUT%\data.tmp" -c -t, -S .\SQLEXPRESS -T
if errorlevel 1 exit /b 1

copy /b "%OUT%\header.csv" + "%OUT%\data.tmp" "%OUT%\p3k_%STAMP%.csv" >nul
del "%OUT%\data.tmp"

The CAST(GETDATE() AS date) boundary sets the window to midnight to midnight, regardless of when the task fires. The date stamp comes from PowerShell because %date% in a batch file changes format with regional settings. If a text column can contain commas, choose a different delimiter with -t (tab or pipe), or wrap that column in quotes inside the SELECT.

Verify the File Before Anyone Trusts It

1. Run SELECT COUNT(*) with the same WHERE clause. The CSV line count should equal that number plus one for the header.
2. Open the CSV in a plain text editor, not Excel. Check the delimiter, the timestamp format, and that the last line is complete.
3. After the first scheduled run, check Task Scheduler's Last Run Result. means the batch exited cleanly. Any other value means the export failed, so run the batch by hand as that account to see the error.
4. Compare two or three values in the file against live tag values in Productivity Suite at a known time. This proves the tag-to-column mapping in DataWorx.

Watch for the Traps That Break It Later

• Database growth: Express limits the size of each database. Check the limit for your edition, and plan to purge or archive old rows before the table reaches it. When the cap is hit, inserts fail.
• Password changes: If the task account's password changes, the task stops running without any visible alarm. Use a service account with a managed password.
• Clock drift: The date window depends on the SQL Server PC's clock. If that clock drifts from the PLC clock, rows land in the wrong day's file.
• Locked output files: If someone has yesterday's CSV open when the task runs, copy fails. Write to a new file name each run, as the example does.

FAQ