PS Tools 5.5 Help


Overview of Help for Import:

    This help document will help you import . 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 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.
(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, but can be comma, pipe, or just about any other 'standard' delimiter. Sometimes a file can have multiple delimiters. For instance, the User import file has a field for "Authority" that can contain multiple values. Those multiple values within a field must be a different delimiter from the primary delimiter of the file. So if the file is pipe-delimited, then the multiple Authority values in the User import file would be comma-delimited. The recommended and most common choice here is to tab-delimit the file and use commas within any field that allows for multiple values.

    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 OBJECT Name of the thing that will be assigned to a Y 60 Field must match the name of an existing object in the Timesheet database exactly, including capitalization. If in doubt as to which field this is, it is always the first field in the import file for that type of object.
2 TYPE What type of thing are you assigning to a Y 60 Field name of the thing that you are assigning to the on this row. So if the object that you are importing in this row is a , then the ObjectType would simply be "" (without the quotes). This is case sensitive and must be spelled correctly. This is exactly the name of the fields on the Management screen.
3 PROJECT Name of the to which the object will be assigned Y 60 Field must match the name of an existing in the Timesheet database exactly, including capitalization.
3 ACTION Are you going to remove a assignment? N 6 You can choose to delete a by placing the word "REMOVE" in all caps in this field. Any other value will add the .

(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.)

    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.
OBJECT TYPE PROJECT ACTION
Research Project One
Pre-Sales Project One
Customer Meal Project Two
Military Leave Project Two REMOVE

(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 before you import Memberships.)

    These sample files are slightly different than the others, and may require editing before you can import them. The second field in this import is the name of the field into which you are importing each item. But those names can be customized. We can use programming in the help files to automatically replace the default name for each field with the names you have given them. Unfortunately we cannot do that with these sample files. So the sample files have the default names in them. Below is a little list of the fields that can be renamed and the default names versus your name. For any that do not match you will need to replace the default name with your name in the sample file before you can import it. Use Excel or Wordpad or your favorite text editor to do a |File| |Replace All...| for each name you need to change.
      Default Value Your Value
      Project
      Task
      Pay Type
      Bill Type
      Expense Code
      Source
      Currency
      Vehicle
      Measurement
      Reason
      Group

    A standard Import file.

    A Import file containing Removals.

    An Excel template for importing s.
(top)