Results 1 to 11 of 11
  1. #1
    deepusrp is offline Member
    Join Date
    Jun 2009
    Posts
    4
    Rep Power
    0

    Question Problem when exporting to CSV

    Hello All,

    I have a few problems related to formating when exporting data to CSV. I'm following this method: i have a string buffer, to which i add different elements from a list separated by comma. finally i convert this stringbuffer to string and write it to a csv file.

    Most of the data are coming properly except some fields like dates or some long valued fields.

    My problems are:
    1. If the date retrieved from the list is in format 'yyyy/mm/dd', when we view the csv in MSExcel the date format is changed to its default format automatically.

    2. The long number retrived from the list is showing as an exponential number in excel.

    How can i avoid these two formating issue. I dont want to change the formatting in the excel manually for the columns. When they look at the file for the first time, it should show properly.

    Thanks
    Deepak
    Last edited by deepusrp; 06-24-2009 at 02:59 PM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    Quote Originally Posted by deepusrp View Post
    Hello All,

    I have a few problems related to formating when exporting data to CSV. I'm following this method: i have a string buffer, to which i add different elements from a list separated by comma. finally i convert this stringbuffer to string and write it to a csv file.

    Most of the data are coming properly except some fields like dates or some long valued fields.

    My problems are:
    1. If the date retrieved from the list is in format 'yyyy/mm/dd', when we view the csv in MSExcel the date format is changed to its default format automatically.

    2. The long number retrived from the list is showing as an exponential number in excel.

    How can i avoid these two formating issue. I dont want to change the formatting in the excel manually for the columns. When they look at the file for the first time, it should show properly.

    Thanks
    Deepak
    Are the fields formatted the way you need them to be in the CSV file?
    That is, does the date appear as yyyy/mm/dd...if so there's nothing in Java you can do about this.

    If the number is appearing in the CSV as an exponential, you'll need to use a NumberFormat against it to make sure it appears the way you want it to.

  3. #3
    emceenugget is offline Senior Member
    Join Date
    Sep 2008
    Posts
    564
    Rep Power
    7

    Default

    as tolls said, it seems like neither of these problems is due to programming or your csv file but how your excel is set up to automatically format information it reads. if you examine a sample csv file in a text editor, you should see the answer easily. that said, i think it's more important to know why these are problems, unless you just find them annoying.

  4. #4
    deepusrp is offline Member
    Join Date
    Jun 2009
    Posts
    4
    Rep Power
    0

    Unhappy

    Hi,

    Ya you are correct. When you open the csv file in the text editor, it is showing perfectly. that i knew earlier. But, u can guess about the client ;) they want like, when they open the csv in excel they should see the data as they want. I tried to explain them the technical scenario, but they are sticking to their point. :confused:

    Is there any way by which we can set format of the field when we put into csv so that excel picks up those formatting and displays them correctly?

    I'm :( :confused: :mad:

    Any ways thanks a lot for your suggestion.

    Regards
    Deepak

  5. #5
    pbrockway2 is offline Moderator
    Join Date
    Feb 2009
    Location
    New Zealand
    Posts
    4,574
    Rep Power
    12

    Default

    If you want the contents of a cell to appear in a particular way you could wrap it in quotes and see if the client's software interprets that as a String. It is important to realise that, in that case, the value will not be a date - it will be a string.

    If you must have both a date value and a particular format associated with a cell then CSV is not an appropriate format.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    Quote Originally Posted by deepusrp View Post
    Hi,

    Ya you are correct. When you open the csv file in the text editor, it is showing perfectly. that i knew earlier. But, u can guess about the client ;) they want like, when they open the csv in excel they should see the data as they want. I tried to explain them the technical scenario, but they are sticking to their point. :confused:

    Is there any way by which we can set format of the field when we put into csv so that excel picks up those formatting and displays them correctly?

    I'm :( :confused: :mad:

    Any ways thanks a lot for your suggestion.

    Regards
    Deepak
    Assuming they want dates as dates, you're only option is to not use a CSV and to create an Excel file (well edit an existing one) using one of the libraries around...JXL for example (which I only mention because it's the only one I've used). However, that'll be a load more work...and you should therefore charge the silly sods for it. :)

  7. #7
    serjant's Avatar
    serjant is offline Senior Member
    Join Date
    Jun 2008
    Location
    Ukraine,Zaporozhye
    Posts
    487
    Rep Power
    7

    Default

    for CSV use Java CSV parser (google for it) very simple one, and if you need to export into Excel use Apache POI library, also very simple one

  8. #8
    deepusrp is offline Member
    Join Date
    Jun 2009
    Posts
    4
    Rep Power
    0

    Default

    Hello All,

    @ pbrockway2 - I have tried that double quotes method. It displays when opened in a text editor, but Excel is intelligent enough to convert even the quoted values. :(

    @ Tolls - The requirement is a csv file.

    @ serjant - My problem is not with creating the csv file. So i think no need to use someother parser (Java CSV Parser). We send them the csv, how they are opening is left to them. They might export it to excel or simply opens in MSExcel.

    I think looking at the overall discussion, the requirement is not feasible as the rendering in excel is dependent on viewers personal locale settings. If any body has a solution please let me know.

    Thanks
    Deepak

  9. #9
    deepusrp is offline Member
    Join Date
    Jun 2009
    Posts
    4
    Rep Power
    0

    Default

    Hello All,

    As a workaround, what i'm doing is, for long numbers and dates, i'm enclosing them insite single quote ('asdf') and not double quotes. Because excel changes the format even for the double quoted strings.

    One drawback of this workaround is, when the user views the csv on the excel, the single quotes will also appears. I think its a kind of trade-off. If you want format to be unaltered, you have to accept single quotes in the excel! otherwise the format will change!

    For time being i've found this workaround, if anybody knows the correct way, please let me know.

    Thanks
    Deepak.

  10. #10
    pbrockway2 is offline Moderator
    Join Date
    Feb 2009
    Location
    New Zealand
    Posts
    4,574
    Rep Power
    12

    Default

    if anybody knows the correct way, please let me know
    I think the correct way has already been pointed to a couple of times in this thread. As tolls put it "Assuming they want dates as dates, you're only option is to not use a CSV and to create an Excel file".

    It is important to realise that a CSV file does not contain values of a particular type with a particular format. It contains bits of text separated in some (more or less) well defined way. So Excel is not changing the format: it is merely applying a format and a type where there is none.

    The correct response faced with the "requirement" of expressing a dates with a particular format in a CSV file is so say to whoever gave you this requirement - as politely as you can manage - "Do you know what a CSV file is? Because Excel and other file types store type and format information, CSV files don't."

    Using single quotes satisfies neither part of the requirement: the resulting cell values are ot dates, and, specifically, they are not dates in the particular format you wanted. I point this out because when Tolls pointed to the need (==necessity, ==requirement) of using Excel files for this task, the answer was "CSV is a requirement!", but now you seem content to alter the requirement.
    Last edited by pbrockway2; 06-26-2009 at 10:02 AM.

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    Quote Originally Posted by pbrockway2 View Post
    Using single quotes satisfies neither part of the requirement: the resulting cell values are (n)ot dates, and, specifically, they are not dates in the particular format you wanted. I point this out because when Tolls pointed to the need (==necessity, ==requirement) of using Excel files for this task, the answer was "CSV is a requirement!", but now you seem content to alter the requirement.
    Exactly. Should the client wish to order the rows by date, for example, this will no longer be possible. Well, it wil be possible but will probably result in an incorrect ordering.

    If they require a date then you should be giving them a date (ie something Excel recognises as a date). If it needs to be in a particular format then either you will have to provide an Excel file instead, or they will have to do some formatting (in Excel) of the CSV data to create a worksheet. Sticking quotes around the value is not really a solution...it's sticky tape attempting to disguise the problem.

Similar Threads

  1. Exporting Question
    By Moncleared in forum Eclipse
    Replies: 9
    Last Post: 03-08-2009, 08:35 AM
  2. Greenfoot exporting in .jar?
    By sciguy77 in forum New To Java
    Replies: 0
    Last Post: 01-18-2009, 08:06 PM
  3. Exporting my project
    By Nim in forum CLDC and MIDP
    Replies: 3
    Last Post: 11-04-2008, 12:53 PM
  4. Problem Exporting a Jar as an Applet
    By Hank Ag99 in forum Eclipse
    Replies: 0
    Last Post: 12-22-2007, 10:22 PM
  5. Exporting/Importing JAR files
    By JavaForums in forum Eclipse
    Replies: 0
    Last Post: 04-26-2007, 11:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •