How to Export to CSV from the DOS Shell

A collection of utilities and scripts are collected and attached to this article so you can "learn by doing" how to export to the popular .CSV format from the DOS shell.

Overview

This collection of utilities and scripts are designed to show you how to run a SQL query against an xTuple database from the command line of a Windows/DOS session. This example includes PSQL.EXE and supporting DLL's, but could have easily included pg_dump, pg_restore, and other tools.

The reason for creating this is because BAT scripts do not behave like unix shell scripts, shell scripts make this a very easy task. BAT files are read one line at a time and a multiline query or instruction just doesn't translate in a BAT program, it'll produce an error.

Sometime's clients just want CSV data to play with in Excel, and don't want a fancy OpenRPT report. They just want to click a shortcut icon, enter a date range, and get results.

Intended Uses for this Method

Anything that may require CSV data:

  • FTP/SSH upload or email attach to a vendor who wants a nightly update of your inventory
  • Integration with a remote web database or customer portals — upload the file, and have a script parse it
  • Remote backup with email notification — include pg_dump and search Google for BLAT and PUTTY
  • Combine it with the Windows Scheduler for interesting possibilities

Future

It would be nice to extend this functionality into the OpenRPT renderer to parse out variables from the commandline and output to CSV. Until then, use this method.

How

  1. Download the runqrybat.zip file and extract on your Windows filesystem
  2. Exceute the example rmaqueryfunction.sql against your xTuple database via pgAdmin, or psql
  3. Query creates 1 TYPE and 1 FUNCTION
  4. Edit runqrywin.bat , and set the variables to match your environment
  5. Double click on runqrywin.bat, enter startDate and endDate in YYYY-MM-DD format

Other

Should work on Vista, XP, 2K, etc. The query has been tested to work on 2.3.2 to 3.2.2.

The same method would work if the BAT script is converted to shell script syntax.

Here's a link to the download file:

rungrybat.zip