In some cases, the ID numbers assigned by LeadsRx to conversions, touchpoints, groupings, etc. may be quite long. Unfortunately, applications like Excel will sometimes default these numbers to scientific notation. If you then try save this Excel file as a CSV and try to upload it into LeadsRx, you will receive an error message that the ID number does not exist in our system.

Because this is a feature of Excel, it has to be corrected for in Excel before saving the file as a CSV for upload. The solution is for these large numbers to be saved as text in Excel, but there are a few potential pitfalls.

First, if the number is already in scientific notation, just converting it to text, or to a number and then to text, will not work because Excel will likely round the number off before converting it.

Second, if you enter a long number into a cell in Excel without specifying first that it should be considered as TEXT, Excel will automatically convert is to scientific notation.

EXAMPLE: If you entered a number longer than 15 digits into an Excel cell and then hit enter, Excel will convert this to scientific notation.  If you click into the cell, you will see the number rounded off to 15 digits.

Original number3377699726026627
Scientific  notation3.3777E+15
Converted to a Number3377699726026620
Entered as Text3377699726026627

For these reasons, you will need to force Excel to consider the value as TEXT before a number is even entered.

If you are beginning an Excel file from scratch, select the columns that will contain long ID numbers while they are still blank and save those columns as TEXT from the very start.

FORMAT NUMBERS AS TEXT

If you have downloaded a CSV from LeadsRx, the ID numbers will be correctly stored in the CSV until you open it in Excel to edit. At that point, Excel will change the format and mess up the data. To avoid this, you can import the data into a new Excel file and open that column as text.

 

 

https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

 

Here is an article from Microsoft that you may find helpful. It includes instructions on turning long number strings to text.
Excel auto roundoff long numbers in the .csv file

Please reach out to us if you are continuing to have problems.