Retain Excel formatting on import

Document ID : KB000047764
Last Modified Date : 14/02/2018
Show Technical Document Details

Summary

Excel formatting is not retained when the file is converted to a CSV file, therfor causing carriage returns, bolding, and underlining to be removed. This article documents how to correct this.

Issue

When I convert my Excel spreadsheet to a CSV file for import, the formatting is removed, specifically carriage returns, bold, and underline.

Resolution

Because of this, we recommend using <br/> in place of new lines in input fields, which will result in a carriage return within rich text fields upon import to CA Agile Central. You can also use the steps below to easily search for line breaks inside cells in Excel and replace them with <br/>.
?

Find a Line Break

Line breaks are easy to add, but a little trickier to remove.

To find specific text in Excel, you can use Ctrl + F to open the Find and Replace dialog box. However, if you try to type Alt + Enter in the Find What box, you?ll just hear a beep from your computer. Excel won?t let you enter that shortcut.

Instead of using Alt + Enter in the Find What box, you can use a special shortcut -- Ctrl + J -- to enter a line break.

A line break is character 10 in the ASCII character set, and the Ctrl + J shortcut is the ASCII control code for character 10.

Find and Replace a Line Break

To replace a line break with a space character:

  1. Select the cells that you want to search
  2. On the keyboard, press Ctrl + H to open the Find and Replace dialog box, with the Replace tab active
  3. Click in the Find What box
  4. On the keyboard, press Ctrl + J to enter the line break character -- NOTE: Nothing will appear in the Find What box
  5. Press the Tab key on the keyboard, to move to the Replace With box
  6. Type a space character
  7. Click Find Next or Find All, to find the cells with line breaks.
  8. Click Replace or Replace All, to replace the line breaks with space characters.
**Note - The above information is an excerpt from the following blog on Microsoft Excel formatting. Microsoft owns and manages the formatting code and modifications that retain formatting, and this information is provided as-is to help resolve Microsoft Excel formatting issues**



For bold or underline, you can use the following? markups:

Bold --> <b>? <b/>
Underline-->? <u>?? <u/>