Main menu:
F.A.Q.
Yes you can! To begin an import follow the steps listed below.
1. Data can be imported via a number of file formats including...
It is suggested that you use the Excel spread sheet option, as it is the easiest format to use when data manipulation is required.
2. The Fritz data import procedure is found via the main menu under the heading “Functions | Data Utilities” & is called “Import/ Export”. When first entering this module, select the table to which you want to import your data, then click on the “Import Data To Selected Table” button
Formatting Your Spreadsheet
3. It is helpful to know what data the columns contain, so insert a new row at the top of the list to contain the name of the field that it will be imported to. In the example table below all headings that are all capitals (which are the original fields) will not be imported. All header name fields that have upper and lower case text names will be chosen for the import procedure.
4. The import procedure will not process columns that contain Function statements. Function statements will cause a failure message and the import process will halt causing a fatal error. To recover from this error, close the import utility form and reopen it again. See point 8 for more details.
5. If you are importing a database of jobs or assets then function statements are useful to extract needed data out of a line of general info. Eg: If you have one very long line of text that is up to 255 characters long then it could be imported straight into the “Description” field, however the description would be truncated to 80 characters long as that is the maximum number of characters of text allowed in that field. A better way is to split the line into 2 separate sections using the following commands listed below. An example of the command effect is also included.
Column A contains the original text values. In this example we want column B to contain only the first 16 characters of column A, the rest of the text in column A needs to be saved to column C.
Cell B2 contains the function: =Left(Proper(A1),16) This command says to take the first 16 characters starting from the left hand side in cell A1 and make each word into proper case (capitalise the first character of each word).
Cell C2 contains the function: =mid(A2,17,255) This command says to take the middle characters of cell A2 starting from the 17th character up to the 255th character in cell A1.
Example XL File
| A | B | C | ||
| 1 | Job Description | Description | Details | |
| 2 | Please fi light as it has a broken lamp shade. |
Please Fix Light | as it has a broken lamp shade |
|
| 3 | 1122 3344556677 889900 |
1122 3344556677 | 889900 | Note that each space also counts as one character. |
| 4 | 1111111111122222 3333 |
1111111111122222 | 3333 |
If you have an import file with job description text that is say 150 characters long, place the first 80 characters in the Description field and the rest in the Details field.
Tip!
If all the import data is typed all in upper case (capital letters), you may want to convert it proper or lower case lettering for easier readability. Eg: =Lower(Mid(A1),81,255)) will convert text to be placed into the Details field into lower case.
6. If you only have a list of jobs which contains a Department field you can import the same data into the Contact Table to get a list of your departments. To achieve this, add an extra column to your data import table and call it “DepartmentType”. Set every field value to “True”. When you carry out the import, import the Department Name into the field called "KnownAs". As the department names will be duplicated many times into the data import file, the duplicates will be excluded leaving only a refined list of unique Department names.
7. If required, each “Check Box” field in Fritz needs a True or False setting. Having a column of “True” values, as well as a column of “False” values will enable you to correctly set any Yes/No field within a form.
8. When you have created all your required columns, any columns containing a formula will need to be converted to “hard-coded” text. Note that leaving formulas imbedded in your import spreadsheet will cause the import routine to fail. To successfully convert formula fields into hard-code carry out the following steps…
a) Insert a new column next to the one containing a formula.
b) Highlight the whole column containing the formula by clicking on the letter header of that column, now select “Copy” from the icons.
c) Highlight the new empty column that you recently made.
d) From the Edit menu select “Paste Special” and select the option.
e) A form with a number of Paste options will appear. Select “Values” as the item to be pasted into the column. Both columns will now look the same but the original column containing the formulas can now be deleted.
9. If you have some data that contains information that is similar but not exactly what is required for an import then you can use “If” function statements to correct the data. Eg: You may have some data called “Work Status” that contains status data such as “Done” and “Unfinished”. These will need to be converted to status setting understood by Fritz. Here is an example of how you could convert the data…
| A | B | C | |
| 1 | JobID | Work Status | Status |
| 2 | 501 | Done | Closed - Completed |
| 3 | 502 | Unfinished | Open - Commenced |
| 4 | 503 | DONE | Closed - Completed |
The following formula was used in column C…
=IF(UPPER(B2)="DONE","Closed - Completed","Open - Commenced")
A more comprehensive formula could be used to set multiple status results…
=IF(UPPER(B2)="DONE","Closed - Completed", IF(UPPER(B2)="CANC","Closed - Cancelled", IF(UPPER(B2)="HOLD","Open - On Hold", IF(UPPER(B2)="OPEN","Open - Commenced","Open – To Be Commissioned"))))
Sub-Menu: