How do I import customers from an Excel file?
The Data import wizard loads an Excel sheet or a CSV file and creates the ERP records from it. The example is customers, but the steps are identical for items, suppliers, sales agents or any other target.
What do I need before I start?
A file with one row per customer and a first row of headers with the name of each column. A .xlsx, a .xls, a .csv or a .txt separated by semicolons, commas or tabs will do. The wizard detects the separator and the encoding by itself.
Each customer needs a code and a name: they are the two required fields of the Customers target. The code is also the external key, that is, the piece of data the programme uses to recognise that customer if you import the same file again.
The file can’t be larger than 20 MB. If yours is bigger, split it into several.
Go to Management › Data import.
How do I do it?
- On the Target tab, open Import type (target) and choose Customers. The list of the target’s fields appears below, showing which ones are required and which one is the key.
- Go to Data source and click the file selector. Choose your Excel file.
- Check the Preview: the first 50 rows are shown with the columns exactly as the programme has read them. If the columns look out of place, the file doesn’t have the header in the first row.
- Go to Mapping. Each target field has a row with a Column drop-down. The wizard has already paired up by itself the columns whose name matches the field’s; correct or complete the rest.
- Go to Import and click Import now.
There is no intermediate check step: Import now validates and writes in the same pass.
What happens when I click Import now?
The records are really created, one by one, in the company you are working in. There is no undo: to remove what has been imported you have to delete the records by hand.
Each row ends up in one of three statuses and the programme counts them in the Result:
- Created: the record has been set up.
- With errors: the row had a problem and has not gone in. The rest do go in: one bad row doesn’t cancel the import.
- Duplicates: that key had already been imported before from the same type of file and has been skipped.
Below the result is the Issues table, with the row number and the reason for each error or warning. That number is the row number in the file, not counting the header.
What happens if I import the same file twice?
Nothing is duplicated. The programme stores which ERP record each code in the source file corresponds to, so the second time it recognises the ones that already went in.
What it does with them depends on the Reset existing (update what was already imported) box:
- Unticked: customers already imported are skipped and counted as duplicates. This is the default behaviour.
- Ticked: customers already imported are updated with the data from the new file.
That correspondence is stored per company and per source type. The same code imported from a CSV and from an Excel file are, for the programme, two different sources.
And what if a column doesn’t fit the field as it is?
In the Expression column of the Mapping tab you can write a calculation instead of choosing a column. It is used to join two columns, take the VAT off a price or set a fixed value.
The simple assignment the drop-down makes is really the expression [NombreDeLaColumna]. From there you can write things like [Nombre] + " " + [Apellidos] or [TotalConIva] / (1 + [Iva]/100).
If the expression mentions a column that isn’t in the file, the box turns red straight away and the import doesn’t get to run. The full language is in the expressions manual.
If something goes wrong
| What you see | What has happened | What to do |
|---|---|---|
| ‘No reader recognises file …’ | The extension isn’t one of the accepted ones | Save the file as .xlsx or .csv |
| ‘The mapping is not valid. Check the expressions.’ | An expression mentions a column that doesn’t exist | Correct the name in Mapping; it is marked in red |
| ‘“Nombre” is required and is empty’ | That row doesn’t bring the data | Fill it in in the file and import again |
| ‘No value for the deduplication key.’ | The row doesn’t bring a code | Give it a code, or map another column as Codigo |
| ‘Key “…” is duplicated in the file.’ | Two rows in the file have the same code | Remove the repeated one; only the first goes in |
| ‘Tax ID “…” fails the check digit.’ | It is a warning, not an error | The record goes in anyway; correct it afterwards if needed |
| The columns come out shifted in the preview | The header isn’t in the first row | Delete any title rows above it |
| Accents or the ñ are imported wrongly | Old file encoding | Save it again as CSV UTF-8 |
See also: Prepare an import template · What can be imported · Review an import that has been done