PS Tools 5.5 Help


Overview of Help for Import:

    This help document will help you import (s).  Sometimes there is more than one way to accomplish your task (for instance, you can import assignments in the import file or in a separate Membership file.)  Therefore you should examine all of the import file specifications before you begin building your import file.

    This document (and the attached sample files) should tell you everything that you need to know about importing files.  You should start by testing these in a non-production environment.  The sample files that are attached can all be imported with no modifications.  And then you can edit them and re-import them to verify that you are able to edit and build importable files.  Finally, you should always backupdb your Timesheet database before you begin importing files, especially into your production environment.

    The Scheduled Process is an advanced option that will allow for an automatic import on a recurring basis.  You should not enable this until you have manually imported representative files several times, in order to verify that the system which is producing the files is doing so correctly.  Remember that there is no Preview occurring in the Scheduled Imports, and so bad data can creep into the Timesheet system if not thoroughly tested.

Help Contents:

Usage Instructions

    This is the general method that Journyx recommends you follow when you are using the Import Tool.

1. Logged in as an Admin, on the main Admin page click the link for Professional Services.

2. On the Professional Services page, under the Import heading, select the import that you would like to run and press the button.

3. The screen will refresh to the Import page.  On this page you should click the button and browse your system to find the file that you want to import.  When you have found the correct file press the button.

4. The screen will refresh with your import file displayed in a grid on the screen.  You should examine the file thoroughly to make sure that it is complete and correct.

5. If the file is correct, then click the Admin button in the button bar, and on the main Admin page click the link for Professional Services.

6. On the Professional Services page, under the Import heading, select the import that you would like to run and press the button.

7. The screen will refresh to the Import page.  On this page you should click the button and browse your system to find the file that you want to import.  When you have found the correct file press the button.

8. The screen will refresh to a results screen with Messages, Warnings, and Errors.  The messages are simply confirmations.  There are not normally any warnings.  Errors will list each record that did not get imported along with the reason.  Generally those reasons are things like “the Employee Number in the import file could not be found”.

9. If you received error messages you can correct those errors in the Timesheet application (adding or correcting Employee Numbers, for instance) or in the import file.  You can "chop down" the file to only the previously-rejected records when you re-import it, or you can simply re-import the entire file.
(top)

Advanced Usage Instructions

    Most of the time you will be using the button in the Import a file that is located on your computer section and importing a file from your computer.  When you are ready to begin importing a file automatically, through the scheduled process, then you need to use the Import a file that is located on the server section to test that the Timesheet application can read files from this location.  You might also use this section if your import files are on the server.

    When you want to upload a file that is on the server, the Timesheet application itself will be going to get that file.  This is different from the browser upload, because in the browser upload option it is really you who is reading the file.  This is an important distinction for network security.  For the server import (and the scheduled import as well) to work, then the Timesheet application must have the correct permissions to read from the location where your file(s) will be stored.  This is why this document indicates that paths to your file that do not contain a drive letter will not work, because the Timesheet application will almost never have permission to read from that location.

    If your Timesheet installation is on a Windows server, then it will be the Anonymous IIS account that is trying to read the file for import.  The Anonymous IIS account usually does not have read access to most directories, either on or off the server.  In that case you will undoubtedly need to get assistance from your Network Administrator to set that up.  They will probably suggest changing whatever program that is writing the files to have them written into a folder that the Anonymous IIS user already has permission to read in.  This is a great suggestion, and Journyx recommends the \Journyx\jwt\ps directory for this purpose because this location will get backed up with Timesheet backups.

    If your Timesheet installation is on a Unix server, then it will be the account that installed the Timesheet application which will be trying to read the files for import.  Your unix server administrator should be able to grant the proper read permissions with no difficulty.

    Other than the issue of the Timesheet application's user account having the proper rights to read the file, there are no special issues for the Import a file that is located on the server section of the Import Tool.  The and buttons work in exactly the same way as they do in the Import a file that is located on the server section of the Import tool.

    This section is rarely used, except as a test during the transition to enabling Scheduled Imports.  The Set a Schedule to regularly import a file that is located on the server section's File Location field works in exactly the same way as the Import a file that is located on the server section's field.  That makes this section an ideal testbed before you begin scheduling your import(s).
