Exported CSV Reports from SendPro Enterprise not formatted correctly in Excel
Learn how to open SendPro Enterprise reports in CSV format in Excel so that the numbers are formatted correctly.
Products affected: SendPro® Enterprise
Issue
When exporting data to a Comma Separated Delimited file (CSV), long numbers in the file may be changed to a shorter number with an E in it.
Cause
Excel changed the number into E Notation of Scientific Notation.
Resolution
To open the files in Excel so that the numbers are formatted correctly, use one of the following options.
- Solution 1: Change the file type before importing
- Solution 2: Use the Get Data option to import the file
Solution 1: Change the file type before importing
- Download the file as a CSV file.
- Rename the file and change the file extension from .csv to .txt.
If you can't see the file extension, go to the View tab and check the File Name Extensions box. - Open Excel.
- Change the File Type menu from All Excel Files to All Files.
- Go to File > Open or Open > Browse and open the .txt file you just renamed. The Import Wizard opens.
- Select Delimited and My Data has Headers and select Next.
- Check Comma and select Next.
- In the Data Preview area, find any column that has a long string of numbers (10 or more) and click on the column. In the Column Data Format box, select Text. Repeat for each column with more than 10-digit numbers. Once complete, select Finish.
- The file should open and all number fields that were changed to Text should display as a regular number rather than in the E Notation format.
- Save the file. It is recommend to save the file as an Excel file type in order to save your changes.
Solution 2: Use the Get Data option to import the file
- Download the file as a CSV file.
- Open a blank Excel document.
- Select the Data tab.
- Select Get Data > From File > From Text/CSV.
- Select the CSV file you downloaded and select Open. A new window opens.
- Select the Transform Data button. The Power Query Editor window opens.
- Find any column that has a long string of numbers (10 or more) and right-click on the column header. Select Change Type and change the type to Text.
- If prompted to confirm the change, select Replace Current.
- Repeat for each column with more than 10-digit numbers.
- Select Close and Load.
- Save the file. It is recommend to save the file as an Excel file type in order to save your changes.
UPDATED: June 15, 2022