4.2. Importing data from a CSV file

CSV (comma-separated values) is one of the most widely used formats for storing and exchanging tabular data (text, numbers, dates, etc) in plain text.

Lots of applications (including Fanurio) export their data to CSV so it can be processed externally. But each application structures and formats its data differently so when importing data from somewhere else, there may be some differences that need to be handled. This guide explains how Fanurio can import data from any CSV file and how it can handle such differences.

4.2.1. How import works

Fanurio can import clients, time, expenses and trips from a CSV file. To import them, go to File » Import and select the type of records you want to import. Then select CSV as the source. If the application doesn't have a CSV format that can be used to read records from the CSV file, it will ask you to create one. Once it has a format, it reads the records and displays their count.

A CSV format consists of the following elements:

  • Name: The name of the format. It lets you identify a format if you have more than one.

  • Separator: A character that separates values in the CSV file (usually the comma character).

  • Text Delimiter: A character that indicates the beginning and the end of a value (usually the quote character). Text delimiters are useful when you have values that contain separators. For instance, "one, two, three" is a single value and not three in a comma-separated file because it is delimited (surrounded) by the text delimiter.

  • Record fields: One or more of fields, each with a mapping expression that extracts data from CSV. The fields are specific to each type of record. That's why, the format for importing time has some fields while the format for importing expenses has other fields. Fields whose names start with an asterisk are mandatory and must have a mapping expression.

A format for importing time from CSV

Even if formats have different fields, their mapping expressions are built in the same way. The following examples show how this is done.

4.2.2. Mapping a field to a column

To associate a field with a CSV column, click the columns dropdown box next to it and select a matching column. The dropdown box shows the column names (in black) and the values from the first row of the CSV file (in gray) so you can see what you are importing.

Mapping a field to a column

If you click the Activity column for the Task field, the following expression is generated automatically:

${column("Activity")}

You don't have to learn how to write mapping expressions unless you need to write custom mapping expressions (explained below).

4.2.3. Mapping a field to a column with a specific format

Some columns can't just be mapped to a field if they contain data in a non-standard format because the application won't be able to read it. That's why, fields that require standardized input have a dropdown box that allows you to select the format for the CSV column. The dropdown box contains the standard format on the first position followed by non-standard but frequently used formats.

Mapping a field to a column with a specific format

For instance, if the Date column contains non-standard dates like 20/12/2012, selecting the format dd/MM/yyyy from the formats dropdown box will generate an expression that transforms the non-standard date into a standard one (2012-12-20). For more details on date and time patterns, see this page.

${column("Date")?date("dd/MM/yyyy")}

Besides date fields, there are other types of fields like time and money that require standard input. Their standard format is documented below. Here are some non-standard formats for various types of fields:

  • time durations may be represented in hours (1.5 hours - H.H), in minutes (90 minutes - M), or in hours and minutes (01:30 - H:M),

  • numbers (including monetary amounts and distances) may be represented with different thousands and decimal separators (1,234.50 vs 1.234,50),

  • monetary amounts may use currency symbols ($1,234.50), currency codes (1.234,50 USD) or no currency information (1234.50).

4.2.4. Mapping a field using a custom expression

Sometimes fields can't be mapped to a single column, even if a format is specified. In such cases, the only solution is to use a custom mapping expression. To enter one, just select Custom from the columns dropdown box (the first entry) and then type the mapping expression in the text field.

Fanurio uses Freemarker for the mapping expressions, the same language that it uses for invoice templates. If you have problems creating a format for your CSV file, contact us and we'll create it for you.

4.2.4.1. Fields with missing data (default values)

The CSV file may not have (enough) data for each field, in which case you need to provide default values for them.

Mapping a field to a default value

If a field is optional, you can leave it empty and the default value will be an empty text (this works for fields like Description and Tags). But if a field is mandatory (marked with an asterisk), you must enter the default value as a mapping expression. Here are a few examples.

