Import filters

With data import/export you'll be able to import and export data to and from Microbizz. This is very useful in integrations but also when having a large portion of data you want imported to Microbizz e.g. customers, contact persons, equipment etc.

Through import filters and export filters we specify the format in which we will read and export data. This can be either in regular Excel-formats (export only) or CSV-files.

Import filters are usually used for importing large portions of data into Microbizz. This is most often done when setting up Microbizz for the first time to get base data in your Microbizz solution right away. Therefore, import filters often have to be adjusted in order to fit the data you're given, but some restrictions in Microbizz import filters may require adjustments to the data from the third party system/database.

Create import filter

To create an import filter you'll have to access the Setup module, then the Filters menu and List import filters. Here you'll get a list of existing import filters and you'll also be able to create new import filters by clicking the 'Create new filter'-button.

When creating a new import filter, you'll firstly have to choose what type of data you're going to import. You can import various types of data into Microbizz and depending on the objects and elements you want import, you'll get a list of different fields e.g. CRM-objects will have fields such as customer number, invoice addresses etc. while equipment have fields such as manufacturer, model and serial number. Custom fields will also be shown in the list of fields, where you can match data.

You will then be presented with the following page, where you need to fill out the following:

• Name: Give the import filter a name e.g. 'Company import'
• Source: Choose what data format you're importing either CSV or text-file. CSV are most common and is also the choice if importing data generated from excel.
• Destination: Here you'll choose what element and type of data you want to import. It's basically what object you'll import in Microbizz e.g. Companies, Equipment, Persons, Users etc.
• Upload File: Here you can choose to upload a test-file. If you choose to do so, you'll get the option to upload the file as well.
• Test data: Here you can specify test data by entering text in the text field.

When set up you can click 'Save' and you'll be taken to the import filter set up page.

Setting up the import filter

You'll be presented with a list of all the fields for the concerning data object you want to import.

The next thing to do is to let Microbizz know where it should read the specific data form in the files you're going to import.

For example (companies):

When import companies you'll have to specify where in your CSV-file you've located the customer number.

On the customer number fields in Microbizz you'll then in the drop down menu choose the specific column in the CSV-file e.g. Column A.

Next the name of the company might be in Column B, then you'll find the company field called 'Name' and match that with Column B.

Next you'll for instance need the address, which is located in Column C.

This goes on till you've got all fields matched.

Combination

If you want to combine a Microbizz field with two columns in your CSV-file, you'll be able to do this by clicking on the small +-sign. You'll then get an additional drop down menu to select what data should be combined with the data in the first column. This will be divided with a hyphen (-) and can for instance be used if you want to combine company name with the address e.g. "Ventu - Lergravsvej 59" - a combination of Column B and C.

Rules

It is possible to set up different rewrite rules when importing data. This is set up by pressing the 'No rules'-text. If rules are created in the import filter it will say 'X rules' - with X being the number of rules set up for the individual field.

Rules can be used to rewrite certain elements in the data, but also used with regular expressions.

Rules will be applied one by one, so the second rule will operate on the result from the first rule and so on. This means that if you have a rule that rewrites A to B, and another rule that rewrite B to C, then the input A will first be rewritten to B and then it will be rewritten to C.

Microbizz uses the PREG regular expressions from PHP. Refer to PHPs documentation for details. The captured patterns are inserted in the "to" field at the position of $¡, $2, $3 etc., so if the input is "25 bananas" or "25 apples" then the rule below will output "25 lemons", but it will output nothing if the input doesn't start with a number:

Import filter options

By pressing the 'Other settings'-button in the bottom of the set up page for the import filter, you'll be able to adjust some additional options for the import filter. By pressing the button you can adjust the following settings:

Ignore first line - An option to ignore the first line in the import data file. Useful if you're using headlines in your data file.

Fields separator - Which character should be used as separator. Default is a comma (,), but newer versions of for instance excel uses semicolon (;) as separator, which then also enables you to use commas within the different data columns.

