CSV+truncate leading zero solution

[Jyoti Chavan]

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]

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]

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]

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]

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]

(e.g.2012-01-03 it will give me 2012-1-3 )

[Winston Gutkowski]

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]

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]

Database? What database?
Please tell us the whole story, otherwise we shan’t have the faintest idea what to suggest.

[Jyoti Chavan]

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]

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 Vashko]

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 L Gibbons]

is he using java at all?

[Campbell Ritchie]

He? I always thought Jyoti was a woman’s name.

[Jyoti Chavan]

hi
Help me about this ,I dont know about this CSV reading library (see AccessingFileFormats).
I think this could be solution...

[Joanne Neal]

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]

I tried one solution:I had added space before it and it works......
My problem is solved...
thanks

[Rob Spoor]

That's a workaround, not a solution.

[Paramesh Korrakuti]

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