Uploading content using an .xls file is a quick and powerful method of defining your event. However, it has a steep learning curve. If you have problems while attempting to upload content using .xls files, consult the following troubleshooting guidelines to help you on your way.
The best way to manage content using .xls files is to download existing content as a template
The best way to ensure that your .xls file is correctly formatted is to ensure that it starts in the correct format. Download existing event data as an .xls file and you'll have a template that has all the necessary columns present, correctly spelled, and in the correct order.
Save a backup before you modify and upload content
If you upload your.xls files using the default upload options, the uploaded file overwrites the existing event content. Using this method typically makes managing content easier, but it also means that accidentally uploading the wrong file can irretrievably erase content.
Whenever you download an .xls file, save a copy as a backup before modifying and uploading the file. If you accidentally erase data you can simply upload the backup file.
Ensure the file format of the target file is .xls
If you cannot upload data successfully, you may be attempting to upload the wrong type of file. The App Manageronly uploads data from files saved in .xls format, which is the default file format used by Microsoft Excel up to Excel 2007. In Microsoft Excel 2007 and beyond, spreadsheets are saved in.xlsx format by default. These files cannot be uploaded. Spreadsheet created in other applications use other formats such as .csv, which also cannot be uploaded.
To convert a spreadsheet from its current file format into .xls, open the file in any version of Microsoft Excel, and Save As an .xls file.
Ensure the target file contains no formulas and that all data is formatted as text data
The App Manager will only upload Excel data that is formatted as text. Unfortunately, when you are entering numbers or dates in Excel, Excel will sometimes automatically change the column's data format. While this may be useful in other circumstances, changing the data format will cause the App Manager to ignore the data.
Rather than search for unwanted data formatting, it's easier to just select all cells and change them to text data format. To change an entire worksheet to text data format using Microsoft Excel 2013:
- Select all cells by clicking the cell in the top left corner of the worksheet.
- Select the Home tab.
- In the Number group, use the Number Format drop down list to select the Text data type.
Ensure the target file contains no special text formatting or cell formatting
The App Manager will not upload data with special text formatting, such as bold text, italics, underlines, strike through, superscripts, subtext, colored text, and anything other than the default font typeface and size. Similarly, the App Manager will not upload data from cells with highlighting or background colors, or from cells with colored borders. Empty cells must not have text or cell formatting either.
Rather than check for text or cell formatting, it's easier to just erase all formatting that may be present. To remove all formatting from a spreadsheet using Microsoft Excel 2013:
- Select all cells by clicking the cell in the top left corner of the worksheet.
- Select the Home tab.
- In the Editing group, select Clear > Clear Formats.
Ensure that Boolean data columns contain only ones and zeros
Fields in Boolean data columns must only contain a 1
or 0
. If you find a yes
, on
, or something similar in one of these columns, change it to a 1
. If you find a no
, off
, or something similar, change it to a 0
. The data format of these columns must remain as text.
Note that sortOrder, a column that appears in many worksheets, is not Boolean even though it typically contains a lot of zeros.
Ensure that each row's ID value is present and unique
The far left column of each worksheet is very important as it provides the unique identifier for each row of data. If there is data in a row, an ID value must be present and it must be unique. For example, if you are uploading Attendees, you must ensure that each attendee has an antendeeID, and that each attendeeID is different.
Open the file and examine the leftmost column of each worksheet. Ensure that each row with data contains an ID and that the ID is unique, alphanumeric (contains only letters and numbers), and 45 or fewer characters. Note that an ID value only needs to be unique within a given worksheet. You can have an attendee with attendeeID of V0245
and a speaker with speakerID of V0245
, just not two attendees or two speaker with the same ID.
Ensure all times and dates are formatted correctly
The App Manager only accepts dates and times entered in a certain format. Dates must be entered as YYYY-MM-DD
format. Times must be entered asHH:MM:SS
format in 24-hour (military) time, and do not include an AM
or PM
. Note that columns containing date or time information must be in text data format, not in a date or time data format.
Ensure that URLs include protocols
URLs must include protocol information (i.e. they must start with an http://
or https://
). URLs without protocol information will not be recognized.
Ensure that no columns have been renamed, moved, or deleted
When you download your event's database as a spreadsheet, the header row of every worksheet contains the names of the columns. These names tell the App Manager what data to expect in the column. The column names must not be changed or the data will not upload. Furthermore, the App Manager expects to see columns in a particular order, so columns cannot be moved or deleted either.
Check that none of the columns have been renamed, moved, or deleted. Compare your file to a freshly downloaded database spreadsheet if necessary.
Ensure that no worksheets have been renamed, moved, or deleted
Like column names, worksheet names cannot be changed. Worksheets in the database must not be moved or deleted, even if they are empty.
Check that none of the worksheets have been renamed, moved, or deleted. Compare your file to a freshly downloaded database spreadsheet, if necessary.
Check the File size of the XLS
QM's solution is to split the spreadsheet into portions (for example, try creating two spreadsheets - top half and then bottom half). Upload the individual spreadsheets (where you would need to merge, as opposed to overwrite the data). This ensures the file size can be handled by QM's system.
Comments
0 comments
Please sign in to leave a comment.