Good day,
I have a java web application which allow user to download some data in csv format.
Everything is fine unless I have numeric data but start with 0, for example 010.
I wish to store 010 correctly into the csv file, but it will auto change to 10.
In my java code, I tried to append ' in front of the number, but it will store to '010 instead of 010
data.alignCenter().text( "'010" ); \\ data is my TableBuilder object.
Kindly advise.
As I understand you are looking to store the number in string format. In Excel I stored the number's 10 as string in excel by specifying '010 and saved the document as CSV. As soon as I saved the document to CSV, the formatting information got lost and the data got stored as 010.
Once I open the CSV file in excel directly, the data gets auto changed to 10. I did a test with a text file just specifying '10 but the when I see the data in excel it gets transformed to some wierd text. So I do not have the leading ' character when saving numbers with leading 0's. SO, DO NOT OPEN THE CSV FILE DIRECTLY WITH EXCEL!
Here is a link that details how to keep leading zeroes in excel.
Steps that will be useful borrowed from the page:
- Open a new worksheet in Excel (see below for Excel screenshots.)
- Open the Data tab
- Click on the From text button in the Get External Data section
- Select your CSV file to import
- Select the "Delimited" radio button -- Text Import Wizard, Step 1 determines that your data is delimited
- Click Next
- Check "Comma" as a delimiter (column dividers will appear in preview)-- Step 2 lets you set delimiters
- Click Next
- Highlight the column(s) with leading zeros in Step 3
- Mark those columns format as "text" by clicking the radio button in the Column Data Format section.
NOTE: You will need to do this for each column where the data contains leading zeros.- Click Finish
- The leading zeros will still be there in the new worksheet with the imported data.
I found the solution.
Instead of append ' in front of the number, I append =" in front, and " at behind.
This solve my problem.
