CODEWITHSUNDEEP
javastringexcelcsvnumeric
Panadol Chong
Panadol Chong

Reputation: 1903

Java store number as String in excel

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.

Upvotes: 0

Views: 104

Answers (2)

Panadol Chong
Panadol Chong

Reputation: 1903

I found the solution.

Instead of append ' in front of the number, I append =" in front, and " at behind.

This solve my problem.

Upvotes: 0

randominstanceOfLivingThing
randominstanceOfLivingThing

Reputation: 18642

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:

  1. Open a new worksheet in Excel (see below for Excel screenshots.)
  2. Open the Data tab
  3. Click on the From text button in the Get External Data section
  4. Select your CSV file to import
  5. Select the "Delimited" radio button -- Text Import Wizard, Step 1 determines that your data is delimited
  6. Click Next
  7. Check "Comma" as a delimiter (column dividers will appear in preview)-- Step 2 lets you set delimiters
  8. Click Next
  9. Highlight the column(s) with leading zeros in Step 3
  10. 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.
  11. Click Finish
  12. The leading zeros will still be there in the new worksheet with the imported data.

Upvotes: 2

Related Questions