You submitted a report in JD Edwards and selected CSV output as an option. After retrieving the CSV report into Excel, some of the columns that contain leading zeros and look perfectly fine in PDF, drop the leading zeros in Excel. Has this ever happened to you? Did you blame JD Edwards for this behavior? You shouldn’t have! This is actually an Excel issue. Keep reading for a possible workaround to this behavior.
Let’s take the following table as an example:
Notice that the sub field contains leading zeroes in the last 2 rows.
When JD Edwards generates the csv file, the raw data will look like this:
mcu,object,sub
500,1500,100
500,1500,101
500,1500,001
500,1500,002
This can be verified by opening the csv file using Notepad.
But if the same csv file is opened in Excel, it will be displayed as:
The reason for this behavior is that Excel assumes that the field sub is numeric. And by default, numeric fields don’t display leading zeroes. In order to get Excel to display the leading zeros, the sub field has to be identified as text to Excel. This can be done as follows, assuming you’re running Excel 2007:
I must say this is not the most elegant or efficient method, but it works. And if the same csv needs to be retrieved on a regular basis, an Excel macro can be developed to automate this task. Do you know of a better way of accomplishing this? I’d love to hear about it!
Let’s take the following table as an example:
| mcu | object | sub |
| 500 | 1500 | 100 |
| 500 | 1500 | 101 |
| 500 | 1500 | 001 |
| 500 | 1500 | 002 |
Notice that the sub field contains leading zeroes in the last 2 rows.
When JD Edwards generates the csv file, the raw data will look like this:
mcu,object,sub
500,1500,100
500,1500,101
500,1500,001
500,1500,002
This can be verified by opening the csv file using Notepad.
But if the same csv file is opened in Excel, it will be displayed as:
| mcu | object | sub |
| 500 | 1500 | 100 |
| 500 | 1500 | 101 |
| 500 | 1500 | 1 |
| 500 | 1500 | 2 |
The reason for this behavior is that Excel assumes that the field sub is numeric. And by default, numeric fields don’t display leading zeroes. In order to get Excel to display the leading zeros, the sub field has to be identified as text to Excel. This can be done as follows, assuming you’re running Excel 2007:
- Open up an empty Excel spreadsheet.
- Click on Data tab, and then From Text on the Get External Data toolbar
- The Import Text File wizard will open up.
- Navigate to your csv file location, and click the Import button.
- The Text Import Wizard will start.
- Select Delimited data type and click Next
- Click on the Comma checkbox in the Delimeters box. Click Next
- Click on the sub field, and change its properties from General to Text. Click Finish
- The data will be imported and leading zeros preserved
I must say this is not the most elegant or efficient method, but it works. And if the same csv needs to be retrieved on a regular basis, an Excel macro can be developed to automate this task. Do you know of a better way of accomplishing this? I’d love to hear about it!
No comments:
Post a Comment