• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

CSV+truncate leading zero solution

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 80874
506
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 80874
506
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 22862
132
Eclipse IDE Spring TypeScript Quarkus Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
(e.g.2012-01-03 it will give me 2012-1-3 )
 
Bartender
Posts: 10780
71
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Marshal
Posts: 80874
506
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 22862
132
Eclipse IDE Spring TypeScript Quarkus Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
is he using java at all?
 
Campbell Ritchie
Marshal
Posts: 80874
506
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Rancher
Posts: 3742
16
  • Likes 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 22862
132
Eclipse IDE Spring TypeScript Quarkus Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That's a workaround, not a solution.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
reply
    Bookmark Topic Watch Topic
  • New Topic