Issues when opening data in Excel
Often when exporting data from Natural HR, you may wish to then open that export in Excel - sometimes when you open that exported data in Excel you may encounter "issues" predominately with the way the data displays where it doesn't display in the way intended.
The most common issues encountered are below along with possible workarounds to help.
Please note, these are Excel issues and not issues in the data export nor anything in the control of Natural HR.
We cannot stop Excel from doing any of the things below and this article is provided as a courtesy to assist customers.
Data does not appear in the correct cells / all data appears in a long line
Whilst technically a CSV file should only ever have commas separating the different fields (hence CSV standing for Comma Separated Values), it is possible to use a number of different separators. The most common options are comma, tab and semi colon.
When you open a CSV file in Excel, sometimes Excel will not be able to determine the separator you are using and will default to using TAB - when this happens your file will open like the below:
When this happens you need to change the way you open the file - instead of opening the file in the usual way you will need to import the data and then tell Excel during the process which separator to use. The actual process will vary according to the version of Excel you use but generally you need to go to Data then From text and then select the CSV file.
You will then see popups similar to the below - there is nothing you need to change on this screen but make sure that Delimited is selected and NOT Fixed width:
This screen is key - below you can see that Excel has chosen Tab as the delimiter hence why the data is all appearing on a single line.
To resolve this, you simply need to untick Tab and select Comma (or whichever delimiter you have chosen):
Once you have done this you will notice that the data now flows into individual columns as expected - when you then click on Finish the data displays how you expect:
Numbers display incorrectly - namely phone numbers and account/sort codes losing leading zeroes
In the above example, the data file has both telephone number and sort code/bank account numbers - these are values which commonly will include leading zeroes.
When you view this data in Natural HR (including in reporting) you will see that these leading zeroes are present:
However, when imported these zeroes are dropped:
Often this is desired behaviour - for example, if you had salaries with leading zeroes then you would want this happen as a number does not need leading zeroes and the leading zeroes themselves do not materially impact the value itself.
However, with phone numbers and sort/account numbers these leading zeroes range from being useful (phone numbers) to vital (sort/account numbers).
To be clear, before opening the file in Excel you can open a CSV file in a text editor like Notepad or Textedit etc - this shows the data which has been exported from Natural HR and will show the leading zeroes are in place:
So how do you handle this? When Excel opens a file it formats the file using a formatting type called General - the key thing is to either "trick" Excel or tell Excel that the content of these specific columns are text and not numbers so Excel will not drop any characters of text fields.
The first option is to edit the source data - in the above example you will see that the first sort code is 001234 and, when opened in Excel, Excel drops the first 2 0's and shows 1234. However if you look at the second row (00-11-22) or the third row (00 11 22) these do not get dropped - why? In both cases the source data has a dash or a space included in them - as a result Excel treats these as text so doesn't drop the zeroes. The same applies for the Work telephone field - row 1 has a space and when imported the zero is not dropped but when row 2 is imported as it has no spaces etc, it gets treated as a number and the zero is dropped.
Now, of course, it is not always practical to go back and edit all your data to meet the above so what are the other options?
When you import a file into Excel, it is possible to tell Excel how to treat specific columns - the process is similar to when using method 1 above but in the third step of the wizard you can specify the data type in each column.
In the below, you can see that Excel is choosing to import each column as General data format - this means that Excel will try and guess the content hence will drop the leading zeroes.
To update the data format of an individual column, you simply need to highlight the column and then (in this case) choose Text as the data format type - you will notice that the first row now shows Text above Work telephone:
You can continue to do this for each and every relevant column as per below:
Now finally, click on Finish and when imported you will see that Excel no longer drops the leading zeroes:
Please sign in to leave a comment.