Scheduled ODBC Exporting on Steroids.
The Polychrome ODBC Export Tool exports data from any ODBC data source but that’s not all. It does a whole lot more, like emailing the results or calling a command line application and passing the exported data filename. Now you can create CSV extracts from your software and because it can be run from the command line it can be scheduled using the Windows scheduler or if you want more control (like run on last working day of the month) then use our scheduler.
- Scheduled export of data using ODBC
- Export “real world” data scenario, only orders added since last export
- Run post export process for example transfer via FTP using uFTP tool
- 2 styles of output
- Normal exports each row on a new line
- Header exports one header line followed by multiple detail lines
- Email extracted data to someone
- Scheduled export on special accounting days, like last working day of the month, last physical day of the month
The ODBC export tool uses SQL statements saved in a pre-formatted external XML file. The user interface makes creating these ODBC definitions easy. The tool will read the data using ODBC and create a CSV file with each column separated with a comma (or your chosen delimiter) and laid out in the order determined by the SQL statement.
Let’s look at the user interface
In this screen we can see the basic definition.
ODBC Definition Filename
This allows us to choose the name of the ODBC export definition (so that we can reuse these setting later or schedule this to run later).
As we are using ODBC we need to choose an ODBC connection name. I’ll assume for now that you understand how to make a DSN but I will make a post on this and link it later.
The default output style is Normal, this is the format used for Excel where each record needs to be on a new line.
If you want the export to contain a header row that has the field names, be sure to check this box.
Most of the time you will want this to be a comma, if you have special needs this can be overwritten for example if you want the | symbol between each column.
This is the big one! If you want data you MUST define how the database will retrieve the data. If you are not a SQL guru then the easiest way to work out your SQL statement is to design it using Microsoft Query in Excel. Then open the SQL window and copy and paste the SQL text. This is important, if the SQL doesn’t work in something like Excel it won’t work here either so if you are having problems check your statement in Excel. If you have saved a query in Microsoft Query you can edit that in Notepad and copy the SQL statement.
This is the name of the file that will be created when you run the export. We have some nice touches like [DATE] will insert the current date but that’s optional.
You are now ready to export data
That’s enough to be able to Export data, in fact because of the way the program defaults values when you start a new ODBC Definition, All you need to do for most exports is
- Choose File | new ODBC Export Definition
- Choose a name that will be used to save the definition
- Choose an ODBC DSN connection
- Enter some valid SQL Text
- Choose a destination filename
Now to save the definition and export the data now choose File | Export Now.
Want to try it out first?
We understand that you might want to take a test drive so click to download
Like what you see?
This price includes all of the b2b suite modules, CSV Conversion, ODBC Exporting, Scheduling, Scripting and Unattended FTP. Talk to us today and let us show you how you could use your web presence more effectively and improve the value of this vital business resource.
Prices are in NZ dollars and exclude GST.