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 firstname.lastname@example.org
Three times Microsoft Excel lied to you about your data
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.