D – Export Data

Export Data #

Introduction #

ATMS has an easy to use and powerful export routine that is accessible from the System Management menu.

Access may be granted to users without giving access to the general management and administration functions – see User Profiles.

ATMS can export delimited ASCII files, which can be easily imported into most standard Windows applications (including MS Excel, Access, Lotus products, etc.).

The export process #

From the System management menu, select the “Export Data” icon.

From the drop-down menu, select Report – Full Table Output.

You are now presented with a list of the tables used within the ATMS relational database.

Selection of any one of these, loads the relevant table data to the form, from where it can be viewed, printed directly, or exported.

Exporting to a text file #

To export, select Report – Export…

Do not select the “Spreadsheet” button, but specify your choice of field delimiter.

(The delimiter is the character used to separate the fields in the data to be exported. Hence it is important to select a character that is unlikely to occur in the data itself.)

The default “TAB” character is usually adequate.

The default condition is to export the field headings, and it is recommended you leave this set.

After pressing the proceed icon, you are then prompted for a file name and location.

Your data has now been exported, and can be imported into various applications.

This export method will only export data from one table at any one time.

Exporting direct to Excel #

To export, select Report – Export…

Select the “Spreadsheet” button, and your choice of field delimiter.

(The delimiter is the character used to separate the fields in the data to be exported. Hence it is important to select a character that is unlikely to occur in the data itself.)

The default “TAB” character is usually adequate.

The default condition is to export the field headings, and it is recommended you leave this set.

The last two options are to determine whether after the export, you want Excel to open automatically with your data loaded, and whether you want ATMS to close itself after the export activity.

After pressing the proceed icon, you are then prompted for a file name and location.

Your data has now been exported, and what happens next depends on your selections above.

This export method will only export data from one table at any one time.

SQL Enquiry #

From the ATMS export menu select Report – SQL

You are now presented with a screen in which to type an SQL query, the ATMS SQL script editor screen.

If you are familiar with SQL (Structured Query Language), then all you need to understand is where ATMS stores the data you need.

TIP:
Use the Export menu Report – Tables option to find the right tables, or from the SQL script editor screen, the menu Fields, displays all field names accessible in the database.

Type in a query and press the proceed icon.

Data is populated into the screen behind.

When you are satisfied with results, you can cancel the SQL screen.

TIP:
You can save queries to disk for later retrieval and usage for common queries. From the menu, select File, Save as, and specify the file name and location, etc.

When you have data in the view screen, and have cancelled the script editor window, you can export the data to many file formats.

Select REPORT, EXPORT, and to import directly into MS Excel and open Excel with the data loaded, tick the open file option, and enter a file name and location, and press the disk SAVE icon.

The data is exported and loaded directly into Excel for you. It can now be manipulated and presented in any format you desire.

SQL Examples

“Select * from transactionlog”

Will retrieve all records from the ATMS transaction log table.

“SELECT Item.Item_number, Item.Description_Of_Item, Item.CreatedBy, Item.Min_Stock FROM Item WHERE [min_stock]<=1”

Displays only the fields in the select statement, and the “where” is the selection criteria, from the Item table.

TIP:
As the Issues and Returns report wizards ask the user to confirm that their selected options are correct prior to viewing, if the users “Double Clicks” in the list view (text on white background) the wizard will reveal the full SQL query it is about to execute. You may find this a useful learning aid.