Why am I getting member matching errors with different numbers or missing zeros when I upload my file?

When dealing with contribution or member files, we are often using CSV (Comma separated values) formats.

If you open a CSV from payroll (or elsewhere) directly via Microsoft Excel, then you will experience a data loss in the file. This is because of MS Excel’s data format interpretation process which may result in incorrect formatting and thereby loss of data.

The main data losses include:

• The removal of any leading zero (0s). For example: 00657 becomes 657.

• Long numbers shown in scientific notation. For example: 123456789012 might become 1.23457E+11.

• Rounding values of 16+ digit numbers. For example: 123456789012345678 might become 123456789012345000.

If you see any of these symptoms when uploading a file, it is very likely this is due to the file being opened incorrectly in Excel before uploading to the Portal.

What are the solutions to fixing and avoiding data loss due to opening CSV files in Excel?

Option 1. If you have already opened the file in excel incorrectly:

• Once a file has been opened in excel and saved that way, the data loss is permanent to that particular file and can’t be undone.
• The best solution is to download it directly from the original source (or output again from payroll) and ensure that this newly downloaded file is not opened in excel again this way.
• You can directly upload the file without opening or you may follow the below methods to safely open the file correctly.

Option 2. Importing a CSV file in excel correctly as a Text format:

• Open a brand-new Excel document.

• Hit the ‘Data’ tab at the top of the page, then ‘From Text/CSV.

• Select the relevant CSV to import.

• Make sure the ‘Delimiter’ is set to ‘Comma’, then hit ‘Load’.
• This should ensure no data is lost and leading zero's are preserved.
• You may need to delete extra rows.

• If you need to save the file, don’t forget to save it as a CSV as well. It will not automatically save to CSV by default.
• File -> Save As -> Filetype ‘CSV’.

Caution:

If you edit any of the fields from a correctly imported CSV, you may still have to format the individual field as text first. Otherwise, the field could revert to a number field when you edit it, and you may have data loss for that particular field.

Option 3.  Open the file in an alternative application:

• • There are other applications and software you can open your CSV file in to prevent data loss.
  • A couple of examples: Notepad or Notepad++.