Leading zeros are lost when opening a CSV file

Microsoft Excel is a fantastic tool that can be a great help in many situations.  However, when downloading a CSV file of all investment information from your Update Capital software, Excel can sometimes incorrectly assume that some columns (such as bank account numbers) are numbers instead of text values.  As a result, when you download a CSV report from Update Capital - any account numbers, routing numbers, or other banking information that starts with a 0 can be incorrectly read by Excel, which will remove the leading zeros.

If you use Excel 2016 or an earlier version of Excel, please read this to understand how to open CSV files without losing data.  If you're using a version newer than Excel 2016, please read below.

Download the CSV file from Update Capital:

Navigate to the property which you would like to export investment data for and scroll to the bottom of the Investments table, then click Download All Investment Data.

Open Excel, NOT the downloaded CSV file:

Double-clicking on the CSV file to open it will lead to Excel trying to automatically convert the CSV file, this means that Excel will recognize columns such as an account number as a number instead of a text field and remove the leading zeros. This will create a lot of confusion for you and might lead to some data loss as leading zeros will be removed. Instead, open Excel, then:

  • Open a blank workbook
  • Click Data
  • Select Cell A1
  • Click From Text/CSV
  • Select the CSV File Downloaded from Update Capital
  • Set File Origin to Western European (Windows) or Western European (Mac)
  • Set Delimiter to Comma
  • Set Data Type Detection to Do Not Detect Data Types
  • Click Load

This will load your data into Excel correctly formatted with no data loss, Excel will also automatically add a header row, you can delete this by:

  • Clicking Table Design at the top of the page
  • De-selecting Header Row
  • Right-clicking on the row number on the left
  • Clicking Delete

To clear the formats Excel applies:
  • Select all cells by clicking the top left corner of the document
  • Click Home
  • Click Clear
  • Select Clear Formats

You will now have a blank file containing all data, Save as a new file for sharing.