3 Replies Latest reply on Apr 2, 2015 11:45 AM by thatJeffSmith-Oracle

    4.1 / Excel - issues with import of integer numbers


      I have an .xls file with some column containing integer numbers. When I attempt to load those to the table, import either gets them as "<Number>.0" or "<Number>E<exp>" notations, which might be fine when I import them to number columns, but is definitely NOT fine when I import these to varchar2 columns! I want my "1234567" to stay exactly "1234567" after insert, not "1.234567E6", and not "1234567.0". Is there a way to force SQLDev to process column as text? You can use the attached file containing a single cell with an integer number for tests.




      Also, there's another problem - when I attempt to import that number, but to an actual number column instead of varchar2, I get an ORA-01722 in both cases, with dot and E notations. However, the generated script in "bad" file runs fine. I can guess that's related to regional settings, as decimal point is represented as comma in Russian territory settings, but oracle expects it to be a point when inserting unquoted value. The following is happening under my NLS: (C_CUR is number)

      INSERT INTO TMP_ACC (C_CUR) VALUES (1234567,0); --fails with ora-00913
      INSERT INTO TMP_ACC (C_CUR) VALUES ('1234567,0'); --works
      INSERT INTO TMP_ACC (C_CUR) VALUES (1234567.0); --works
      INSERT INTO TMP_ACC (C_CUR) VALUES ('1234567.0'); --fails with ora-01722
      INSERT INTO TMP_ACC (C_CUR) VALUES (1.234567E6); -- works
      INSERT INTO TMP_ACC (C_CUR) VALUES ('1,234567E6'); --works
      INSERT INTO TMP_ACC (C_CUR) VALUES ('1.234567E6'); --fails with ora-01722

      "bad" file suggests "INSERT INTO TMP_ACC (C_CUR) VALUES (1234567.0);", which works fine, but the import itself fails, so it must use one of the bogus statements - probably, "INSERT INTO TMP_ACC (C_CUR) VALUES ('1234567.0');".