Excel Editor

Introduction

The Excel Editor can be used to import and update Purchase and Customer records in CloudBilling. While it is possible to create a new file from scratch with the Excel Editor, we recommend to always create and prepare the files in Microsoft Excel (or similar software) and use the Excel Editor to import it in CloudBilling.

Procedure

To import a file with the Excel Editor, open it directly from the top menu, or under Connectors if your system has multiple Connectors active, and follow the steps below.

1. In the *Select Entity Type *drop-down, select Purchase or Customer, depending on what you what to import. The procedure for importing Purchases and Customers is the same.
   • We recommend to use a template to import Customers or Purchases. These templates are prepared with the correct header labels (see step 4.) for easier processing. Download them here. Note: both template files contain an example record for illustration purposes. Please delete this before adding your own data to the file(s).
     • Purchases: Data_template_Purchases.xlsx
     • Customers: Data_template_Customers.xlsx
2. In the *Select New or Existing *drop-down:
   • Select New to create a new file from scratch in the Editor (not recommended).
   • Select Upload to upload a file from your computer.
   • Select an existing file that was created or processed at an earlier moment.
3. After selecting Upload in the previous step, use the Browse button to locate and upload the file from your computer.
4. Check the File has header row *box if the selected file has a header row. *Note: we recommend to always use a header row with pre-defined column titles. If done correctly, this will allow CloudBilling to automatically map the correct columns to the corresponding CloudBilling objects.
5. Click the Next button. After a few seconds, the file you selected will be displayed in CloudBilling.
6. If one of the provided templates is used, it should look similar to this:

• The green row represents the header row in the original file.
• in row 2, the destination object (Map To) for each column must be provided. Row 2 will be pre-filled by CloudBilling wherever possible. In other words, if the correct header labels are used, CloudBilling will automatically map these columns to the correct destination objects.

7. Please check the following:

1. All columns are mapped to the correct corresponding CloudBilling object.
2. All date fields are in the following format: yyyy-MM-ddThh:mm:ss (2019-01-01-T12:00:00).

CloudBilling allows extra data to be stored on both Purchases and Customers in the form of Metadata (string, numeric & date). To import these, a label/key/name must be provided in the header row and these must be mapped to the appropriate Metadata type. As an example, consider a Customer that has the following extra data:

• AccountID (StringValue)
• DueDays (NumericValue)
• Birthday (DateValue)

If named and mapped correctly, the example above will look like this:

Columns can be mapped to the following objects

* All date fields are in the following format: yyyy-MM-ddThh:mm:ss (2019-01-01-T12:00:00).

As stated above, it is recommended to use these exact names, as the Excel Editor will recognize these fields and prefill them. This will make importing purchases easier and less sensitive to errors.

After all columns are mapped and labeled correctly, click Save & Process. CloudBilling will import the data from the Excel file. A field with the text “Succesfully saved file changes” will appear. Navigate back to the start screen of the Excel Editor by clicking cancel and click the View Logs button to check whether all records are imported correctly. The logs should have a row that states the latest import. In case of a purchases import, it will state “Imported 0 customers and … purchases from file “…”. In case of a customer import, it will be vice versa. It could take a while when all purchases will appear under the “Purchases” section. The purchases will be put in a queue and be processed.

---