3 Replies Latest reply on Oct 7, 2014 1:04 AM by KiwiAndrew

    Excel Import hangs when columns skipped

    KiwiAndrew

      I have a spreadsheet with 17 columns, and a table to import into with 17 VARCHAR2(2000) columns named COLUMN1 to COLUMN17.

       

      Selecting the table in the connection browser, I choose Import Data from the context menu.

       

      Locate the file (.xlsx), Uncheck the Header Row checkbox and set Skip rows to 4; press Next;

       

      Press Next;

       

      1. If I accept the default colmn list (all columns) and continue to the end, the data is imported correctly;

       

      2. If I deselect column 1 OR 3, map by Name (Not position), and Finish, the import fails and is rolled back;

       

      3. If  I deselect columns 13-17, map by Name (Not position), and Finish, the import succeeds;

       

      4. If I deselect columns 1 and 3, map by Name (Not position), and Finish, the import fails and is rolled back;

       

      5. If I deselect columns 1 and 13-17, map by Name (Not position), and Finish, the import fails and is rolled back.

       

      6. If I deselect column 1, 3, and 13-17, map by name and Finish, SQL Developer locks up permanently and has to be killed using Task Manager.

       

      7. If I save the file as CSV and do the same import (no header, skip 4 rows, exclude columns 1,3 and 13-17), the data is imported correctly.

       

      The data should be imported correctly in all cases - the mapping is constant, and there can be no data errors since the targets fields are all VARCHAR2(2000). I;d expect the data to be loade into the correct columns, as well.

       

      The spreadsheet has some of the columns null, including the ones that are being skipped.

       

      This is on Windows 7 Enterprise; SQL Developer is version 4.0.2.15 (locked down).

       

      Oracle is 10.2.0.4.0

        • 1. Re: Excel Import hangs when columns skipped

          Thanks for the detailed explanation.

           

          Can you post the table ddl and a row or two of sample data so we can try to reproduce the problem?


          • 2. Re: Re: Excel Import hangs when columns skipped
            KiwiAndrew

            Weird... It doesn't hang any more, but still fails to import when columns are skipped.

             

            I have rebuilt another table and a simplified spreadsheet to test with.

             

            Table creation - I've taken it verbatim from SQL Developer, so you may need to fiddle it:

             

              CREATE TABLE "BOOST_DEV"."BILL_CYCLES_EXCEL"

              ( "COLUMN1" VARCHAR2(2000 BYTE),

            "COLUMN2" VARCHAR2(2000 BYTE),

            "COLUMN3" VARCHAR2(2000 BYTE),

            "COLUMN4" VARCHAR2(2000 BYTE),

            "COLUMN5" VARCHAR2(2000 BYTE),

            "COLUMN6" VARCHAR2(2000 BYTE),

            "COLUMN7" VARCHAR2(2000 BYTE),

            "COLUMN8" VARCHAR2(2000 BYTE),

            "COLUMN9" VARCHAR2(2000 BYTE),

            "COLUMN10" VARCHAR2(2000 BYTE),

            "COLUMN11" VARCHAR2(2000 BYTE),

            "COLUMN12" VARCHAR2(2000 BYTE),

            "COLUMN13" VARCHAR2(2000 BYTE),

            "COLUMN14" VARCHAR2(2000 BYTE),

            "COLUMN15" VARCHAR2(2000 BYTE),

            "COLUMN16" VARCHAR2(2000 BYTE),

            "COLUMN17" VARCHAR2(2000 BYTE)

              ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING

              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

              TABLESPACE "DATAMIG_DATA" ;

             

            Sample date - it works as a csv, so you need to actually save it as .xlsx (I'm using Excel 2013 I think):

             

            Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14,Column15,Column16,Column17

            thing1,,,,,,,,,,,,,,,,

            ,thing2,,,,,,,,,,,,,,,

            ,,thing3,,,,,,,,,,,,,,

            ,,,thing4,,,,,,,,,,,,,

            ,,,,thing5,,,,,,,,,,,,

            ,,,,,thing6,,,,,,,,,,,

            ,,,,,,thing7,,,,,,,,,,

            ,,,,,,,thing8,,,,,,,,,

            ,,,,,,,,ting9,,,,,,,,

            ,,,,,,,,,thing10,,,,,,,

            ,,,,,,,,,,thing11,,,,,,

            ,,,,,,,,,,,thing12,,,,,

            ,,,,,,,,,,,,thing13,,,,

            ,,,,,,,,,,,,,thing14,,,

            ,,,,,,,,,,,,,,thing15,,

            ,,,,,,,,,,,,,,,thing16,

            ,,,,,,,,,,,,,,,,thing17

             

            It fails to load if I skip column 3, and prevfiously it was hanging if I skipped 1, 3, 13-17...

            • 3. Re: Excel Import hangs when columns skipped
              KiwiAndrew

              Can anyone reproduce this? or is it just me?