Workbooks Import Wizard
Do you want to assign records direct to a queue? Have you set up advanced permissions? If the answer to these questions is yes, be careful.
Assign the records to a user that will create the right permissions for that record and after importing, reassign the records to a queue.
Open one or more of your imported records before accepting the import to check that you're happy with all the field mappings.
Your import should run smoothly if you have prepared the import data and work through the Import Wizard systematically. To help you with preparing your data we've put together a spreadsheet giving lots of useful information on how your data should be formatted to download, click here.
You'll see two tabs for each record type, one detailing the standard fields for each record type, which gives information on the required format for each field. The second tab provides a template you can use and a few rows of example data.
We recommend that you read through the information provided here before running your first import. You can go straight to specific areas by clicking on the links provided:
- Stage 1 - Upload File
- Stage 2 - Review Uploaded Data
- Stage 3 - Define Field Mappings
- Field Mappings
- Indexing Fields for Import Mapping
- Matching on Blank Values
- Field From Source File Mapping
- Concatenate Fields from Source File Mapping
- Fixed Value Mapping
- Blank Value Mapping
- Mapping Date Fields
- Mapping Currencies
- Update Settings
- Linking imported data to existing Workbooks records
- Multi-field Matching Rule
- Extending your Import to other Record Types
- Stage 4 - Existing Record Matching
- Duplicate Checking
- Running your Import
- Reviewing your Import
- Imports awaiting a Response
- Undoing an Import
To start the Import Wizard click Start > Import and from the list in the left-hand column choose the type of records you want to import. If you're simply importing one type of record with no related records it should be fairly easy to choose.
For information on the type of records you can import along with your main record, click here.
Stage 1 of the Import Wizard is where you select the CSV file you want to upload for your import.
- Use previous settings
If you've already imported similar data and want to re-use the mappings used in a previous import, tick the checkbox next to Use mappings from previous import. This will activate the picklist at number 2 where you can specify which previous import mappings you want to re-use.
- Activate the picklist
Imports can be audited in Workbooks just like any other record type. This is providing that you have purchased the additional licence for the Audit module. If you are regularly updating records via Import, you may wish to audit your changes to keep track of what fields were updated.
The audit module in Workbooks creates an additional record when a field is updated, because of this enabling Import auditing can significantly slow down the time it takes to complete your Import.
- Find your file
Click on Choose File then navigate to the CSV file you want to import.
- Use column headings
We recommend that you use column headings in your CSV file (and ideally, use names that match the field names used in Workbooks).
However, if your CSV file doesn't have column headings, remove the tick from the box next to File contains column headings. In this circumstance, Workbooks will automatically name the columns headings for you, starting from Col 1.
- Auto-Map file headings
Leaving the tick in the checkbox next to Auto-Map file Headings will prompt Workbooks to create likely mappings for the imported data, which you should review and refine if required. If you don't want Workbooks to do this, remove the tick.
- Choose an applicable file format
The Import Wizard defaults to expecting your CSV file to be in Windows format as this is the most common format. You should change this to Unix/Linux if you are using Linux or Mac OS X 10.0 (or newer). Alternatively, if your Mac is older than this you should use the Mac option in the picklist.
- Choose applicable character set
For a 'Windows' based CSV file Workbooks defaults to expecting your data to use a West European character set, which is likely to be suitable for most needs. However, if your data is in another character set you can use the dropdown picklist to select the appropriate one.
For CSV file format of 'Mac' and 'Unix/Linux' the default character set is 'UTF-8 Unicode'.
- Advanced upload settings
The default settings for how your CSV file is uploaded are likely to work for most files. However, if you need to change these settings open the reveal next to Advanced CSV file upload settings and make the required changes.
When you're happy with the first step click Next.
Stage 2 of the Wizard is where you can check that the uploaded file contains the amount of data you expected and, if required, rename the import.
NOTE: Workbooks automatically names your import but you can overwrite that. This is especially useful if you want to create a 'template' of import mappings to be re-used later as you can give it a name that you'll remember.
Click on Uploaded File to check that the number of columns and rows in your import match the number that Workbooks has uploaded.
If the data isn't as you'd expected you can cancel the import, revisit your source data and start the import again.
Imports can be queued up in Workbooks so that as soon as you have approved your first Import job, the next one will then start as soon as the service has available resources. The Run After picklist will display a list of created import jobs that have not yet been approved.
This is an optional field but if you choose to populate it then your import will not start until you have approved the import that is selected here.
NOTE: Do not select the name of the import that you are currently running from this picklist. If so, your import will never run and it will have to be aborted.
When you're happy with this stage click Next.
Stage 3 is where you control which fields within Workbooks will be populated with information from your source data. This is done by 'mapping' the fields from your source data against the fields within Workbooks. All mandatory fields within Workbooks (marked with a star )must be mapped. If you left the checkbox next to Auto-Map file Headings ticked at Stage 1 Workbooks will suggest likely mappings from your source file to Workbooks fields.
Make sure you check that Workbooks has mapped the fields correctly. If required, you can amend these mappings by clicking on the row you want to amend.
More information about field mappings can be found below.
Workbooks can create different types of mappings for you, as listed below:
- Field from Source File
A direct mapping of the information in your source data into a specified Workbooks field. This is the simplest option and will take the value from the specified column in your source data and put that into the Workbooks field that you've identified in section 1 of the screenshot below.
- Concatenated Fields from Source File
This option allows you to concatenate (ie, combine) the values from two or more fields in your source data and put them all into the Workbooks field that you've identified in section 1 of the screenshot below. Alternatively you can use a program like MS Excel to concatenate your data before starting your import.
- Fixed Value
This option means that you don't use values from your source file but instead you can enter a value that will appear in the Workbooks field that you've identified in section 1 of the screenshot below. This will populate every record you import with exactly the same data for that field.
- Blank Value
Similar to the one above except that the Workbooks field you've identified in section 1 of the screenshot below will be blank on every record you import. Of course, this option cannot be used for mandatory Workbooks fields.
In addition Workbooks gives you the option to replace any blank rows in your source data with a fixed value, by ticking the checkbox next to Use fixed value in place of blanks. This opens a text box where you can enter the fixed value you'd like to use. More information on the different mapping types is given below.
If you are importing to Custom Fields that are not indexed, this can significantly slow down your import if you are using these to uniquely identify your record. It is recommended that if you do this frequently, you should index the field. More information on Custom Fields, can be found here.
When importing data, looking for matching records based on blank values can slow the import down considerably. Our import wizard no longer defaults to matching blank values, although a user can make a conscious decision to enable it if required. By not looking up blank values for matching purposes your imports will run much more quickly.
This is the simplest type of mapping and is likely to be the type you use most frequently so it is the default setting. All that's required is:
- Use the dropdown picklist next to Field in section 1 of the above screenshot to choose the Workbooks field where you want the data to appear.
- Leave the Mapping Type field in section 2 as Field from Source File.
- Use the dropdown picklist next to File Field in section 2 to select the field in your source data that you want to import into the Workbooks field you've already specified.
NOTE: There is also a checkbox called Use fixed value in place of blanks. Ticking this allows you to import the values in your source data when a value has been entered and import a fixed value for the records if your source data field is blank.
Use this option if the data you want to import into one Workbooks field is split across more than one column in your source data. A common example of this is a file where a Street Address is split across multiple columns.
- When importing People it's mandatory to import their full name. Workbooks will then use this data to auto-populate the First Name, Middle Name and Last Name fields. You may need to concatenate fields in your source data to map to the Name field.
- You can only concatenate data into Workbooks fields are either text or multi-select fields.
To concatenate fields, follow these steps:
- Use the dropdown picklist next to Field in section 1 of the above screenshot to choose the Workbooks field where you want your concatenated data to appear.
- Choose a Mapping Type of Concatenate Fields from Source File, which will cause some more fields to appear as shown below.
You can now choose how you want the concatenated fields to be separated using the dropdown picklist next to Concatenated Field Separator. You can choose from Comma, Space, New Line or Custom. If you choose Custom you can enter whatever letter or symbol you want to use in the field next to Custom Field Separator. You can define which fields you want to concatenate by clicking Add and choosing the fields from your source file. The order in which you enter the fields is the order in which they'll be combined when concatenated.
- If you are concatenating data into a Workbooks multi-select field the only field separator you can use is a comma.
- There is also a checkbox called Use fixed value in place of blanks. Ticking this allows you to import the concatenated values from your source data when a value has been entered and import a fixed value for the records if your source data field is blank.
Use this option if you want all the records you're importing to have the same value in a specific field. To use Fixed Value mappings:
- Use the dropdown picklist
Next to Field in section 1 of the above screenshot to choose the Workbooks field where you want your data to appear.
- Choose a Mapping Type of Fixed Value
Which will cause a new field to appear called Fixed Value. Use this new field to enter the text that you want to appear in Workbooks. You can also tick the box next to Capitalise initial letter of each word to help you 'tidy up' your data.
NOTE: If you want to use a Fixed Value for Workbooks fields that are checkboxes, enter either 1 or Y if you want the checkbox to be ticked. If you want the checkbox to remain empty, don't enter anything.
Use this option if you want a particular field in your imported records to be blank. Simply:
- Use the dropdown picklist next to Field in section 1 of the above screenshot to choose the Workbooks field you want to be blank.
- Choose a Mapping Type of Blank Value.
Of course, this option cannot be used for mandatory Workbooks fields.
- When you map data to a Workbooks field that is a date or date/time field, you'll be prompted to specify the format that's used in your source file for the date using the dropdown picklist next to Data Format. The standard choices are:
- %d/%m/%Y, which, for example, corresponds to 30/09/2011;
- %m/%d/%Y, which corresponds to 09/30/2011; and
- %Y-%m-%d, which corresponds to 2011-09-30.
If none of the above formats match the format of the data in your source file you can specify the format yourself. For example, if your source file shows 2011/30/09 you should set the format to be %Y/%d/%m or if your source file shows 30/09/11 you should set the format to be %d/%m/%y.
- If you choose to map data to a Workbooks field that is a currency field, you'll receive a prompt about whether or not all the values are in a single currency.
If they are are, you can tick the box next to All values are in a single currency so used fixed currency code, which will activate a field called Fixed Currency Code from which you can select the appropriate code. If, however, your source data contains values in different currencies, the data must include a column for the currency code and you should use the dropdown picklist next to File Field to select the appropriate column.
If you choose to map data to a Workbooks field that is a checkbox (for example the Customer checkbox on an Organisation record or the No Email checkbox on a Person record), your source data should contain either a 1 or Y if you want the checkbox to ticked. If you want the checkbox to remain unticked, your source data field should contain 0 or N.
Regardless of which mapping type you choose, you'll also see a section of the Edit Field Mappings dialogue box headed Update Settings. The fields here only apply if you're using the import functionality to update existing Workbooks records. If you're simply importing new records, you can ignore these fields.
- Only overwrite if Workbooks field is blank
Ticking this checkbox means that if the Workbooks field is already populated with some data, the values in your source data will not be imported. The source data values will only be imported if the Workbooks field is blank.
- Do not overwrite if source data is blank
Ticking this checkbox means that if your source data is not populated with a value, your Workbooks data will not be 'blanked'
If you're importing data that you want to link to an existing Workbooks record, you must define how to identify the correct record to link to. For example, you may want to import People records and link them to existing Organisation records. You can specify just one criterion to identify the linked record or apply a multi-field matching rule, to apply more criteria.
Fields marked with a triangle – refer to existing records in Workbooks so you need to consider how you want the linking rule to work. If you choose to map to a Workbooks field that refers to existing records, the New Field Mapping dialogue box will open a Linking Rule section, as shown below.
In the example above, the Workbooks field to import data into has been set as the Employer field (within a Person record). The field from the source data has also been set as Employer (simply because the source data column header happens to be the same as the name of the Workbooks field - this may not be the case with your imports).
Within the Linking Rule section, Workbooks has identified that the Employer field within a Person record references an Organisation record within your database so it knows to try to link your import data to existing Organisation records. However, you still need to specify how you're going to identify which records to link to.
This is controlled using the dropdown picklist next to Matching Field. In this example, the values in the Employer column of our source data are the names of the Organisation records so we're using the Organisation name to match on. However, if the source data contained other information, such as the Object Reference number, or a custom field that you've created, we could match on that.
It's important to make sure your newly imported data is linked to exactly the right existing record so Workbooks gives you some more options to uniquely identify the record to link to:
- Case Sensitive Match
Tick this checkbox if you only want the new data to be linked to records within Workbooks when there is an exact Case Sensitive Match between your source data and the Workbooks record.
- Reject row if not found
Tick this checkbox if you want the import to reject the whole row of your source data if Workbooks can't find an existing record to which to link your imported data.
For example, if you're importing a Person called John Smith who works for ABC Company and Workbooks cannot find ABC Company, ticking this box will mean that no record will be created for John Smith. If you don't tick this box, a Person record will be created for John Smith but it won't be linked to any Organisation.
- Multi-field Matching Rule
Tick this checkbox if you want to apply stricter controls on how existing Workbooks records are uniquely identified. See below for more information.
When you tick the checkbox next to Multi-field Matching Rule and click Save & Close a new dialogue box will appear like the one below where you can specify more fields that Workbooks should reference when looking for a unique match.
In this example, we've specified that the Organisation field within Workbooks should match the Employer field in the source data before the new record can be linked. By clicking on Add Referenced Field to match on, we can use the dropdown picklist to identify another Workbooks field that needs to match a field in our source data. You can select as many fields as you like to uniquely identify which record you want your new import data to be linked to.
When importing one type of record you may want to include other (associated) record type at the same time. For example, you might want to import Organisation records AND Notes about those Organisations, or import People and Activities (either historic or future) for those People. Workbooks can help you with this by 'extending' your import. Information on which records can be imported at the same time as your 'main' record can be found here.
Below is a worked example which steps you through how you can import Notes at the same time as importing some new Organisations.
The screenshot shows the CSV data to be imported. (This example doesn't contain many fields - more typically there would be many more fields to import.) Notice that the file contains 6 columns and 5 rows of data plus a header row. It shows 3 different Organisations, each with at least one Note against them.
I started the import by clicking Start > Import and choosing Organisations from the left-hand column.
In Stage 1 of the Import Wizard, I selected the appropriate CSV file (which was in Windows format) and clicked Next. I also left the tick in the box next to Auto-Map file Headings.
In Stage 2 of the Import Wizard I reviewed the uploaded data to check that the file contained 6 columns, 5 rows and a header row and then click Next.
The screenshot below shows Stage 3 of the Import Wizard as it first appeared.
As you can see, Workbooks has automapped the 'organisation name' from the source file to the 'Organisation name' field in Workbooks (which is a mandatory field, as indicated by the star * symbol). I then went on to map the 'org address' from the source data to the 'Street Address' field in Workbooks, the 'org town' to the 'Town or City' field and the 'org postcode' field to the 'Postcode/Zipcode' field.
This just left 2 unmapped fields from the source data: 'notes subject' and 'description', which I want to map into a Workbooks Note, rather than an Organisation. To do this, I clicked on Extend (at the top of the Unmapped Workbooks Fields column) and chose Note. This makes a bar appear at the bottom of the Unmapped Workbooks Fields column, called Note 1, as highlighted in yellow below.
To map my remaining source file fields into a Note, I clicked on the + symbol next to Note 1, which exposes the fields available in a Note. This made it possible to map 'note subject' from the source data into the Subject field in Workbooks, (which is a mandatory field as indicated by the star * symbol) and map the 'description' field into the Workbooks 'Text' field, resulting in the mapping screen looking like the one below.
NOTE: If my source data had included columns for more notes, I could have clicked on Extend again and chosen Note 2 and then repeated the mapping process as outlined above. You can extend your import in this way to include multiple related records if required.
Having mapped all the fields I wanted to import, I clicked on Next, which opened Stage 4 of the Import Wizard. This stage allows you to control how to look for matching records within Workbooks and what action to take if matching records are found.
In this screen, I removed the tick from Use this setting for all (so that I could use different import modes for the Organisation records and the Notes).
Using the dropdown picklist next to Import Mode, I chose Update existing records and create new ones and ticked the checkboxes next to Organisation name and Postcode, which means that if the import found an existing Organisation with the same name AND postcode as the records being imported, the existing record would be updated but if no match was found a new record would be created.
I set the Import Mode for Note 1 to Only create new records and ticked the Subject box, which means that if the import found an existing Note with the same name as the one being imported, the new Note would not be imported.
It's important to understand that this means Workbooks looks for matching Notes for that particular Organisation. It does not mean that if a Note with the same name exists for any other Organisation it will be rejected.
Having set up all my mappings and controlled the matching behaviour I was then able to run the import. Below is a screenshot of the import results, showing the Affected Records tab, along with a screenshot of the original data.
Workbooks has correctly created an Organisation record for ABC Organisation and created two Notes for ABC Organisation, one called Background and the other called Financial Results 2011. It then created an Organisation called Lyons Ltd with a Note called Background.
As you can see, it's fine to have a Note for ABC Organisation with the same name as a Note for Lyons Ltd. Workbooks then created an Organisation record for Top Logistics with a Note called Background. The source data shows that there are two Notes for Top Logistics, both called Background.
However, the way I set up the matching rules, I told Workbooks to look for existing Notes based on their Subject (ie, the name). This means that Workbooks, correctly, did not create a second Note for Top Logistics. This is highlighted in the Warnings tab, which displays a message as follows:
The same technique of extending your import can be applied when importing records of other types.
Stage 4 of the Import Wizard is where you can control how Workbooks compares your source data with the records already on your database to see if there's a match and thus reduce the likelihood of creating duplicate records. You are also given a choice of Import Mode:
- Only create new records
This is the default setting and will simply create new records (subject to the duplicate checking controls that you apply).
- Update existing records and create new ones
This setting will use the values in your source data to update the fields on your existing records, providing that a matching record can be found. If no matching record can be found, the import will create a new one.
- Only update existing records
This option will use the values in your source data to update the fields on your existing records, providing that a matching record can be found. If a matching record cannot be found a new one will not be created.
Tick the checkboxes provided to specify the combination of fields you want to use to uniquely identify an existing record. The options provided will depend on the type of record you're importing and will include any custom fields you've created (as long as you have mapped some source data against them). The ones shown in this screenshot are for an import of People records.
NOTE: When checking for existing People the duplicate checking will be restricted to the People linked to the specified employer. So, for example, if you're importing a Person record for John Smith with an Employer of ABC Company, Workbooks will simply look at the People already linked to ABC Company to see if a Person record exists for John Smith.
The more options you tick, the more rigorous the duplicate checking will be. The Import Mode you apply will govern what happens when a match is or is not found.
When you're happy with all the settings click Run at the top of the screen. Your import will be placed in a queue and you'll be notified when it's ready for review. In the meantime, you can carry on using Workbooks as normal.
The notification will look similar to this:
When you receive the notification, click Open to generate a screen similar to the one below.
This screen gives you information on the success (or otherwise) of your import. The summary tab shows you the Status of your import (in this example, the Status is Awaiting Review), as well as showing you statistics about the number of rows successfully/partially imported or rejected.
The second tab shows the data that has been fully/partially imported. You can click on the records to open them to check that the right fields have been populated with the correct import data and that they are linked (if appropriate) to the right records.
Until the import is Approved, only the User carrying out the import has access to these records. (The User carrying out the import can also open the relevant Landing Page, where imported rows awaiting review will be highlighted in blue.)
The Errors tab will show any records that could not be imported along with information on why they weren't imported. Similarly the Warnings tab shows information about any problems with importing that have resulted in only a partial import of data, as illustrated in the screenshot below.
Notice that to help you identify what has caused an error or warning you're given information about which row of your source data has been problematic, as well as being given an error message showing information about the nature of the problem.
In this example (a People import where the records were to be linked to existing Organisation records), the employer Organisations don't already exist on Workbooks so it wasn't possible to link the People to them.
Opening the Imported Records tab allows you to click and open the records awaiting approval so you can check that the right fields have been populated with the correct import data. We strongly recommend that you carry out spot checks of this data to ensure that the import has created the records as you expected.
Once you're happy with the imported data click Approve. This will complete the import and make your records visible to other Workbooks Users.
If you're not happy with the import you can click Reject, which will remove all the records created via your import and reset the status of the import job to Not Run.
NOTE: Clicking Close simply closes the window, whereas clicking Delete will delete the entire import and discard the uploaded data file.
Until the import is either Approved or Rejected, the records will be visible in Landing Pages (highlighted in blue) but only for the User carrying out the import.
NOTE: It is important that all your imports are either Approved or Rejected and are not left in any other state. Approving an import will change the Status to Complete and Rejecting it will change the Status to Not Run. Leaving an import in any other state is likely to cause problems on your database and possibly corrupt your data.
When an Administration User logs into a database and there are imports awaiting a response within that database, there will be a warning banner displayed on the top of the screen similar to the one below.
By clicking on this banner the user will be directed to an import Landing Page filtered to show the Imports that require attention. This includes information on when the import was created, run and the current status of that import, see below.
By clicking on any of these records, the user will be directed to the screen in which you can Review your Import.
Whilst there are some circumstances in which you can 'back out' an import, we recommend avoiding having to do this by checking your import thoroughly before accepting it.
An import can only be undone within 7 days of it having been approved. To undo it, open the relevant import record on the Import Landing Page and click Undo.
NOTE: Remember though that this will remove all the records you imported, even ones that have been modified.
There are some circumstances which will prevent you from undoing an import. For example, if you've imported Products which have been used within other records, such as within an Opportunity or a Sales Order, you will not be able to undo your import.
To save using unnecessary space on your database you should consider deleting your import records once you're happy that you do not need to undo the import. It's good practice to delete any records with a status of Not Run. If you have any import records with a status of Awaiting Review but you know you will not be approving the import, these too should be deleted.