Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    exporting (2000)

    Hello all,

    I have a somewhat interesting problem. Here it goes. I am using software called Crystal Reports to export into a CSV file which is opened by excel. But I have a problem. The date in crystal reports is written as 2003-06-04 (yyyy-mm-dd) and is formatted as a text string. When I export this into excel using the built in exporter, excel sees this as a date and automatically turns puts it in the format (mm/dd/yyyy) and the format is date when you right click-format cells. Whenever we create a CSV file by exporting, crystal reports asks the user to input the new name of the file so I cannot use a template, we are trting to get away from even having to open the csv file. The CSV file is then used to import into a webpage. So... I guess my question is how I can export a string that looks like a date into excel and not have excel automatically convert it to date???

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: exporting (2000)

    can you export it with quotes around it?
    ....., "2003-06-04", .....
    I don't think excel will "touch" items within quotes.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exporting (2000)

    Okay, I tried it with the quotes, the problem is that when I export it, the quotes stay in the cell, which then means that the webpage cannot read in the data from the cell. The format however was the way I wanted it to be. I also tried to trick excel by exporting it in with a few extras spaces behind, but excel just cut them off and converted to a date again. Is there maybe a way to shut off this feature in excel??

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: exporting (2000)

    After importing it into excel what if you format the data column to "yyyy-mm-dd" before exporting it from excel into a CSV?

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exporting (2000)

    The thing is that it defeats the purpose of what we are trying to do. We export from Crystal reports directly into a CSV. Therefore we don't even see the CSV file being created. We are trying to get away from having to open up the CSV file after it has been created and modifying it at all. We would just like the CSV file to be created in properly, then open are webpage and post the csv file into it, that way the webpage reads in the file correctly and does it's thing.

    The reason I keep saying excel is that when we double click the csv file it uses excel to open it.

    thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: exporting (2000)

    Why not change the ASSOCIATION so that the file-type does NOT default to open in excel.
    This can be done in Windows explorer
    Right-click on the file choose "open with" (in some Windows version you must hold the shift down while right-clicking to get this menu item)
    browse
    Select the program you WANT to open them with
    make sure you "CHECK" the box by "Always use this program.."

    Now it is associated with a different program other than excel. If you need that extension to be used for excel and these Crystal report csv, what about changing the extension to something else and then associate that extension to the desired program.

    Steve

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: exporting (2000)

    Better still:
    Change the file extension to something other than CSV (eg CSW or something else that's not used), and give the web page that association. That way, you can output the required format but not have Excel claim ownership, and Excel will continue to open other CSV files.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: exporting (2000)

    That is what I meant in my last paragraph. I guess I wasn't clear enough, will have to watch that.

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exporting (2000)

    Well, all that sounds good, if anyone here knew how to program a webpage. It was created by another compnay years ago and only accepts CSV unfortunatly. However, the discussion regarding associations does sound interesting. But....what other programs will open csv's???? I think that crystal reports exports the feilds into specific columns so that the webpage know where to look but I can try using something else instead.

    thanks

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exporting (2000)

    Notepad, Word and any text editor will open a CSV file. You will need to make sure that the file is saved as a text document and that the name is saved in quotes "myupdate.csv". Without the quotes, it will become myupdate.csv.txt. HTH
    Gre

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exporting (2000)

    I end up with a lot of .csv files from exported/ downloaded bank account data etc. I use the freeware dbEdit to view/ edit them. I think it "snatches" the .csv extension when it installs, which suits my purposes - I find Excel a bit of overkill for this task and dbEdit has some quick & dirty DB functions that I find very handy for these files. It's also very lightweight & fast. However, when I want to include the data in a formatted report or use some of Excel's functions, it's easy to Send To or drag the .csv to Excel.

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Alan

Posting Permissions

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