Import CSV Format Reports into Microsoft Excel


All Users


This is not a LPG feature but is supplemental information to avoid problems when opening a CSV format report that has been exported from LPG.


Before you begin

Follow the steps in the relevant Jasper Report topics to export the report to CSV format. (See Related Topics below.)


IMPORTANT: If you output a report to CSV format,  (A) you must be careful of any data that has leading zeros (e.g. Account numbers or Invoice numbers). The data will be correct if you open the report directly within the LPG Report portal (C). However, when you open the exported CSV file in a program such as Microsoft Excel, typically the leading zeros are automatically removed (D).

The simplest way to avoid this problem is to export as XLSX (B) instead of CSV and open the exported report in Excel.

The alternative is to import the CSV into your spreadsheet program and explicitly tell the program not to remove the leading zeros. This is a more complicated option. To do this using Microsoft Excel do the following: 

1. Open a new spreadsheet in Excel.

2. Click the Data

3. Click From Text.

The Import Text File window opens.

4. Select the CSV report that you exported from LPG.

5. Click the Import

The Text Import Wizard opens.

6. In step 1 of the Text Import Wizard select Delimited.

7. Click the Next

Step 2 of the Text import Wizard is displayed.

8. Make sure only the Comma option is ticked.

9. Click the Next

Step 3 of the Text import Wizard is displayed.

10. In the Data preview, scroll across as required to find the column with leading zeros and click to select the column.

11. Select the Text

12. Repeat steps 10 and 11 for any other columns that may have leading zeros.

13. Click the Finish

The CSV should now be loaded into Excel without removing the leading zeros.

14. Save the file not as a CSV file but as an Excel Workbook.

If you use an alternative spreadsheet application to Microsoft Excel, consult the help information for that application on the topic of importing text files.

Have more questions? Submit a request


Please sign in to leave a comment.