Latest revision as of 12:57, 4 October 2016

1 Overview

Sometimes when loading data from an Excel spreadsheet, Excel can add hidden data and challenges to the import process.

For example if you don't use default font, size, no cell colour, formulas, etc. Excel must keep information about this in the data being imported.

The below process removes all this wasteful data and cleans the data for import.

2 Clean Excel Data

1. Open your sheet with all of the data.
2. Highlight all your data (Ctrl and A)
3. Copy all your data (Ctrl and C)
4. Open an instance of Notepad or Notepad++
5. Paste all of your data into Notepad (Ctrl and V)
6. When all your data has appeared, select all of it in Notepad (Ctrl and A) again.
7. Again copy your data from Notepad (Ctrl and C)
8. Open a new instance of Excel
9. In Sheet 1, select Cell A 1
10. Paste in your data from Notepad (Ctrl and V)

The data should now appear with the default font, no colouring, comments etc. There should be no pivot tables at all.

You will need to scan over the data specifically for date / time format columns or large number columns such as mobile numbers.

Here is a handy guide on Column Formatting http://www.excel-easy.com/basics/format-cells.html Simply select the column not just one cell that you are formatting.

• For date time files, change the format of the column to Custom yyyy-mm-dd hh:mm:ss
• For long number fields, change the format of the column to Text. (You may need to re-paste the data from Notepad, step 6 on again)

Once your data has been cleaned up,

1. Save the workbook to the desktop with a distinct new name
2. Follow the bxp import process as usual