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.
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.
- 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).
- 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.
- After selecting Upload in the previous step, use the Browse button to locate and upload the file from your computer.
- 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.
- Click the Next button. After a few seconds, the file you selected will be displayed in CloudBilling.
- 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:
- All columns are mapped to the correct corresponding CloudBilling object.
- 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
|BillInAdvance||Boolean||Defines if the purchase should be billed in advance|
|CustomerCode||String||Defines the customer code. Customer codes should always be unique|
|DateValues||Date*||Defines any date value that can be added to the meta data field. A label must be provided in the header row. DateValues can be used multiple times.|
|EndDate||Date*||Defines the end date of the purchase in case of pro rata purchases, such as subscription services or usage services|
|InvoiceReference||String||Defines the (unique) invoice reference|
|IsAdhoc||Boolean||Defines whether this is an ad hoc invoice or a recurring invoice.|
|NumericValues||Numeric||Defines any numeric value that can be added to the meta data field. A label must be provided in the header row. NumericValues can be used multiple times.|
|OverrideCost||Numeric||Defines the costs that should override the costs that normally apply for the products|
|OverrideTotalPurchasePrice||Numeric||Defines the price that overrides the normal calculation done by all pricing rules for the products|
|OverrideUnitPrice||Numeric||Defines the price that should override the price that normally apply for the products|
|ProductLabel||String||Defines the product label of the purchase|
|PurchaseDate||Date*||Defines the purchase date|
|PurchaseReference||String||Defines the purchase reference. This value must be unique|
|Quantity||Numeric||Defines the quantity of the purchased items|
|RecurrenceLimit||Numeric||Defines the limit of times this purchase should recur.|
|RecurrencePeriodSize||Numeric||Defines the period size of recurrence defined in RecurrencePeriodUOM|
|RecurrencePeriodUOM||Defines the periods in which a purchase should recur. It could be defined in Second, Minute, Hour, Day, Month, Year. The period size must be defined in RecurrencePeriodSize|
|SourceUpdateDate||Date*||Defines the date when the source is updated|
|StringValues||String||Defines any string value that can be added to the meta data field. A label must be provided in the header row. StringValues can be used multiple times|
* 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.