Importing from excel doesn't work on date fields

711683

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.

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 [email protected] 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