Text agent

A text agent can be used as a source and can serve as a destination for:

  • Acumatica GL
  • Excel
  • MySQL
  • ODBC/ODBC32
  • Sage Intacct
  • SQL Server
  • Text

When defining a text agent connection, you now have the option to use wildcard notation for your file search in the source. This means a single task can now load multiple files.

  • Note that this option is only for use when the destination is a MySQL table.
  • All the retrieved files are appended to the MySQL table.
  • In the MySQL table, the name of each source file will be written under an additional column titled SourceFileName.
  • The Job Log shows the status of each file that the task attempts to load; any loading failure can be traced back to the specific file.
  • If the search finds no matching files, the Job Log lists the task as a success and the Log panel states No file was found meeting the wildcard criteria.

Add a text agent connection

To add a connection for a text agent:

  1. In the navigation panel, select Admin Setup.
  2. Select a text agent or peer connection, and click > Add Connection.
  3. Connection Name must be unique. Up to 40 characters.

  4. Files using wildcards: Turn on to have text agent search for files using wildcard notation in File name below.

    The wildcard notation is as follows:

    • ? (Question mark) – Use to represent a single character.

      • Example: fy201?.txt finds both fy2018.txt and fy2019.txt.

    • * (Asterisk) – Use to represent multiple characters.

      • Example: new*.txt finds both newyork.txt and newjersey.txt.

  5. File name:
    • Up to 250 characters
    • Do not use:
      • Drive letters (example: C:, D:)
      • Placeholders (example: %TEMP%)
      • Move Up (..)
    • Permitted:
      • The file name without a location (example: textfile.csv)
      • The file name with a location (example: dir1\pl.xlsx)
      • The file name with a network path (\\testing\abc\text.txt)
       Note:

      The destination for text files is determined as follows:

      • If you don't specify a location, the destination file is saved to the temporary folder at:

        C:\Program Files\Prophix\DataIntegration\ProphixTemp\<site-id>

      • If you specify a location (example: dir1\pl.xlsx) the file is saved at:

        C:\Program Files\Prophix\DataIntegration\ProphixTemp\<site-id>\dir1\pl.xlsx.

     Best Practice:  Set up a shared folder on a network drive that the controller can access.

  6. Column names in first row: Turned on by default. Use this option if the first row of your data file contains column headers.
  7. Delimiter: The options are: TAB , ^ # + - | : ; / (TAB is the default).
  8. Click OK.

Delete the source files

In a job definition, when using a text files as a Source, you can optionally have the source files automatically deleted after the file transfer.

  1. In the Job Definitions page, select a job.
  2. Select or add a task.
  3. In Source, select a Text agent connection.
  4. Turn on Delete source file(s) after transfer.
  5. In File path, add your source.
  6. Click Save.

Export data from FP&A or FP&A Plus (hosted deployment)

If you are using a hosted deployment and want to export data from Financial Planning & Analysis or FP&A Plus, we recommend using either Report Binder or, if you want an Excel file, downloading the data from a data view or template.

These approaches are needed because, for security purposes, direct access to the file folder structure on the Cloud DIS server is forbidden. For this reason, the Excel and Text agents cannot be used to export data.

Using double quotes

Text Agent supports the parsing of special characters. However, in order to avoid confusing the parser, you need to be careful when using the double quote character (").

 Tip:   A potential solution is to use two single quotes (') to represnets a double quote.

Valid entries

In these cases, the records are valid, with single and double quotes appropriately handled:

  • name 1,name 2,name 3, name 4

  • name 1,name 2,name 3', name 4

  • "name 1","name 2","name 3'","name 4"

Invalid entries

These entries are invalid due to improper usage of quotes or a mismatched number of quotes.

  • name 1, name 2, name "3", name 4

  • "name 1","name 2",""name 3"","name 4"