This discussion is archived
12 Replies Latest reply: Nov 25, 2012 10:12 PM by Purvesh K RSS

export data

751828 Newbie
Currently Being Moderated
Iam having a date datatype for my Sample table column.

Now i want to export data from my excelsheet to Sample table mention above but the date format in my excel sheet is
3/3/1988. So it was not allowing me to export how to resolve this.
  • 1. Re: export data
    sb92075 Guru
    Currently Being Moderated
    user9093700 wrote:
    Iam having a date datatype for my Sample table column.

    Now i want to export data from my excelsheet to Sample table mention above but the date format in my excel sheet is
    3/3/1988. So it was not allowing me to export how to resolve this.
    use TO_CHAR() function
  • 2. Re: export data
    Manik Expert
    Currently Being Moderated
    Dumping from EXCEL or CSV?

    If you are using SQLLDR and CSV file, then there is an option in control file as below:
    load data
    infile 'tmp.csv'
    into table sample_table
    fields terminated by "," optionally enclosed by '"'          
    ( 
    datefield "to_date(:datfield,'dd/mm/yyyy')", 
    field1
    )
    
    {code}
    
    Cheers,
    Manik.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 3. Re: export data
    Purvesh K Guru
    Currently Being Moderated
    user9093700 wrote:
    Iam having a date datatype for my Sample table column.

    Now i want to export data from my excelsheet to Sample table mention above but the date format in my excel sheet is
    3/3/1988. So it was not allowing me to export how to resolve this.
    So, you can just convert the Date to the format specified in Excel.

    You can use
    to_date(column_name, 'MM/DD/YYYY')
    However, when you mention 3/3/1988, how do I know which of the 3/3, stand for Month and Date? If it is DD/MM, then change the format model in TO_DATE function.

    For more information on Format model, read Datetime Format Model.

    You might as well, want to explore the External Tables option to load data.
  • 4. Re: export data
    751828 Newbie
    Currently Being Moderated
    Where can I include this as I don't have any script
  • 5. Re: export data
    751828 Newbie
    Currently Being Moderated
    Whether I need to alter the column as u mentioned or what
  • 6. Re: export data
    945255 Newbie
    Currently Being Moderated
    if you are uploading excel data through plsql developer tool you can use to_date function
    in function parameter
    otherwise you can change the date format in your excel then export.
  • 7. Re: export data
    751828 Newbie
    Currently Being Moderated
    Can u help me how to change date format in excel
  • 8. Re: export data
    Purvesh K Guru
    Currently Being Moderated
    user9093700 wrote:
    Whether I need to alter the column as u mentioned or what
    What you would be required to do is, follow the example provided in the External Tables and proceed.

    To enlist the steps:
    1. Create a Directory and Grant Read access to User on the directory.
    2. Place the Excel File in the directory.
    3. Create a Temp External Table that loads data from Excel into the Table.(When you are loading the Date column, ensure that the Format Model for TO_DATE is set appropriately. Else you will get error during Date conversion.)
    4. Update the Base table with the data in Temp Table.

    I hope this resolves your situation.
  • 9. Re: export data
    Purvesh K Guru
    Currently Being Moderated
    user9093700 wrote:
    Can u help me how to change date format in excel
    You can learn it by Googling the same.

    Select the Entire column in Excel.
    Right Click on it and select Format Cells.
    In the Format Cells pop up, select the Date category and choose the appropriate Type.
  • 10. Re: export data
    751828 Newbie
    Currently Being Moderated
    HI ,


    iam using SQL loader to export the data ..



    SQL*Loader: Release 11.2.0.1.0 - Production on Fri Nov 16 16:20:23 2012

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

    SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
    ORA-12154: TNS:could not resolve the connect identifier specified
  • 11. Re: export data
    751828 Newbie
    Currently Being Moderated
    yup Purvesh i google it and find how to but there is no date format for my requirement it can only change for 26/9/1983 but my requirement is 26/09/1983.
  • 12. Re: export data
    Purvesh K Guru
    Currently Being Moderated
    user9093700 wrote:
    yup Purvesh i google it and find how to but there is no date format for my requirement it can only change for 26/9/1983 but my requirement is 26/09/1983.
    This is a Trivial issue which can be googled. This isn't a place to support it.

    Yet, just to help you, you have to change the Location from English U.S to Englist U.K. (present just beneath the Type in Excel Format Cell pop up.)


    As for your another issue of TNS Connect Identifier:- Can you post a sample table structure with the script for External Table used? People might be able to help you on it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points