https://docs.google.com/document/d/1n3SnRWSNBg0RZmgP0EhTLzzOotLbD3N5L_jEpAQjbas/edit#heading=h.j86bemvfnbqw


Overview

Exclaim can import an Excel Schedule of loss in one of the following Excel formats - .xls, .xlsx, .xlsm - directly into exclaim.  

How to upload an Excel SOL

The Excel SOL Upload is accessible from the main claim page.

To import a schedule of Loss:

  1. Navigate to the main claim page.

  2. Click Import.

  3. Click on Choose file and select the file that you wish to import.

  4. Click Import.

  1. You will get a confirmation when the file is uploaded.

You can also get the insurance companies default SOL if you click on the “Use this link to download the Excel Template”


The Excel upload will not upload any numeric cells that have Excel formulas. The cell contents will be empty for any numeric cell that has an Excel formula. This includes the following columns - Quantity, Original Price, Age (Years and Months), and insured estimate. Formulas are evaluated for text information like reference number, description, brand, model, supplier. If your spreadsheet has formulas for numeric information you will have to copy and paste-values prior to uploading the Importable SOL.


Excel workbooks with multiple sheets

The upload of content items will only import the first sheet. The Excel workbook may have multiple sheets. The first sheet is the leftmost tab. Also keep in mind that someone may have hidden sheets and the first sheet may be a hidden sheet.

In the following example, the first sheet is the sheet labelled “Summary”. When you upload this Excel document it will attempt to find content items in that sheet. 

If the intent was to upload the sheet labelled “Page 1” you will need to move it to the leftmost sheet position. You can simply drag the sheet to the desired position like the following image.

If you want to upload the content items on multiple sheets, you will have to manually copy the content items from different tabs into the first sheet.


Column headings

The following table highlights the supported columns. The Header values show the currently supported headers. The column heading must be an exact match for one of the values in the header values column for the corresponding column name. The columns can be in any order.

Column Header

Header Values

Notes

Reference Number

reference, no., no, ref, réf.

If the reference number is not supplied, exclaim will automatically create reference numbers. Reference numbers must be unique. If reference numbers are provided, they cannot conflict with reference numbers of content items in exclaim or in the Excel sheet.

Room (1)(2)

room, location, pièce

The room can optionally be provided as an empty row with the description column containing the room name.

Description (1)(2)

description, item description


Brand

brand, manufacturer, marque


Model

model, model number, model #, modèle


Supplier

purchased at, retailer, retailer name, supplier, fournisseur


Quantity (1)

quantity, qty, qté


Age - Year (1)

age, age(yrs), years, y, année

If only a year column is provided, the year can be expressed as a decimal. For example, 1.5 would be 1 year and 6 months. 

Age - Months

age(months), m, months, mois

Year column must exist if months exist. The year is then used as a rounded whole number.

Original cost

original cost, coût original, purchase price each, prix original


Notes

notes, remarques


Condition

condition

The values on the condition column must match the insurer naming of item condition.

Action

action

The only valid entries for the Action field are: “Estimate”, “Accepted” and “Settled”

Value

value, valeur

If an action value is provided the value must also include a numeric value.


Note 1: This column is mandatory column heading. exclaim will generate an error when attempting to upload  if those columns are not present.

Note 2:  Each row in this column MUST include information otherwise exclaim will present an error indicating which Excel row(s) is in error.

Mandatory Columns

The following columns are mandatory to have on any Excel Importable SOL upload successfully into exclaim.

Room

The room column can be either a separate column or can be included in a separate row provided the room name is in the description column AND all the other cells in that row are empty.

Description

A description column must exist AND the description field for each content item must not be empty.

Quantity

A quantity column must exist. If the content item has an empty value for the quantity exclaim will assume the quantity is 1.

Age

An age Year and/or Month column must exist. If the content item has an empty value for the age then exclaim will upload the content item with no age.

The age can be expressed as a Year or a combination of Year and Month. When a single column for Year is included, the value will be converted to years and months. For example, if the Year is 1.5 then exclaim will upload as 1 year and 6 months.

If a spreadsheet has both a year column and month column, the year column is treated as a whole number and will truncate the years column. For example, if the Year column contained 1.75 years and Month column contained 4 months, exclaim would treat the age as 1 year and 4 months.

