The following information should provide you with what you need to ensure a successful exhibitor list import. We rely on an Excel file in a certain format as outlined below.
Each single row in an exhibitor list usually corresponds to three different record types in Boomer: the Exhibitor, the Main Contact and the Booth. During the import, three separate records are created within the event and connected. They include an exhibitor record, a contact record and a booth record. The booth record is only created if there is a booth number in the booth number column of the Excel spreadsheet. Otherwise, all other booth information is ignored.
Booth information is not required.
If you ever need a copy of the template, click on and save the below .xls file:
Exhibitor Import Template - blank.xls
Column Order and Content
The fields in the spreadsheet map to the following within Boomer when imported:
| Excel Column Placement Order | Name | Target in Boomer | Comments |
|---|---|---|---|
| A | Company | Company name for the contact Exhibitor name |
|
| B | First Name | Contact First Name | |
| C | Last Name | Contact Last Name | |
| D | Email Address | Contact Email | |
| E | Address 1 | Contact Address 1 Exhibitor Address 1 |
|
| F | Address 2 | Contact Address 2 Exhibitor Address 2 |
|
| G | Address 3 | Contact Address 3 Exhibitor Address 3 |
|
| H | City | Contact City Exhibitor City |
|
| I | State | Contact State Exhibitor State |
Must be a valid country defined in Global Settings\Field List Values\States and Provinces |
| J | Country | Contact Country Exhibitor Country |
Must be a valid country defined in Global Settings\Field List Values\Countries |
| K |
Zip |
Contact Postal Code Exhibitor Postal Code |
Note this column must not be defined as a number in the Excel spreadsheet since some postal codes may contain alphanumeric characters. |
| L | Phone | Contact Phone Exhibitor Phone |
|
| M | Phone Ext | Contact Phone Extension | |
| N | Fax | Contact Fax | |
| O | Booth Number | Booth Number | If no booth number is provided in the spreadsheet, all other booth information is ignored and not imported. |
| P | Booth Size | Booth Size | This is a numeric value that represents the total square footage of the booth |
| Q | Booth Dimensions | Booth Dimensions | This is a text value to communicate the length and width of the booth; for example 10x10 |
| R | Booth Type | Booth Type | This optional column is a keyword value Default values include: In-line, Island, Peninsula |
| S | Booth Location | Booth Location |
This optional column is a way to further define the booth's location on the show floor |
| T | Booth Pavilion | Booth Pavilion |
This optional column is a way to further define the booth's location on the show floor |
| U | Authorized to Bill to Master | May be hidden if never used (but not removed). Used only be venues with hotel rooms where a property management system and master accounts are involved. | |
| V | Is Main Contact | May be hidden if never used (but not removed). If this is left blank, the imported contact automatically becomes the main contact for the exhibitor. | |
| W | Sign Name | If left blank, the exhibitor's company name will default in as the Sign Name; or, the value entered here will be imported into the Exhibitor's sign name field. |
Tips on Cleaning the Spreadsheet in Excel Prior to Importing
Q. The spreadsheet has the contacts' name in a single column and Boomer requires First Name and Last Name Columns. How can I break it out?
A. There are two methods you can follow.
The first method is using a fairly new Excel commend. See this link for instructions: https://support.office.com/en-us/article/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e157d1db19
The second method is to use a formula:
If there is only one column in the exhibitor spreadsheet that contains the contact name, you can separate the name into two columns by following these steps:
- Insert a new column and call it First Name. The cell should have the following formula: =LEFT(B2,FIND(" ",B2)-1) (where B2 is the column containing the full name).
The FIND formula returns the number of characters where it finds the first space (which is what is in the quote -- a single space). Then one character is subtracted because we want the location of the last non-space character. - Insert a new column and call it Last Name. The cell should have the following formula: =RIGHT(B2, LEN(B2)-FIND(" ",B2)) (where B2 is the column containing the full name). This takes the total length of the contents of the Full Name cell. The formula subtracts the total number of characters of the first name from the total number characters of the full name and that leaves the last name. The RIGHT command then returns just that string.
- Copy the formulas from the cells you just added the formulas to to the remaining rows in those columns.
Now we need to convert the first and last name fields to actual values rather than referencing the full name, because we eventually need to delete the Full Name column as it is not supported by the Import. To do this:
- Insert two columns next to the first and last name columns you created above.
- Highlight the entire First Name column and right click/Copy.
- Highlight one of the columns you just created in step #4 and right click/Paste Special. Change the Paste option to "Value" and click OK. The column now has the each contact's first name.
- Highlight the second column you just created in step $3 above and right click/Paste Special. Change the Paste option to "Value" and click OK. The column now has each contact's last name.
- Delete the original full name column as well as the two calculated name columns you created in steps 1-3 above.
Q. Dimensions of a booth are in Two Columns?
A. In the Dimensions cell, create a formula that references the two separate dimension values. For example, if the width of the booth (10) is in cell W2 and the depth of the booth (10) is in cell X2:
- Insert a new column and create a formula in the first row of the spreadsheet. The formula in this case would be =IF(X2>0,X2&"x"&Y2,""). For all booths that have a width (where X2 is the cell containing the width), put the width plus an "x" plus the depth value, or "10x10". Otherwise the formula will leave it blank.
- Copy that formula to the remaining rows in that column.
Now we need to convert it to an actual value rather than referencing other columns, because we eventually need to delete columns W and X. To do this: - Highlight the entire column and right click/Copy.
- Move your cursor to where you want the Dimensions column permanently and right click/Paste Special. Change the Paste option to "Value" and click OK. The column now has both dimensions. Delete the original two separate dimensions columns as well as the column with your formula.
Finally, check to see whether the Area column is a formula calculated by the two separate width and depth columns. If it is, we will have to convert that column to values as well since we plan to delete the depth and width columns: - Highlight the entire Area column and right click/Copy.
- Move your cursor just to the right or left of it and right click/Paste Special. Change the Paste option to "Value" and click OK.
- Delete the original calculated column.
Q. What is the best way to fill in the area and/or pavilion based on the booth number column?
A. Excel has a way to put a value in one column based on the value of another column.
First, in a second worksheet of the exhibitor spreadsheet, provide a cross reference table to indicate how booths should be assigned. Name the worksheet tab "BoothValues". Create a table similar to below, where booth numbers determine in which area the booth resides:
| Booth Starts With | Area Is |
|---|---|
| 2 | Shoreline A |
| 3 | Shoreline B |
| 4 | Shoreline A |
| 5 | Shoreline B |
You can establish the table based on any number of digits or even use letters that the booth number starts with. The names of the headers don't matter; just that the table clearly references the booth number and how it should translate to an area.
Second, in the exhibitor import spreadsheet itself, find the "Booth Location" column (or insert one). Now we'll use Excel's "VLOOKUP" command to use the table just created on the second worksheet. The formula for the Booth Location will be similar to this:
=VLOOKUP(--LEFT(Q3,1),BoothValues!$A$3:$B$6,2,TRUE)
The above formula says "Populate the Booth Location field" by looking to the first character by using the cross reference table on the BoothValues worksheet in cell range A3 through B6.
Make sure the green value is the current cell you're in.
If you want it to look to more than the first character, simply change the red number in the formula to the number of characters to look up.
If you have a bigger range, just change the cell references in the blue area to the range on the BoothValues worksheet.