MoneyWorks Manual
Import File Format
The import data may be in one of four different text file formats:
| Format | Details |
|---|---|
| Tab-separated text | This format is common to most applications. Records have a fixed number of variable-length fields with each field separated by a TAB character (ASCII 9), and each record terminated by a Carriage Return character (ASCII 13) or CR/LF. |
| Tab-separated text with field names | This format is the same as Tab-separated text except that the first record in the file contains not data, but the names of the fields present in the file. If you are using this format when importing accounts or budgets, make sure you set the Ignore First Line option in the File Open dialog. |
| Comma-separated values (CSV) | This is a text format in which the fields begin and end with a quote character (ASCII 34), and are separated by a comma (ASCII 44). Records are terminated by a carriage return or CR/LF. e.g. "Field One","2","Field 3". This is not supported when importing accounts or budgets. |
| Merge format (Comma-separated with field names) | Merge format is the same as CSV (above) except that the first record contains not data, but the names of the fields present in the file (and this first record is comma-separated without quotes around the field names). This is not supported when importing accounts or budgets. |
If you are importing transactions from FileMaker Pro, MoneyWorks supports the FileMaker Pro repeating field export format. Each FileMaker repeating field can be mapped onto a field in the transaction details.
When importing transactions, names, products and job sheet information, MoneyWorks allows you to specify which fields will be imported and in what order. This is done by means of the Import Field Order dialog box.
To specify the field order:
- Choose the correct file format from the File Format is pop-up menu
On the left side of the Field Order dialog box is displayed the data from the text file that you chose.The first column contains the field number. You can use this to calculate values derived from the raw data as part of the import.
If the format of the text file is Merge File or Tab Separated with Field Names, then the field names will appear in the Source Field column.
The Record x column displays the actual record data from the import file. If this data does not appear correct—everything is on the first line for example—then you may have selected the wrong file format. You can scan through the data by clicking on the left and right pointing arrows at the top of the Record column. The Record number will change as you scan through the data. Scanning the data in this way will help you to identify the fields if the File Format does not include field names.
The fields within MoneyWorks into which you can import data are listed in the Destination column. The order of these fields can be changed by dragging them up or down the list. The mouse cursor will change shape to indicate that the field can be dragged. When you drag a field and drop it in a new location (opposite the data that you want to be imported into it), it will swap places with the field name that was in that position.
- Modify the field order to map the data into the correct MoneyWorks fields
The data is mapped from the Source to the Destination column wherever there is an arrow in the central column. To activate a field for importing, click on the dotted line in the central column. An arrow appears, indicating that data will be imported into the field. If you do not want to import data into a particular field, click on the arrow to turn it off. A dotted line is displayed in place of the arrow to indicate that no data will be imported into the field.
Fields into which data is not being imported can be filled with calculated data. This is shown in the Use Value column—see Using Calculations in Imports.
- Click Options... to change the importing options
The Options dialog box appears. The options available depend on the file that you are importing into.
MoneyWorks will remember the last used field order and option settings, so the next time you import the field order will default to that previously used. However if you will be importing data from several sources, you may want to save the import setup for later use. The map can be reloaded in subsequent imports by using the Load button.
- To save the import map for reuse, click Save...
The standard file save dialog box is displayed. You should save the map into the Import Maps folder in your MoneyWorks Custom Plug-ins folder.
- If you have a previously saved Field Order and want to use it now, click Load... to load it
- Click OK to continue with the Importing
The Import Progress dialog box appears.
If no errors are found, you can import the data as described previously.