Q: It has been a while since we used the Export Processor to Export Data from Elliott. I remember how to select the category and choose the search data, but I can't remember how to select all the fields that need to be processed for export. Can you please provide step-by-step instructions on how to pull data using the processor? Thank you!
A: The export processor will allow you to export certain Elliott table data in an easily organized format to a CSV format, which you can bring up in an Excel spreadsheet. There are other more powerful report writers like "Crystal Reports" that you may also consider. They are licensed separately and have a high learning curve. On the other hand, the Elliott Export Processor's pre-join tables are easy to use. In addition, attribute data are pre-linked so the learning curve is quite low. It is a "simple report writer" that's bundled with Elliott.
To use the Elliott Export Processor to retrieve your desirable data, you should follow the steps below:
- Choosing Category
- Choosing Selection Criteria
- Get Records
- Choose Columns and Export Data to CSV file
- Save Your Template for Re-Use
- Automate Your Export Processor by Using Batch Command
To use the Export Processor, you need to determine which category of data you need to access. It is important to choose the right category to get the data you need. Choosing the wrong category may result in duplicating data.
The category listed here refers to the lowest level (one record per category). For example, in the Customer Category, we include both “Customer” and “Salesman,” and they are linked automatically. You will get one record per customer. You can also access Salesman data through the “Customer” category. But if you only want one salesman per record, then this is the wrong category. You should use the “Salesman” category instead.
Using the same principle, you can access Order Line Items, Order Headers, Items, Customer, and Salesman in the “Sales Order Line Items” category. If your intention is to have one line item per record, then this is the right category. But if your intention is to have one order per record, then it is the wrong category; you should choose “Sales Order Headers” instead. Refer to the screen display shown below.
Choosing Selection CriteriaIn this example, we choose the "Customer" category. The next issue you should consider is whether or not you want to export all customer records. If so, then what should your filter criteria be? In the "Selection" tab, your job is to determine which customer records to retrieve. For example, you may decide that you only want customers in California. If that is the case, you should select the field "cus_st," which is our internal table column name for "Customer State." Then use the "EQ" operator to select the value "CA." See sample screens below:
You may impose additional filter criteria. You can have up to 10 filter criteria and, by default, they use the logical "AND" operator. Therefore, the following examples will retrieve all customers in "CA" and the customer discount percent is greater than or equal to 3.00%. See sample screen below:
If you need to choose multiple states, you can separate the Values with a space, and the system will interpret that as "or." For example, if you wish to select all customers in CA or AZ, then enter it as shown in the following example:
You can also use "*" for wildcard. The following is an example of how to retrieve a customer name that contains "CENTURY" since the value begins and ends with "*". You can begin a match with "CENTURY*" or end a match with "*CENTURY" as well:
If you try to compare a name value that includes a space, make sure to include it with quotation marks so the system does not interpret the space as an "OR." See example below:
Choose Columns and Export Data to CSV File
If the above result is what you want, you should next decide what columns to export. There are many columns in the customer table. You probably are only interested in a certain number of columns. So in the "Export" tab you should find the columns you want on the left-hand pane, then drag and drop them to the right-hand pane.
The Export Processor will export your selected data to a CSV file format. The column heading by default will be "Table.Column_Name." So for customer number, it will be "arcusfil.cus_no". If you don't like this format, you can right click on the columns on the right side pane and choose "Change Heading" to something more friendly like "Cust#." See sample screen below:
Once you have chosen all your columns, you can click "Export" to create the CSV file. It is important to know that the file name extension is ".CSV." Once the file is exported, you can click on the "Launch" button to bring it up in your spreadsheet. See sample screen below:
Save Your Template for Re-Use
Since it takes some time for you to construct this query to export your data, you should consider saving your template for future use. See sample screens below:
Advanced Subject: Between, Multiple Values and Wildcard
To use the "BT" between operator, you simply have to provide two values with a space in between. See sample screen shown below.
To use the Multiple Values Selection, you simply use the "EQ" operator with multiple values separated with a space. See sample screen below.
To use the Wildcard, you simply use the "*" symbol. See sample below:
Advance Subject: Select Attributes
One the key benefits of using the Export Processor, in addition to exporting data, is that it integrates with Elliott attributes and makes attributes easy to use as a selection criterion.
Typically, there are two types of attributes:
- Attribute only
- Attribute with Additional Data
Automate Your Export Processor by Using Batch Command
The Export Processor can be further automated by using command parameters. To find out all the parameters supported by the Export Processor, you can go to the command prompt. From your Elliott root folder (V7.x), use the following command:
M:\Elliott7> EL700ME /?
With Elliott V8.x, you will go to the Elliott Bin folder and use the following command:
M:\Elliott7\bin> EL800ME /?
Then you should see a Help screen that explains the possible parameters, such as the following:
The greatest benefit of doing this is that you can then create an icon on your desktop. For example, the following sample command:
m:\macola\el700me /co:01 -e -t:1 -ol
means launch the Export Processor and choose "company 1, " use template 1, run it, export to the file and launch it. The end result is when you double click on this icon, the icon will do exactly this without any human interaction and will bring up the final result in your Excel spreadsheet.