*
The moose likes Java in General and the fly likes CSV+truncate leading zero solution Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "CSV+truncate leading zero solution" Watch "CSV+truncate leading zero solution" New topic
Author

CSV+truncate leading zero solution

Jyoti Chavan
Greenhorn

Joined: Dec 06, 2011
Posts: 8
Opening CSV in Microsoft Excel leading zero are truncated (e.g.2012-01-03 it will give me 2012-1-3 )
please suggest me solution on this.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38075
    
  22
Welcome to the Ranch

You are usually better off searching for CSV parsers than trying to do it yourself. If you want to get that String into a date, I suggest you look at the SimpleDateFormat class. If you want to display it, have a look at the date and time handling in the java.util.Formatter class; String#format and System.out.printf use the same format Strings as shown for Formatter.
Jyoti Chavan
Greenhorn

Joined: Dec 06, 2011
Posts: 8
thanks,
But i have this problem is not for date only,e.g i have one field named batch number value is 00001 but when i open csv this value is look like this only single 1 is there it is the problem for all values leading zeros.
please give me solution on this..
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38075
    
  22
Not sure I understand the problem. Do you want to include the leading 0s? You can do that if you retain the information in String format. If you change it to an int or Integer or (I think) BigInteger, there will be no record of the leading 0s.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19656
    
  18

Are you opening the CSV file in Microsoft Excel? This application has a tendency of applying its own formatting. It's easy to change:
- Select the cells (or entire columns).
- Right click
- Select "Format Cells"
- Select the "Custom" category.
- Type in 00000 for the numbers to have at least 5 digits (adding leading zeros), or yyyy-mm-dd for the dates.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Jyoti Chavan
Greenhorn

Joined: Dec 06, 2011
Posts: 8
(e.g.2012-01-03 it will give me 2012-1-3 )
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 7552
    
  18

Jyoti Chavan wrote:but i have this problem is not for date only,e.g i have one field named batch number value is 00001 but when i open csv this value is look like this only single 1 is there it is the problem for all values leading zeros.

Well presumably you must know what it is you're looking at; otherwise there really is no solution. I'd suggest looking at the NumberFormat or DecimalFormat classes.
please give me solution on this.

No; that's not how it works. ShowSomeEffort, and come back if the code you write has problems.

Winston


Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Jyoti Chavan
Greenhorn

Joined: Dec 06, 2011
Posts: 8
Yes rob I m opening Comma separated value in Microsoft Excel.but your solution is i have to do it manually to see the figure,
i have to dump all the data of Comma separated value in Data Base directly,without doing any modifications in it...
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38075
    
  22
Database? What database?
Please tell us the whole story, otherwise we shan’t have the faintest idea what to suggest.
Jyoti Chavan
Greenhorn

Joined: Dec 06, 2011
Posts: 8
I have one CSV file with details i have to dump the data of CSV through shell script in Oracle database in one temporary table.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19656
    
  18

Then why use Excel at all? Just use a proper CSV reading library (see AccessingFileFormats) to read the data, and add it to the database directly. My guess is that you're first creating an Excel file and then using JDBC to read from that Excel file. There's absolutely no need for that.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Another possibility would be to use Oracle's SQL loader. You'd create a control file describing the format of your CVS file and use the SQL loader to import the data into the database. It might be less work and might perform better than a Java-based solution.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

is he using java at all?
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38075
    
  22
He? I always thought Jyoti was a woman’s name.
Jyoti Chavan
Greenhorn

Joined: Dec 06, 2011
Posts: 8
hi
Help me about this ,I dont know about this CSV reading library (see AccessingFileFormats).
I think this could be solution...
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3432
    
  12
Jyoti Chavan wrote:Help me about this ,I dont know about this CSV reading library (see AccessingFileFormats).

1. Click on that link.
2. Scroll down to the Excel section - there you will find links to 6 CSV libraries.
3. Click on each of those links and read the documentation and look at any sample code for each library.
4. Once you have decided which one best suits your needs, use it in your code.

Or in other words Show Some Effort


Joanne
Jyoti Chavan
Greenhorn

Joined: Dec 06, 2011
Posts: 8
I tried one solution:I had added space before it and it works......
My problem is solved...
thanks
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19656
    
  18

That's a workaround, not a solution.
Paramesh Korrakuti
Greenhorn

Joined: Nov 11, 2013
Posts: 1

Append a double quote and tab, problem will rectify. Tab prevent leading zeors truncation and double quote separation of a cell when numbers has comma(,) with in it.

String actualString = "0000012345";

Solution:

"\"\t"+actualString + "\"";


----------------
Paramesh Korrakuti
www.parameshk.blogspot.in
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: CSV+truncate leading zero solution
 
Similar Threads
enhanced for loop hangup?
Problem with Tomahawk library
Not compiling right?
Regular Expression Question
Something catastrophe will happen in Dec 21, 2012 on Earth?