Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Importing from excel doesn't work on date fields

711683
Member Posts: 3
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.
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:
Answers
-
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.
-
I'm having the same problem.
-
sect55 wrote:And can you provide any information which may help diagnose the problem?
I'm having the same problem.
There is no point in just posting "me too" if you aren't contributing anything to the thread. -
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 -
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 -
I'll take a look at it today and will update you.
-
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. -
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.
-
email has been sent.
Thank you,
Robert -
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
This discussion has been closed.