(top)

Scheduled Process Instructions

    The Schedules Process is an advanced option that will allow for an automatic import on a recurring basis.  You should not enable this until you have manually imported representative files several times, in order to verify that the system which is producing the files is doing so correctly.  Remember that there is no Preview occurring in the Scheduled Imports, and so bad data can creep into the Timesheet system if not thoroughly tested.

    You cannot establish more than one schedule for a specific import, but you can have different imports running at different scheduled times.  When you enable the Schedule for an import you will see that those values will persist on the import page, so you can view and edit the schedule later.

    You will need to start the process by setting the "Is Schedule Import enabled?" to "Yes".

    Then you will need to decide if you want the automatic import to run one time per day, or many times per day.  If you want it to run one time per day, then you will select the radio button next to the option for "Set a schedule that runs once per day" and then set the options in that section.  If you want the automatic process to run multiple times per day then you will need to select the radio button next to "Set a schedule that runs many times per day" and set the options in that section.

    If you set the automatic process to "Set a schedule that runs once per day" then you will need to specify a time of day for the import to run.  These two fields are in military time, so 0:00 corresponds to midnight and 17:30 corresponds to 5:30 in the afternoon.  The time is based upon the time (and time zone) of the server.  You will also need to select the days of the week that the automatic process occurs on.  If you do not select any of the days of the week you will receive an error.  You can select any number of days.

    If you set the automatic process to "Set a schedule that runs many times per day" then you will simply set the number of hours and minutes between imports.  Of course an interval like 6 hours or 4 hours will be easier to predict because it will occur at the same times every day.  If you choose an odd interval, like 7 hours and 15 minutes, then you will have trouble predicting exactly what time the import will occur next.  With the multiple imports per day you cannot choose to skip some days, the import will occur on every day.

    If the import file is missing, or cannot be read, or another error occurs, then it will be noted in the debug.log file.  No other notification will occur.  The import schedule will continue on at the next scheduled time.

    The File Location field is looking for a server-file.  So you must supply a direct path to the file, like "c:\importfiles\filename.txt".  You cannot use relative path names that reference computer names, like "\bobscomputer\importfiles\filename.txt".  This means that you may have to map a drive from your Timesheet application server.

    In order to make the schedule active you must press the or button.  Obviously the only difference between these buttons is that the second one will perform an import right now.

(top)

File Format

    The file that you generate for import must be a simple ASCII flat-file. It must be a delimited-type of file, not fixed-width. The delimiter should be tab. Contact Journyx support if you need to use another delimiter. Sometimes a field can have multiple values, which must be comma-delimited. For instance, the Groups field can contain multiple values. Those multiple values within a field must be comma-delimited, and the entire field must be surrounded by double-quotes. So if you only have a single value in a Groups field then just enter that value and don't worry about it. But if you have multiple values for that field then separate the multiple values by commas and put double-quotes around the field. See the sample files for examples of this.

    This table lists each column that you can put into a import file.  Your file will not have to contain all of these columns.  The "Header" is the name of the field, and is its header in the import file.  The "Req." field indicates whether or not the column is required ("Y" = required).  If a column is required, then every record (row of the file) must have a valid value in that field.  The "Size" field contains the maximum number of characters that are supported within this field.  The "#" field is displayed in this chart for ease of reading only, your import file will not have numbers like this.
# Header Description Req. Size Notes
1 PrettyName Recognizable Name of the Y 28 Must be unique.  Must be first field.  Only required field in modification imports.
2 Description Full Description of the Y 60 Sometimes overloaded with abbreviations or codes for payroll or billing exports.
3 AutoAdd Is this automatically added to new s? N 1 Must be a capital Y for AutoAdd.  Capital N or blank or any other value is not AutoAdd.
4 Visibility Loggable, reportable only, or hidden N 4 The acceptable values are "LR" (without the quotes) for Loggable and Reportable, "RO" for Reportable Only, and "hide" for Hidden.  Please see explanation note below for more details on the interactions between these fields in the import.
5 Groups What s should the be added to? N none Name of each that the should be added to.  Comma-delimit multiple names in the field.  This field can have strange interactions with Field #4, "Visibility".  Please see explanation note below for more details on the interactions between these fields in the import.

