1) You can use "search and replace" to replace all embedded line breaks in the original data, either with the four-character HTML break code: <BR> (only if the cell is the long, variable-length description field for a specification), or with a space (if the corresponding FF&EZ field cannot contain line breaks at all). 

This is done by searching for the embedded line breaks using a special "regular expression" as the search character (in Excel, this is entered with [Ctrl-J]. Look up "Excel replace carriage return" on the Internet for more information. Note that these usually explain how to replace the line break with a space, instead of <BR>, but using the latter as the replacement text works exactly the same.

2) The other way is to use a formula to produce a new cell that substitutes the alternate characters for the embedded line break (see examples in the next step). 

In either case, replace CR/LFs with a space in all plain character (text) fields and with <BR> in long descriptions that will be imported into a variable length character field. If you use the "search and replace" method, alway do the long description column first (the only one where <BR> can be used), followed by the rest of the spreadsheet rows and columns (to replace the remaining line breaks with a space). 

7. Now, organize your data in the proper import order: On the Sheet2 that you added, use simple spreadsheet formulas to pull the values from the original sheet into the column order required by FF&EZ. Copy those formulas down each column as needed to pull all rows from Sheet1. 
   
   For instance, if FF&EZ needs the "Spec ID" data to be in the sixth column, but your data has it in the first, create a formula in the sixth column of Sheet2 that points to the first column in Sheet1. This is the equivalent of "mapping" the data fields between the two systems, but with all the power of spreadsheet formulas added.  Note that you can also simply copy the data to the correct column in the second sheet, depending on how you want to work and whether you want to set up a template for future imports.
8. You can also use slightly more complex formulas to do some editing as the information is transferred to Sheet2. In the next section we have listed examples of spreadsheet formulas that might be used on Sheet2 as well as for checking data integrity on Sheet1.
9. If you have numeric codes that begin with leading zeroes (e.g., 00001234) be careful that the spreadsheet does not treat these as numbers if the leading zeroes are important to the final format.  FF&EZ's import utility will correctly import these as text based on the definition of the database field. However, if the leading zeroes have been removed by the spreadsheet, you need to correct this before importing. Normally, if you paste data into a spreadsheet or open a text file with a spreadsheet, you can "force" it to interpet "padded" numeric codes correctly by setting the column to be a "text" type instead of "general."
10. Once your data is "clean," you can save it to a "tab-delimited" text file. The easiest way to do this is to highlight the entire non-blank rectangular data area of the second sheet (Sheet2 in our examples) and then copy and paste that into a Notepad file. If you have a format option in Notepad's Save function, be sure to save this as an "ANSI" format text file, not a "Unicode" format. 

If you have any column headings (such as the row of labels in the FF&EZ template), do NOT include these rows. You must only use the data portion of Sheet2.

Data Consistency

Although data exported from another database tends to be relatively clean, if your data's original source is a spreadsheet, it is very important that your data be consistent when repetitive "ID" values are being used. This is because a database like FF&EZ only defines the unique items in it (rooms, vendors, specs, etc.) once, assigning a unique ID to them. When the import process sees an ID that already exists in a project, it does not create a new item but rather "points" any additional instances of the item to the existing definition in the FF&EZ database. This creates some requirements in how you set up your data for importing:

• If your data does not have vendor IDs, you will need to create them (if they do not yet exist in FF&EZ) and ensure that the same ID is assigned to the same vendor wherever that vendor occurs in the spreadsheet.
• If your data does not have Spec IDs, the same situation applies. However, there is a further issue that is absolutely critical: Suppose that a Spec ID is assigned to a product that occurs multiple times in the spreadsheet (e.g., used in multiple rooms). If any of those occurrences have been entered with slightly different details (for instance, a color), all subsequent differences will be ignored (the first one encountered by the importer "wins"). This may be acceptable or it may require that you create a separate Spec ID for the variations.
• Any vendor IDs that already exist in FF&EZ's Vendor List will not be added again or changed. Any spec IDs that already exist in the project will not be added to the project's specifications again or changed.

In all cases, when a specification or vendor is imported, the first occurrence of a new Spec ID or Vendor ID is used to define that spec or vendor. Any and all subsequent occurrences of a particular Spec ID or Vendor ID that are encountered by the importer will be treated as just another occurrence of the first version. Any differences will be lost unless you assign a different Spec ID or Vendor ID before importing.

Here are some ways to assign IDs and the problems that you may encounter:

Here are some ideas that help "clean" spreadsheet data for import. These are usually done on the source sheet, since you are trying to highlight potentially bad "input" data:

• One very good technique for reviewing repetitive data is to highlight the entire data portion of the source spreadsheet (Sheet1 in our examples) and sort it based on a critical column (such as vendor name). This makes it easier to see inconsistencies and decide if a different ID is needed. 

Important: Do this before creating formulas on Sheet2 to pull or manipulate data. Adding the formulas after sorting keeps the formulas from being crossed with each other.

• If you are familiar with the use of "conditional formatting," you can set up each cell in a sorted spreadsheet to look at the cell above it and change color if that cell is different from the current cell. This has the effect of flagging a change in the value that defines two groups. A flagged cell is normally fine, unless you see a flag color where no break should occur. 
• If you are using a formula to create a Vendor ID from the first X characters of a vendor name, the conditional formatting approach can be extended by having the condition look at the vendor name to see if it is the same as the one above. If the formula creates the same Vendor ID but the vendor names are not equal, you can set up a format to flag that. There is an example for this in the next section.