Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Post Unanswered: creating Excel formatted file w/text definition

    I need to create an Excel formatted file from Oracle where each data column will be treated as TEXT format when the Spreadsheet opens.
    I want the recipient of this file to open it in Excel without performing any data formatting steps in the Wizard.
    I spooled a comma separated values file in Oracle. The Wizard was bypassed because Excel recognized the .csv file (which was cool). But all columns were assigned General format and I need them as text format. i.e. a UPC code with a leading zero in the .csv file had the leading zero deleted in Excel which is not desired. It also displayed some of the UPC codes in scientific notation.
    I even put double quotes around the UPC codes in the .csv file hoping EXCEL would then recognize it as a text field but it did not.
    Any Suggestions??? Much appreciated.

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I think you're on the right track Lisa. To get Excel to do what you what you want, try creating your CSV file with Oracle formatting the results first and sending them as text.

    The way to do this is as such:

    dollar amounts: "'$23,100.50"
    upc: "'0-00238213-4123"

    Both ways have a double quote first, then a single quote... the single quote tells Excel to treat the number that follows as a text string and interpret it literally.

    Hope this helps.

    JoeB

  3. #3
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Perhaps it's overkilling the problem, but there's an utility called OWA_SILK that makes possible advanced excel formatting (colors too if i remember correctly).

    Search for owa_sylk on asktom.oracle.tom

    Probably anyway the joebednarz's solution is all that you need. No harm in suggesting ...

    hth
    al

  4. #4
    Join Date
    Jan 2004
    Posts
    4

    Question

    Joe B,

    Thank you for your reply. My results were not successful though. I reran the spool for .csv file creation to format the data as suggested. When I opened the file in Excel, the single quote was visible in the cell. I was hoping for just the numeric value to be there. i.e. '4.85 was in the cell. I had hoped to just see 4.85

    I also got the same result in the cell with just a single quote before the numeric field (no double quotes around the whole value) in the .csv file.

    Any other thoughts? Thanks.

  5. #5
    Join Date
    Dec 2003
    Location
    Ottawa, Canada
    Posts
    72
    Hi Lisa

    I am an Excel-VBA consultant workinf a lot downstream from Oracle. If you save your files as TXT, the user must use the wizard to open the file and then in Step 2 he can require that all fields be treated as text. I have created the "Importer" that does import multiple TXT, CSV and XLS files into an Excel workbook. You can easily modified the open code version to automate this process and create a template.

    Visit:
    http://www.excel-vba.com/e-externa-date.htm

    Regards



    verion
    Originally posted by lisa_app
    Joe B,

    Thank you for your reply. My results were not successful though. I reran the spool for .csv file creation to format the data as suggested. When I opened the file in Excel, the single quote was visible in the cell. I was hoping for just the numeric value to be there. i.e. '4.85 was in the cell. I had hoped to just see 4.85

    I also got the same result in the cell with just a single quote before the numeric field (no double quotes around the whole value) in the .csv file.

    Any other thoughts? Thanks.
    A piece of data is like a brick
    If you don't build anything with it
    It is just a brick
    www.excel-examples.com

Posting Permissions

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