Notes on the fields "Visibility", and "Groups".

Just like in the Timesheet application itself, hiding a will remove it from all s.  If the was previously hidden (defined as not being a member of any ) and gets set to either LR or RO, then it is added to the All only (no other s).

The Visibility column is handled before the Groups column. This means that if you set a to "hide" and then go ahead and specify some s, first it will be hidden, and then it will be added to the specified s, which effectively unhides it.  (Though it will still show up as "RO" on the s screen.)  The Import Results screen will say " Foo is hidden" and then it will say " Foo added to Bar".  
(top)

Notes on Illegal Characters

    There are many characters that you should generally not include in your import file.  These characters can cause problems in displaying HTML in the web interface, and a few of them can cause database-level errors that can be difficult to recover from.  All extended-ASCII characters, like à, ü, and ñ are forbidden.

    Journyx recommends that you build your files in Excel, just because it is so easy to read and use.  If you do use Excel then save it off as a tab-separated-text file, then Excel will add double-quotes around each field that contains a comma.  Journyx has found that remembering to remove these double-quotes is difficult, so Journyx has enhanced the import tools to automatically strip double-quote characters from every field, if-and-only-if the double-quotes are the first and last characters in a field.  Therefore, in the unlikely event that you want a field to contain and initial and ending double-quote then you will simply have to double your double-quotes, like this: ""doubleed double-quotes"" will result in "single double-quotes" being imported.  Generally both double-quotes and commas should be avoided except in fields that specify them (like a users Full Name or the Groups field.)

    There is one more odd situation involving commas. In the fields that support multiple values comma-delimited (Groups and Authority), if your Group or Authority name has a comma in it then you are in trouble. Because you end up with something like this, "G1, G2, G,3, G4" (where the name of the third group really contains a comma). In those situations double the comma in the name, like this, "G1, G2, G,,3, G4". The import tool will see and recognize the doubled comma and treat it as a single comma that is part of the name and not as a delimiter. If you have a better suggestion for how to handle this Journyx welcomes your suggestions at support@journyx.com.

    In HTML it is possible to generate extended ASCII characters by using codes.  So, if you view the source-code of this page you will see that many characters in the following chart were produced using codes like this: &# 38 to create an &.  But & and # are both not-recommended characters.  The fact is that they will often import, but sometimes they will display as the character you wish to display and sometimes they will display as the code itself (&# 38).  This is simply a limitation of the cgi script-based nature of the Timesheet application.

    All of these characters should be avoided, unless specifically allowed in the Notes on a particular field:
: ; & % ' # @ ~ {tab} ÷ \
< > / ¢ £ ¥ ™ © ® ¡ ¿
µ ¦ § ¬ ± ¾ ½ ¼    
    If you must have a “character” in the file/field, then these are the characters that Journyx recommends:
- _ * !

(top)

Sample in browser

    This is generally what your import file will look like.  This one is formatted in an HTML table for easy reading.  See the downloadable sample file, below, for a more precise file to model yours after.
PrettyName Description AutoAdd Visibility Groups
Barter Trading products for services Y LR "Sales, Marketing"
Credits with hosting company Better than gold N LR Support
Republic credits They're no good out here RO N "Development, Support"

(top)

Link to external sample text file

    Right-click on any of these links and select to Save (or 'Save as...') the file onto your local computer.  These files are all tab-separated text files.  All of the sample files are valid and ready to be imported, recognizing that some of the imports will need to occur in a specific sequence (like importing (s) before you import Memberships.)

    A standard Import file.

    A minimum Import file.

    An Excel file with the template for importing s.
(top)