• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

CSV+truncate leading zero solution

 
Jyoti Chavan
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48652
56
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20512
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jyoti Chavan
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
(e.g.2012-01-03 it will give me 2012-1-3 )
 
Winston Gutkowski
Bartender
Pie
Posts: 10273
60
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jyoti Chavan
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Database? What database?
Please tell us the whole story, otherwise we shan’t have the faintest idea what to suggest.
 
Jyoti Chavan
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20512
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1110
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
is he using java at all?
 
Campbell Ritchie
Sheriff
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
He? I always thought Jyoti was a woman’s name.
 
Jyoti Chavan
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi
Help me about this ,I dont know about this CSV reading library (see AccessingFileFormats).
I think this could be solution...
 
Joanne Neal
Rancher
Posts: 3742
16
  • Likes 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Jyoti Chavan
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried one solution:I had added space before it and it works......
My problem is solved...
thanks
 
Rob Spoor
Sheriff
Pie
Posts: 20512
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's a workaround, not a solution.
 
Paramesh Korrakuti
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic