Export Activity to a CSV File

Overview

Argos can automatically produce flat files, such as comma-separated (CSV) or tab-separated files, on a regular schedule. Such files are often used as input sources to easily enter expense data into an accounting system. You can set up as many different exports as you like, on various schedules, in any format you need. Anything beyond very basic needs will often require a knowledge of SQL, however.

To establish a flat-file export:

  1. In Argos Manager, Reports & Data section, click on the Allocated tab and select Scheduled Export to File...                                                                                                                                 Untitled.png
  2. In the Activity Export window, click Create                                                                                                                                                                                        
  3. In the Scheduled Activity Export window, enter a name for the task (e.g. "Monthly accounting export"). Click the ellipse (...) button next to the Export folder: field and browse to the location where the exported file will be saved. Select the type of delimited file from the Export format: drop down list.                                                                                                                                                                             
  4. To export all available fields, select the Export mode: radio button next to Typical. Click the Reset button to set the start date for the job record data to be exported. In the Reset Activity Export window, click the Reset export starting on: drop down list to adjust the start date. Click OK.                                                                            
  5. To export a custom set of fields with further options to perform calculations and rename columns, select the Export mode: radio button next to Custom. Click the Edit SQL... button to make changes to the export query.                                                                                                                                                  
  6. In the Custom SQL Query window, edit the SQL select statement to manipulate the job record data to be exported. Sample export templates for Deltek Vision and Deltek Ajera can be found at the links at the bottom of this article. Click the Preview button to view a sample set of data. Click OK to save the changes.                                                                                                                                                                                            
  7.  In the Schedule Activity Export window, with Export mode: Custom selected, select the desired date range for the job record data from the Date filtering: drop down list. In the Advanced options: section, check the boxes next to any required file formatting options. Enter any adjustments to the file name into the File prefix: or File suffix: fields as needed.                                         
  8. Select the local Argos Integration server (if necessary for remote offices). Check the box next to Enabled to set the schedule for the export task.                                                                    
  9. In the Set Schedule window, select the frequency and time for the export to run. Click OK to save the changes.                                                                                                                                                                                                                                                                                                      
  10. In the Scheduled Activity Export window, click OK to save the task.                                                                           
  11. In the Activity Export window, click the Run button to execute the export immediately. To change the task settings, click Edit. To remove the task completely, click Delete.                                                       

 

Typical export mode

Simple mode exports all the columns in the Argos system. Simple mode exports use all data from a specified start date to present.


Custom export mode

Unlike Simple mode, Advanced mode offers more control over the data, including abilities such as:

  • Specify exactly which columns are exported, and in what order
  • Sort, group, and tally your data using SQL
  • Apply date ranges to your exports
  • Change the format of data (e.g. format dates in a specific way)

Advanced mode tasks are based on custom SQL queries. You may use any SQL SELECT query code you wish, including GROUP, ORDER BY and other modifiers. In order to prevent data corruption or abuse, the following operations are not allowed within the custom SQL export:

  • INSERT
  • UPDATE
  • DELETE
  • TRUNCATE TABLE
  • EXECUTE
  • EXEC
  • FETCH
  • CREATE
  • ALTER
  • DROP

Refer to the Argos reporting view column reference for a full list of fields that can be used in your SQL queries

WHERE clauses and date ranges

When you specify a date range, the Argos does the following:

  • If your query does not already contain a WHERE clause, it appends one that contains the necessary criteria to honor your current date range settings at the time the export runs.
  • If your query does contain a WHERE clause, it adds an AND statement, followed by the necessary criteria to honor your current date range settings at the time the export runs.
  • If your date range is set to "No filter," Argos performs neither of the above two actions

If the above mechanism does not suit the particular query you are trying to write, you may write your own WHERE criteria using the following replacement tokens:

  • <%STARTDATELOCAL%> - the start date/time of your current date range at run time, expressed in local time as determined by the server on which the export task runs
  • <%ENDDATELOCAL%> - the end date/time of your current date range at run time, expressed in local time as determined by the server on which the export task runs
  • <%STARTDATEUTC%> - the start date of your current date range at run time, expressed in UTC (Greenwich Mean Time)
  • <%ENDDATEUTC%> - the end date of your current date range at run time, expressed in UTC (Greenwich Mean Time)

Sample export queries

Below are links to sample export queries for some common accounting packages. They can be used as a starting point for customizing the export to match the requirements of the accounting package configuration.

Deltek Vision

*"Invalid Text File Name" error when importing CSV file into Deltek

Deltek Ajera

Clearview InFocus

SQL Date Formatting

BillQuick

The ArgReports view contains both Allocated and Recycle Bin activity. To display only Allocated data, set IsAllocated = 1.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.