Forum Stats

  • 3,875,722 Users
  • 2,266,954 Discussions
  • 7,912,318 Comments

Discussions

Categories

Importing from excel doesn't work on date fields

711683
711683 Member Posts: 3
edited Oct 21, 2009 7:17AM in SQL Developer
I'm trying to import from an excel file, and two of the fields are dates. When I look at the resulting Insert statement, I see this:

to_date('39896.0', 'MM/DD/YYYY HH:MI:SS')

Which, of course, does not work. Where is this '39896' coming from?

I've seen a few other people with this issue, but no solutions. Is there a suggestion box or something? This tool could be great, but as it stands, even when it works it's too hard to use.
Tagged:
· Share on TwitterShare on Facebook
«1

Answers

  • vasan_kps
    vasan_kps Member Posts: 174
    Can you check and post what is the underlying cell type in excel? Also post the value of the date cell as you see it in xls.
    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,108 Bronze Badge
    I'm having the same problem.
    · Share on TwitterShare on Facebook
  • Jim Smith
    Jim Smith Member Posts: 2,891 Silver Trophy
    sect55 wrote:
    I'm having the same problem.
    And can you provide any information which may help diagnose the problem?

    There is no point in just posting "me too" if you aren't contributing anything to the thread.
    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,108 Bronze Badge
    Jim,

    Here is my detailed explanation. I hope this gives you the needed information to resolve my problem:

    1. I chosed al the fields of the XLS spreadsheet.
    2. I perform the column definitions for about 20 columns. Only one column is a date field, paydate, which i put as the date format MM/DD/YY because that is what appears in the spreadsheet.
    3. I verfiy the import parameters being imported and I receive all SUCCESS for all statuses.
    4. I then click 'Finish'

    I then receive a series of error messages like the following:


    Error starting at line 593 in command:
    INSERT INTO ENGR_SUB_PAYMENTS (TAXID, LOCATION, GROUP, CONTRACT, PRIME, VENDNAMEE, VADD1, VCTY, STATE, ZIP, PAYDATE, SUBNO, MBE, WBE, DBE, PAYNO, PAYMENT, MBECREDIT, WBECREDIT, DBECREDIT, SUPPLRPCNT)
    VALUES ('22-2660081', 'GEORGE WASHINGTON BRIDGE', 'TB&T', 'GWB 244.002', '(Prime)', 'VISTA ENGINEERING CORP.', '1030 PLEASANTVIEW AVE,', 'RIDGEFIELD', 'NJ', '07657', to_date('39,825', 'mm/dd/yy'), 1, 'M', '', '', 5, 1338898.25, 1338898.0, 0.0, 0.0, 0.0)
    Error at Command Line:593 Column:48
    Error report:
    SQL Error: ORA-01747: invalid user.table.column, table.column, or column specification
    01747. 00000 - "invalid user.table.column, table.column, or column specification"
    *Cause:
    *Action

    As you can see from the above PAYDATE value is to_date('39,825', 'mm/dd/yy').

    I hope this is enough information so you can provide a solution.

    Thank you,
    Robert
    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,108 Bronze Badge
    edited Oct 8, 2009 9:12AM
    Jim,

    I provided you additional information over a month ago and I received no response from you or anybody else for that matter. You seemd annoyed that I said I had the same problem but when I provide you additional information, I recieved "NADA' (i.e., nothing) from you. Not responding back did not help me or the others that are having the same problem.

    Robert
    · Share on TwitterShare on Facebook
  • vasan_kps
    vasan_kps Member Posts: 174
    I'll take a look at it today and will update you.
    · Share on TwitterShare on Facebook
  • -K-
    -K- Member Posts: 6,171
    Jim, as you and I, is just another user on the forum.
    Knowing the issue needs more details (a no-brainer) does not mean we know all the answers too.

    Vasan, as part of the dev team, might be more successful in debugging your issue.

    Regards,
    K.
    · Share on TwitterShare on Facebook
  • vasan_kps
    vasan_kps Member Posts: 174
    Can you provide the exact value of the excel cell with the date value as requested in my previous post? It will be even better if you can provide me a scaled down version of your xls with only a couple of rows including the ones for which the bad inserts are generated. You can send the xls to me directly at [email protected] and I'll check and revert back to you.
    · Share on TwitterShare on Facebook
  • sect55
    sect55 Member Posts: 1,108 Bronze Badge
    email has been sent.

    Thank you,
    Robert
    · Share on TwitterShare on Facebook
  • vasan_kps
    vasan_kps Member Posts: 174
    edited Oct 12, 2009 5:09AM
    Thanks for sharing the xls.

    I see that the underlying excel cell category is "custom" and is not a "date". SQL Developer relies on a underlying library called POI to handle xls file formats. I am not sure if POI is capable of handling cells that belongs to the format category called "custom". I'll study POI regarding its capability to handle the format category called "custom". I'll see what best can be done from our end. I'll update the post once I have some concrete information. In the mean time, to keep you going, you can reformat the column using the "date" category and SQL Developer should be able to do the job. Let me know how everything works for you after you reformat the "pay date" column.

    Edited by: vasan_kps on Oct 12, 2009 2:39 PM
    · Share on TwitterShare on Facebook
This discussion has been closed.