Reference Numbers

Reference numbers are optional. If a reference number column exists it will automatically upload the content item using that reference number.

Here are the rules for reference numbers.

  1. Duplicate reference numbers cannot exist in exclaim. An error will be displayed indicating the Excel row number of the duplicate reference number. 

  2. Reference numbers can be a mix of numbers and letters (alphanumeric). For example, A reference number can be A-101 is valid. Also, 001 and 01 and 1 are all different reference numbers. Note that in Excel to be able to enter 001 as text you have to enter the reference number with a preceding single quote (e.g. ‘001, ‘01, 1).

  3. There is special handling for spreadsheets that have room names on separate rows. It has been observed that when the row name is a separate row with the room name in the description column that duplicate reference numbers will exist in the spreadsheet. For this specific case, exclaim will ignore the reference numbers in the Excel spreadsheet and automatically assign reference numbers.

  4. When a reference column exists you cannot have some content items with reference numbers and some without. You must either have reference numbers for each of the content items or no reference numbers.

Importing images

If a content item row on the excel spreadsheet includes a hyperlink to an image, exclaim will automatically download the image and associate it with that content item.  Exclaim supports multiple images that can be associated with a content item. The view order of the image on the content item is based on the column order - from left to right - of the images. The left-most image will be the primary image and subsequent images will be displayed in the order of the columns.

If image links are detected, exclaim will immediately start downloading the images. The exclaim import screen will indicate the images were detected and that you will receive an email when the images have completed downloading. 

Image link caveats

There are multiple ways that links to images can be created in Microsoft Excel. Depending on how the image links are created in the Excel document will determine whether it can be uploaded into exclaim. See table below on the type of image hyperlinks (URLs) and what actions are required.

Type of hyperlink

How to identify

Special tips

Text with hyperlink to image

Text that is underlined in blue

This is the most common and no special instructions required

Thumbnail image with hyperlink associated with image

Image thumbnail that when you click on the image opens up the image

Convert the file to .xlsx format prior to uploading into exclaim

Image URL is a formula

The cell contents has a formula similar to this:

=HYPERLINK("<URL>",<DESCRIPTION>)

Not supported. Contact technical support for assistance on how to convert formula links to proper text with hyperlink images


How the image links are stored on the internet will determine if the images can be imported into exclaim.  exclaim will import images natively  if the image url is direct to an image. Some image links navigate to a website that contains images. If you see image navigation bars or any text or buttons around the image then it is probably a website that contains the images and may not directly be supported. The following table enumerates the most common 3rd party importable schedule of loss templates and their supported status.


Source of SOL

Image link URL starts with

Supported

Encircle

https://encircleapp.com

Yes

iCat

https://cloud.icatsoftware.com/

No

Assured software

https://jobcheck-

No


Error handling

Uploading Errors

exclaim will generate an error if it cannot process the header or it finds errors in the content item details.

Error: Invalid import format: missing headers.”

Solution: Check to ensure that the mandatory column headings exist - Description, Age, Room, Quantity

Error: Invalid import format: missing "age" header

Solution:  Ensure that there is an age column with one of the appropriate header values.

Error:  Row (2), Cell (Description) = 'Chair', Item's Description = 'Chair', Error Message = 'Room is missing'.

Solution: Room is missing as a column heading. Ensure there is a heading with the appropriate Room header value. From the example above, Excel row 2 has the missing room. Note, this is the Excel row and not the reference number in the spreadsheet.

Error: Row (3), Error Message = 'duplicate key value violates unique constraint "Content.ClaimId, Reference No"'.

Solution: A duplicate reference number was detected. Either a duplicate was detected within the spreadsheet or the reference number already exists in exclaim for that claim. In the example above, Excel row 2 has a duplicate reference number. Note, this is the Excel row and not the reference number in the spreadsheet. Usually when a duplicate reference number is detected you may have multiple offending rows. exclaim will generate an error for each duplicate reference number.

Errored Fields

Whenever exclaim is unable to assign the value from an excel cell it will place the cell value into the claims note field. For example, if someone attempts to upload a non-numeric quantity in the quantity column with a text value (“Three”). For example, the notes field will include the following.

