Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Importing from excel doesn't work on date fields

711683Jul 13 2009 — edited Oct 21 2009
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.

Comments

vasan_kps
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.
sect55
I'm having the same problem.
Jim Smith
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.
sect55
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
sect55
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
vasan_kps
I'll take a look at it today and will update you.
-K-
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.
vasan_kps
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 srinivasan.karthikeyan.pitchai@oracle.com and I'll check and revert back to you.
sect55
email has been sent.

Thank you,
Robert
vasan_kps
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
sect55
I looked at the format of the "pay date" columan and the category is not "custom" but "date" with type "03/14/01" and locale "English (United States)".
vasan_kps
Re-verified the xls you sent to me. The cell format indeed belongs to the category "Custom" and the Type is "mm/dd/yy;@".
vasan_kps
Incidentally, I noticed that I don't see a date format "03/14/01". The closest that I can see is 03/14/98! I am using excel 2000 9.0.3821 SR-1 to open the file. Can you let me know which version of EXCEL was used to create this file. I am observing that the underlying POI library is not even being able to open this file!
sect55
I am using excel 2003 (11.8307.8221) SP3

In Excel 2003 it is using Date not Custom.
vasan_kps
This needs a bit more investigation on the underlying POI library that is used to read and write xls file format. I need to study the extent of support for excel 2003 provided by latest POI library. If it does support 2003 then and a lot of testing needs to be done before it can be absorbed into the product. I'll keep the post updated with my findings.
vasan_kps
Verified that POI 3.5 library is able to handle the processing of files in excel 2003 format. However the library needs to be integrated with the product and tested thoroughly for its overall impact on the product before it can be absorbed into the product stack. Hence POI 3.5 can be integrated into the product only post 2.1 release.
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 18 2009
Added on Jul 13 2009
16 comments
3,821 views