Example 4.1. Missing task

Some applications save time directly to projects instead of tasks like Fanurio does. In that case, you can't map the Task field to a certain column so you must enter a default name as the mapping expression (eg Activity, Task, Job). All the time entries will be imported to tasks with that name.


Example 4.2. Non-billable records

When you are entering a custom mapping expression for a field that requires standardized input, the formats dropdown is disabled and the standard format is selected. This allows you to see what values are allowed for that field. Examples for each standard format are documented below.

For instance, if all the imported records are non-billable, enter the text false for the Billable field because boolean fields accept only two values: true or false.

false


Example 4.3. Missing currency

For some fields, there may be partial data in the CSV file. For instance, if monetary amounts don't specify the currency and you know it, you can specify it as a fixed text.

${column("Amount")} USD


4.2.4.2. Fields with data in multiple columns (merging columns)

Sometimes, the value for a single field needs to be imported from multiple columns.

Merging data from two columns for a field

For instance, if dates and times are stored in separate columns (Date and Start Time) and you need to import them as a single value, you can use a mapping expression to merge them.

${column("Date")?date("MM/dd/yy")}T${column("Start Time")?time("hh:mm a")}

Contact names represented using two columns (FirstName and LastName) can be imported into a single field (Client).

${column("FirstName")} ${column("LastName")}

Monetary amounts represented using two columns (Amount and Currency) can be imported into a single field (Price).

${column("Amount")} ${column("Currency")}

4.2.4.3. Fields with data in the same column (splitting columns)

Sometimes, a column contains data for multiple fields.

Splitting data from one column for two fields

For instance, if a CSV file has a column called Project that contains both the client name and the project name separated by a minus (-) then the mapping expressions for the Client and Project fields are the following.

${column("Project")?split("-")[0]}
${column("Project")?split("-")[1]}

This is a simple example that uses the ?split built-in but for other data you may need to use more advanced expressions that use built-ins like ?trim, ?replace, or ?contains. See the Freemarker documentation for more details.

4.2.5. Field types

When importing data in Fanurio from a CSV file, the text mapped to a field needs to have a specific format to be valid. If the text doesn't have the right format, the import fails. Fanurio knows the standard format of a field by looking at its type.

Table 4.3. Field types

Type Explanation Standard Format Examples
Text

Text fields don't require text in a specific format, any text is valid.

This type is used by most fields.

   
Date

Date fields require text in the standard format for dates (ISO 8601).

This type is used by fields that need to represent a date (for instance the date of a time entry).

yyyy-MM-dd 2010-01-23
Time

Time fields require text in the standard format for times (ISO 8601).

This type is used by fields that need to represent a time (for instance the start time of a time entry).

HH:mm:ss 13:30:00
DateTime

DateTime fields require text in the standard format for dates with time (ISO 8601).

This type is used by fields that need to represent a date with time (for instance the start time of a trip).

yyyy-MM-ddTHH:mm:ss 2010-01-23T13:30:00
Duration

Duration fields require text in the standard format for durations (ISO 8601).

This type is used by fields that need to represent a duration (for instance the time recorded by a time entry).

PTnHnMnS PT2H30M, PT150M, PT9000S
Boolean

Boolean fields require the text to be either true or false.

This type is used by fields that can have one of two possible values (for instance expenses can be billable or non-billable).

true,false true
Money

Money fields require text that starts with a number followed by a space and then the currency code (ISO 4217). The number represents the amount and has no thousands grouping separator, only a decimal separator.

This type is used by fields that need to represent monetary amounts (for instance the amount of an expense).

####.00 currency_code 1234.50 USD
Distance

Distance fields require text that starts with a number followed by a space and then the distance unit (mi for miles or km for kilometers). The number represents the distance and has no thousands grouping separator, only a decimal separator.

This type is used by fields that need to represent distance in miles or kilometers (for instance the distance of a trip).

####.00 distance_unit 1234.50 km