1 Overview

When creating a form in bxp from an Excel spreadsheet it is possible to bump into a few challenges.

There are some limitations to the process which are known and here they are with their corrections.

Issue	Cause	Solution
Data doesn't load	Make sure the data is not formulas	Export to Notepad and drop back in (see below)
Data doesn't load	There is a field length limit of 64 characters	Shorten the column names
Data doesn't load properly	Percentage data, e.g.100%, will be loaded as a decimal, i.e. 100% = 1 and 56.25% = 0.5625	If you want the value, drop the data into notepad, remove the % symbol and reload
Field naming doesn't work	Fractions and high precision values will cause the field mapping name not to work on creation	Wipe the data from the columns, create the form, wipe just the data loaded, reimport the full data
Field naming doesn't work	Column names cannot contain the full stop character (Microsoft reserved character)	Remove the character or replace with a minus character
Field naming doesn't work	Column names cannot contain the [ or ] character (Microsoft reserved character)	Remove the [ Character and replace the ] character with space minus space
Field mapping doesn't match	Duplicate column names must be removed / managed before creation	Make the column names unique
All fields creating as drop down lists	If you have a data sample ensure there is 21 or greater examples as if <20 different values, the question gets turned into a drop down list	Make sure you have enough of a sample data size with varying values where you do not want a drop down list otherwise there is a lot of form tidying.

2 Cleaning data with Notepad

Here is a good article which goes through working with Notepad Create_Form_using_just_headings_from_Excel