Call: 01635 889222

change++ Blog

We’ve posted some thoughts below on new product releases, the benefits of product upgrades and added some development tips we find useful……. Let us know if there’s a topic you’d like us to cover on

Email Page Bookmark Page
Like Page Tweet Page
LinkIn Page Digg Page

Three times Microsoft Excel lied to you about your data

Andy Best

Andy Best

Wednesday, 07 October 2015

Quite often when importing data into a Data Warehouse or Reporting Database, the data source will be an Excel file. However, many database developers prefer handling CSV or Text files when importing data.

To generate a CSV file the majority of people will simply hit “Save As…” from within Microsoft Excel but, there are a few areas where Excel will not save your data quite as you would expect. On the flip side, Excel is usually the default program for opening CSV files and this also has its pitfalls.

1. Number Formatting

By default Excel will use Scientific notation for large numbers. For example, “1234567891011” gets translated to “1.23457E+12” in the cell but the formula bar will show the full number in its unadulterated form.

If you simply save the spreadsheet as a CSV file, Excel will use the value displayed in the cell, in this case “1.23457E+12

To avoid this, change the cell format to “Number”

2.  Empty Cells

Excel has a couple of ways of handling empty cells in a spreadsheet. This can cause a real problem when converting to CSV files. You will often find that Excel will treat a cell that has had the data in it deleted as an empty cell rather than an unused cell.

This means that the CSV file you save could end up with lots of extra rows and columns which can cause import processes to fail.

The best way to negate this behaviour is to select the headers for the empty cells, right-click and select “Delete”.

3.  Leading Zeros

This is a real problem when dealing with things like product codes or telephone numbers. By default if you enter a number with a leading zero, for example 01635889222, Excel will treat this is a numeric value and strip the leading zero. You can correct this by changing the cell format or adding a leading apostrophe.

However, if you are opening a CSV file which has leading zeros in a cell, Excel will remove them meaning you might unwittingly be removing a key piece of information if you re-save the file or pass the data on to someone else.

To prevent this from happening, change the cell format to “Text” prior to entering the data.

Reg No. 3221982 | ©2014 change++ | Website Design by The Ad Plain