IMPORT NOTE: QUANTITY: THREE

Excel Tips and Tricks

Exclaim will automatically import many styles of Excel SOL spreadsheets from a variety of sources. But sometimes you may have to tweak the original document to enable importing the information from the spreadsheet.

Excel allows you to transform the information into different formats. This will not be a complete tutorial on the power of spreadsheet formulas but will provide a couple of examples that you can use to be more successful in uploading documents as you expect.

Merged columns

In many cases the headings column will be merged causing an error during the upload process. The best solution to resolve this issue is to highlight the entire document by selecting the upper most left column/cell (see picture below)>from your toolbar make sure “Merge & Center” is not highlighted if so select “Merge & Center “ to unmerge all cells. Once completed you can now update your headers and delete any empty rows. 

The photo below shows the cell to select in order to highlight the entire document. 


Renaming header columns

The simplest trick is to rename a column header to one of the header values that are supported from the table above. For example, if the original spreadsheet has a header of “Item Name” you can simply rename the column to be “Description”.

Auto increment reference numbers

You can introduce some very simple formulas to change the reference number ranges on a spreadsheet. This technique is good when attempting to import a spreadsheet that you know will introduce duplicate reference numbers. Instead of manually typing in new reference numbers you can just change the first cell to the desired value and then add a formula to the cells below. 

The example below shows the Formula of adding one to the value in the previous cell.

Splitting Descriptions

You can also use Excel formulas to split text into their appropriate columns. 

As an example, take this sample where a column with an original header is “Make, Model, Size” and the contents of the column is a combination of the quantity and the description. 

You should first notice that there is a missing “Description” and “Quantity” column so we can add new columns for those headings and then add a formula to parse out what you need to fill in the Quantity and Description fields.

You can see the column labeled Make Mode, Size, etc that the quantity is first text separated by a space and the description is everything else after the space.

The first thing we know we need to do is to create columns for Quantity and Description

To calculate the quantity from the Make and model column we can create a formula to extract what we need. 

The final formula for the Quantity column is:

=IFERROR(LEFT(C4,SEARCH(" ",C4,1)-1),C4)

Breaking it down into each of the components.

  1. SEARCH(" ",C4,1): The character position of the first space starting from the first character.

  2. SEARCH(" ",C4,1)-1: Find the position of the character just before the space.

  3. LEFT(C4,SEARCH(" ",C4,1)-1): Get the text that is left of the space

  4. IFERROR(LEFT(C4,SEARCH(" ",C4,1)-1),C4): If there is no space then just get the entire text.

The final formula for the Description column is:

=IFERROR(RIGHT(C4,LEN(C4)-SEARCH(" ",C4,1)),"No description - see photo")

Breaking it down into each of the components.

  1. SEARCH(" ",C4,1): The character position of the first space starting from the first character.

  2. LEN(C4)-SEARCH(" ",C4,1): Get the number of characters from the first space to the end of the text.

  3. RIGHT(C4,LEN(C4)-SEARCH(" ",C4,1)): Get the text starting from the end of the text to the first space.

  4. IFERROR(RIGHT(C4,LEN(C4)-SEARCH(" ",C4,1)),"No description - see photo"): If there is no space - e.g. no description - then create a description as “No description - see photo”.

Once you have created the formulas you can use copy and paste the formulas to the remaining cells in the Quantity and Description columns. The end result will be the following

.

This may seem complicated but there are lots of articles on how to use Excel formulas when you do an Internet search.

Frequently asked questions

Question: Can I import an Adobe PDF format document into exclaim

Answer: No, The PDF format is more of a layout program and is used to preserve the format and images. There are programs that can convert PDF formats into different formats (e.g. Excel). You will find that each of these convertors have challenges with column identification and word wrapping which require manual editing of the output.  It is best to contact the original sender and request an Excel version of the same document.

Question: Can I upload content items from multiple sheets/tabs

Answer: No. The import will only import the content items from the first tab. 

You have 2 approaches:

  1. Copy the content items from the other sheets to the first sheet. Then import all of the content items at once.

  2. You can upload each sheet one at a time. Between uploads, move the sheet that you want to upload to the first tab. Save the document and then upload.