To import data into a FootPrints Workspace, the
data must be contained in a Comma-separated-Variable
(CSV) format file.
CSV File
The fields in each
record must be separated by a comma (‘,’).
Each record must
end with the system new line separator (i.e., the result of pressing “Enter”,
“Return”, “^M”, etc.)
Fields that span
more than one line or that have internal commas must be enclosed inside
a pair of double-quote (" ") characters. Any
field can optionally be inside a pair of double quotes. For
example:
“This
field, the third, includes two commas.”
Only fields defined
in FootPrints as multi-Line text
fields are permitted to span more than line. For
example, the Assignee
field is not permitted to occupy more than one line.
The following are examples of CSV-formatted data:
this
is the title,this is the description,1,Open,jsmith,Printer
this
is the title,”this includes, a comma”,1,Open,jsmith,Printer
”more
title”,”This description is on multiple lines”,1,Open,jsmith,Printer
Other Features
Empty
fields—All
fields in the Schema must be present in the load file for each Issue.
However, optional fields need not contain data. An empty field should
be indicated by placing nothing between the field delimiters or at the
end of the line. Suppose the previous example has no value for the
optional Platform
field (using the comma-delimited option):
System down,Please reboot machine.,1,Open,jsmith,,network
Notice the two commas between jsmith
(the Assignee)
and network
(the Problem
Type). These represent the Platform
field, for which there is no data in the record.
Mandatory
fields must contain data. Mandatory
fields are indicated in red in the Schema.
Field
Count Test—By
default, each field must be accounted for
each record in the load file. During the data validation step, if any
records do not have the correct number of fields (including optional fields
accounted for by commas or another delimiter as described above), the
load does not continue. There may be a case where one or more of the last
fields in the Schema are optional and there is no data present. There
are no "empty" delimiters to represent those optional fields.
For example, if the load file is created
in Microsoft Excel, the final CSV file created by Excel does not provide
comma characters for null fields at the end of the data line. For this
case, there is an option to skip the Field Count Test. If you select this
option, you can submit a load file without the trailing commas normally
required for optional, empty fields. By skipping this test, there is a
risk that your data may not be loaded properly, so please be careful in
preparing your data.
Note
The choice to skip the Field Count Test is not available
when the Link
records to be loaded to this workspace's Address Book option is selected
because the Address Book key is always the last field in the file, so
all fields must be accounted for.
Here is an example of an acceptable record
if the Field Count Test is skipped, using the Schema above, but assuming
the Problem
type field is optional:
System down,Please reboot machine.,1,Open,jsmith
Notice that the trailing commas for the
two final, optional fields (Platform
and Problem
Type) are missing. When
a CSV file is created by Excel, commas are never included for empty trailing
columns, which are normally required by FootPrints.
By skipping
the Field Count Test, a CSV file created from Excel can be used without
editing it to add the trailing commas.
Different
Submitters for each Ticket—If
you want the individual issues in the load file to have different submitters,
you can do this by starting each line with the submitter’s user ID in
braces, as in [jsmith].
For example:
[jsmith]System
down=Please
reboot machine.=1=Open=jsmith= [jjones]printer broken=Printer #5 is broken=1=Open=joe=
Statuses—Values used
for the Status
field must be valid for the Workspace. Otherwise, they are set to Open.
(FootPrints requires that the status
word, for example, Urgent,
be capitalized. To eliminate a source of problems, the load process capitalizes
the status values found in the load file.) The
status Deleted
is included in all Workspaces, but is treated differently than the others.
It must
be indicated in the Load File as _DELETED_
.
Solutions—Solutions
can be imported, but must be indicated by the following special statuses:
Public
Solution (or _SOLVED_)
Internal
Solution (or _INTERNALSOLVED_)
Assignees—To assign individual
Issues to different people or teams, use the Assignee
field on each line of the text file and insert one or more FootPrints
IDs or full names of agents (spelled exactly as found in the password
file), separated by commas. To
submit an Issue with no assignee, leave the Assignee
field empty. See
example below.
Note
To assign all the Issues
in the text file to the same user(s), the IDs can be entered in a single
place on the Administration
| System | Workspaces page in the Load
External Data into a FootPrints Workspace Database section. If
one or more assignee names are provided on the System Administration page,
any names found in the load file are ignored. A few more notes regarding
Assignees:
Solutions
are not permitted to have Assignees. Any Assignees provided are ignored.
If
more than one ID, full name, or Team name, is supplied as the Assignee,
these IDs must be separated by commas.
If
you are using a CSV (comma separated) data file including issues with
multiple assignees you MUST put the assignee list in a pair or quotes;
otherwise the commas separating the assignees will be interpreted as field
separators. Here is an example of such a line:
this is the title,"this includes,
a comma",1,Open,"Hardware,fhp,scp,jsmith",Printer
To submit an issue with no assignee, leave
the assignee field empty:
this is the title,"this includes,
a comma",1,Open,,Printer
Number
fields—For
Integer or Real Number fields, all values must be numeric.
Date
fields—Data
for Date fields must be in the ISO date format, YYYY-MM-DD,
e.g., 2003-09-22,
even if your Date Format system or user option is set to an alternate
format, such as American or European.
Date/Time
fields—Data
for Date/Time fields must be in the ISO date format, YYYY-MM-DD HH:MM:SS, e.g., 2003-09-22
11:22:35, even if your Date Format system or user option is set
to an alternate format, such as American or European.
Drop-down
Choice Fields—Choice
field data is not verified. It is your responsibility to ensure
that data for such fields matches the list of values you established when
you created the choice field.
Multi-select
Fields—Multiple
values separated by semi-colons can be imported for a multi-select field,
e.g., choice1;choice2;choice3
Comments—You may make
your data file easier to read by using blank lines and comment lines beginning
with a #
sign.
Deleted
fields—An
Address Book may have had certain fields deleted over time. These deleted
fields are not displayed in the Schema; they must
be ignored in the text file.
Checkbox
fields—Input “on” or “off” to import a checkbox field. “on”
populates the checkbox field with a checkmark, while “off” leaves the
checkbox unchecked.
Create
Date / Time—Create Date and Create Time are imported as columns like any other column. The format for the data is YYYY-MM-DD for Create Date and HH:MM:SS for Create Time. Prior to release 8.0, FootPrints used the keywords CDATE and CTIME for this data. These are no longer used.
Close
Date / Time—Close Date and Close Time are imported as columns like any other column. The format for the data is YYYY-MM-DD for Close Date and HH:MM:SS for Close Time. Prior to release 8.0, FootPrints used the keywords CLDATE and CLTIME for this data. These are no longer used.
Multiple Blank Spaces
By default, when a data file is imported, multiple blank
spaces are ignored and only a single blank space is imported. For
example, if a record in your data were as follows:
title,description,1,Open,joe
smith
It would be imported as this:
title,description,1,Open,joe
smith
In other words, "Joe Smith"
becomes "Joe Smith".
To import the blanks exactly as they appear, without
compressing multiple blank spaces, set the $ReplaceBlanks
variable in MRlocaldefs
to "1". If
you do so, imported multi-line data is imported exactly as it is in the
load file.