Quote character - Specific character for quote. Usually just regular quote sign (")

Error for compete line when error in field - specifies whether the whole line should be considered an error, if there is an error in a field.

The rest of the fields depend on the destination (which type of object that are imported, for instance a company or person). There are other parts of the set up, which depend on the destination, therefore we have described the different destinations in detail, below - part of this setup is accessed by clicking on the button Other settings.

USER: You can determine what should happen, if an imported user has the same email adress as an existing user.

TASK: If an imported task matches an existing task, the existing task will be updated, instead of having to seperate tasks. You can also choose which field that should be compared to determine whether the tasks match.

PERSON: Just like with a task and company, you can specify a field that should be compared to existing persons, when importing persons, and you have to specify what should happen, if there is a match.

PRODUCTS: This functions the same way as tasks do.

COMPANIES: You can also import contacts along with company data. This is done by choosing which columns to import as the fields

You can also determine that a column should be imported as the field Start in process. This means that a new unit automatically will be created for the company and the specified process. Alternatively you can choose a process from the Start in proces menu in Other settings.

The following extra fields are found in the settings of an import filter for companies:

• Match on - these fields specify how it should be decided, whether a line in the CSV-file matches an existing company in Microbizz.
• If match - here you can choose what should happen to a line that matches an existing company - whether the company needs to be updated with data from the CVS-file, or that nothing should happen.
• Send to campaign - imported companies will be added to the chosen sales campaign.
• Start in process - a new unit can be created automatically when a company is imported; here you can choose which process the unit should start in.
• Don't set their ref. - if a new contact is imported and this box is checked, then the contact person wont be put as the companies reference.

Testing the import filter

When the import filter has been set up it can be a good idea to test the configuration. This can be done by pressing the 'Transfer new test data file'-button in the bottom of the set up page.

Here you'll be able to upload the import data file When doing this, Microbizz will show you an example of the data with configuration above. It will also highlight some fields or columns if there are any errors.

If any errors you'll have to adjust the data file and then reupload the file.

If no errors you can advance to do the data import.

Importing data

To import data using an import filter you'll have to navigate to the Setup module, then the menu Filters and the List import filters. Here you'll get an overview of existing import filters.

To do the import simply click on the pencil next to the import filter you want to use and choose 'Do import'.

This will guide you through the following steps:

1. Upload file and click 'Show import'
2. An example of the data import will be shown. If too much data Microbizz will only show errors if any. If no errors, advance to step 3.
3. Press the 'Import data'-button and let Microbizz do the rest of the work.

Depending on the amount of data this can take some time. Microbizz will show a loading bar to give you an idea of how far it is.

When done importing you should now be able to work with the data as if you've created in manually.

Data format

When using CSV files we recommend that they contain UTF-8 text. UTF-8 allows characters from any language to be included, whereas the old fashioned 8-bit text (like ISO8859 or Windows code page 1250) only allow a characters from a few languages. With UTF-8 you can have chinese, polish, swedish and greek letters in the same fil.

Object types and values

For some fields you have to specify values that are not obvious.

Task - repetitions

When importing repetitions the repetitions should be specified in the format described here.

Task - invoice period

The possible values for invoice period are: 1w=1 week, 2w=2 weeks, 3w=3 weeks, 1m=1 month, 2m=2 months, 3m=3months, 4m=4 month, 6m=6 months, 1y=1 year.

Values 1, 2, 3 are also valid and specify a number of weeks.

Tasks - visibility

Here you can specify either "normal", "hidden" or "public", or the numerical values 0, 1 or 2.

Equipment - responsible

When importing equipment, mark the field "Don't set responsible if not specified" if the responsible field should not be changed to the user that are importing.

Notes

Task  - stage invoicing

This filter updates/creates stageinvoicing lines for tasks that use stage invoicing. The imported lines are matched with the existing lines by looking at the "Position" field; if there isn't an existing line that matches the position then a new line will be created.