Program options
These options apply to every export while all other settings are per export.
Date format
The date used when substituting dates in file names.
The output date format is used whenever you use the [DATE] function. Adding the text [DATE] to the destination filename will make the program insert the current date. This is handy if you export daily or weekly and dont want to overwrite the file from the last export.
SMTP Email Configuration
It is possible to export data and then email it to one or more people. In order to do that though, the ODBC tool needs login information to be able to access your SMTP server. Your network administrator will be able to help you with this. If you are not running your own email server but are just using the email from your ISP then these settings will match those used in your email software.
Using the advanced tab isn’t so hard
If we break it down
Generic options
The output date format is used whenever a field with the data type is exported. It will write the date to your CSV or text file in this format.
Post Process Options
If you want to call a program after the export for example our unattended FTP file transfer tool then this is weher you define the EXE name and any switches required.
Use the [DestFileName] keyword to pass the exported data file name.
Header Style Options
Sometimes you want to export a header line followed by one or more detail lines. Optionally you want the program to chop each new record into a seperate file. This can be accomodated but is worthy of it’s own posting.
Export Variables
The ODBC export tool support the use of variables, these take a bit of explaining but it’s worth the read. In some scenarios it is much tidier to replace static values with variables. A great example would be where you want to reuse a SQL statement many times substituting one or more values in each case. You might be exporting 5 different stock categories on a regular basis. You might have a SQL statement such as
SELECT * from Stock WHERE Category = ‘WHEELS’
Then you would copy and save the statement replacing the category “WHEELS” with “HUBS” and so on. It is MUCH tidier and easier to maintain if we have a statement
SELECT * from Stock WHERE Category = [WhichCategory]
Now in this example I will admit that it is such a simple statement that it isn’t worth the effort but in a very complex statement it is nice to reuse the statements without risking breaking the statement when you add a new category. When the ODBC Export runs it will load the SQL Statement and then replace the keyword [WhichCategory] with a variable from the ODBC definition if one exists. Don’t worry about where you want to use the SQL Syntax of using square brackets around field names that are not valid like if they contain a space or are a SQL Keyword. The ODBC Export tool only replaces Variables defined in the variables table so just ensure that any variables you add are unique.
Updating variables
This is a very clever feature of the ODBC Export tool and was one of the primary reasons it was originally developed. Imagine the following scenario,
- You want to export purchases to another company.
- You only want to send newly added Purchases orders
The only way to achieve this is to know the last Purchase order already exported. So in the ODBC Export tool you can store the value in a column back into a variable, just check the “SaveVariableValue” check box and specify the column that you want to store. Also we need to decide when variables will be updated. This is a column that is specified in the field above the variables grid labelled Update vars on change of column. Now in your SQL Statement you can get the variable value to use in your where clause for example
SELECT * from Purchases WHERE PONumber > [LastPONumber]
Then in the variables settings we would have
- Variable Name = LastPONumber
- Variable Type = Value
- VariableValue = 0 (to start it is zero but each time it processes a record it will update this number)
- VariableColumnName=PONumber
- SaveVariableValue=True
Note: = Variables are case sensitive
Scenario Example
- Monday export where Last PO is greater than 0 it exports 14-10
- Tuesday export where Last PO is greater than 10 it exports 11-15
- Wednesday export where Last PO is greater than 15 it exports 15-18
You can preview your data
Before scheduling your data export it makes sense to preview the data and ensure that the SQL Statement and connection are working.
Polychrome Scheduler
This is a tray based application that processes schedule files. The schedule files can be repeating and you have control over how often they are repeated etc. Each schedule can process multiple steps and each step can have multiple switches so that is can run most applications including the tools that form the Polychrome data Tools suite.
A convenient tray icon menu allows access to settings and allows you to pause the schedule or create a new schedule.
Settings
The schedule files are located based on settings in the application configuration screen
Attache Scheduler settings
Creating a schedule
The Add a Schedule screen lets you open an existing schedule to amend it or use it as a template. When you save a new schedule to the schedule folder then it is seen by the scheduler and it is added to the work list.
Each step needs an action type an execution item and optionally, each step can have switches
In this example we have
- Get a file from the FTP server
- Get a file from the FTP server
- Get a file from the FTP server
- Use KFI Maker to convert the stock to a KFI file
- Export new Purchases to send to head office
- Take a backup
- Copy a KFI file for unatteded KFI import
This example shows how the interaction with a web server to get new orders might work. Transforming them to KFI format and passing this to unattended KFI. Then how we would export purchases for head office and send them via FTP. Last but not least we take a backup by running a shortcut.
The schedule can be set to repeat a given number of minutes hours days etc. but it can also repeat
- EndOfMonth
Picks the last physical day of the month regardless of day.
- LastWorkingDayOfMonth
Picks the last working day regardless of what day of the week for example, the last working day may fall on a Monday.
- LastFridayOfMonth
Picks the Last Friday of the month. The last actual day of the month could be the following Tuesday.
Schedule definition
ActionType
The type of action being performed.
- EXEC – An executable or program
- SCRIPT – A Windows Script file, ATscheduler will pass the file specified in the Action to the Windows scripting host. The Windows script engine is very powerful.
- SHORTCUT – Will run a windows shortcut file. This is particularly useful for running Attaché Archive.
For more info about scripting see http://en.wikipedia.org/wiki/Windows_Script_Host
Exec
Tells the scheduler the name of the file to run.
Switches
The Scheduler can process multiple switches for each step. There is no limit on the number of switches other than those imposed by the operating system.
The scheduler handles the [DATE] keyword in switches. It will replace the keyword [DATE] with today’s date using the format defined in settings.
You could dynamically use the scheduler for example to get a file via FTP and then convert it to KFI using the KFIMaker and the copy it to the unattended KFI folder.
The big down side to ODBC is the time we spend extracting data. If you need a simple low cost solution to data extracts then look no further our ODBC Export tool can run from the command line. So what? Well that means you can schedule it using Windows scheduler or you can add our scheduler and do smart stuff like export on the last working day or the last friday every month.
http://odbcexport.com/products/polychrome-scheduler/
Look at the scheduler product page to learn more.
Our ODBC Export tools allow you easily, scheduled access to your data. Transformation of CSV for analysis in Excel and much more…
ODBC is a great tool for getting data from our business systems into something like Excel so that we can analyse it. One of the problems with ODBC is that we all spend half our lives running those exports. That’s the problem I am addressing with my new tool that allows ODBC exporting on the command line.
http://odbcexport.com/products/automate-odbc-export/
Create a recurring export from your database
And you can build a history of your data. Our scheduler allows you to date/time stamp your export filenames so that over time you can build a library of data extracts.
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.
Key features
- Scheduled data export using ODBC to CSV
- 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 people
- 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 preformatted 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.
Export made easy
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.
Because the data export can now be called from the command line you can use the Windows scheduler to export your data overnight. Or you can step up to the power of our included scheduler.
Scheduled data export
We understand the real world, that why we don’t lock you into physical days. Most people want to export data on the last day of the month or the last Friday not every Friday, that won’t help when the month ends on a Thursday!
Email that data
This seems so obvious yet it’s so hard to do. You want to export some data and then email it to one or more people. With our tools we make this is a trivial process that we handle out of the box.
Other ODBC resources at odbcexport.com
The various parts to the Polychrome B2B suite
- Defining an Export - set up a ODBC export definition and then you can reuse it later
- Previewing the data - preview the data to test it works before you schedule
- Advanced options – want to send your extracted data to a web site via FTP? All the clever stuff is here
- Exporter options - set and forget, the application setting control things like how to send emails
