7 Replies Latest reply on Sep 29, 2017 9:45 PM by Mike Kutz

    Problem with "Import Data..."

    Mike Kutz

      SQL*Developer Version 17.2

      I'm trying to load data into a table via "DB Connector -> schema -> tables -> {table name} -> Right Click -> Import Data..."

       

      If the data file has a space for a numeric column, you get an error.

      Is there anyway to "fix" this in SQL*Developer (instead of adjusting the data)?

       

      Thanks

       

      MK

       

      CREATE TABLE statement

      create table test_load (
      col1  int not null,
      col2  varchar2(40),
      col3  number,
      col4  date
      );
      

       

      contents of test file:

      COL1;COL2;COL3;COL4

      1;Stout;4;2017

      2;IPA;4;2015

      3;none; ;2012

       

      loading parameters  ("Preview Row" = 2 to simulate problem at line 123456 )  (COL4 is modified for FORMAT=YYYY; all others are default)

       

      Resulting error

        • 1. Re: Problem with "Import Data..."
          Gary Graham-Oracle

          Is there anyway to "fix" this in SQL*Developer (instead of adjusting the data)?

          The wizard does not let you override the blank and instead force in, say, a null value.  I suppose you could do something like...

          1. add a dummy VARCHAR2 column

          2. import into dummy instead of COL3

          3. Write an UPDATE statement to copy/convert any non-null dummy values into COL3, with blank presumably mapping to null or 0.

          4. drop the dummy VARCHAR2 column.

           

          But I would be very tempted to adjust the data instead.

          • 2. Re: Problem with "Import Data..."

            The wizard does not let you override the blank and instead force in, say, a null value.

            The image appears to show a decimal point not a blank - can't tell for sure though.

             

            If so that suggests a possible bug in sql dev - why would it use a decimal point when there is no value?

             

            I agree though that the data should be fixed - actually the process that created the data.

            • 3. Re: Problem with "Import Data..."
              Gary Graham-Oracle

              I think those extra dots in the image come from however the OP has the output rendered.  My worksheet's script output tab only produces black and white, with blanks shown as blanks -- I tried a single row insert in CSV format using HR.EMPLOYEES data with SALARY blanked out.  Got the same insert failure.error message, but actually the wizard warned me in advance that it wouldn't work...

              BadSalaryImportData.jpg

              • 4. Re: Problem with "Import Data..."
                Mike Kutz

                The dot you see is part of the display option.

                 

                I agree.  Making the changes at the source of the problem would be better.

                But, this is a "1-time" thing that I just have to muddle through.

                 

                I wound up modifying Gary's solution and created a staging table with all columns as VARCHAR2.

                 

                MK

                • 5. Re: Problem with "Import Data..."
                  Gary Graham-Oracle

                  So many preferences, so little time...   Tools > Preferences > Code Editor > Display > Show Whitespace Characters

                  • 6. Re: Problem with "Import Data..."
                    Mike Kutz

                    Gary Graham-Oracle wrote:

                     

                    I think those extra dots in the image come from however the OP has the output rendered. My worksheet's script output tab only produces black and white, with blanks shown as blanks -- I tried a single row insert in CSV format using HR.EMPLOYEES data with SALARY blanked out. Got the same insert failure.error message, but actually the wizard warned me in advance that it wouldn't work...

                    BadSalaryImportData.jpg

                    That's because the problem data is within the first 100 rows.  (The default value of "Preview Row Limit" on the first page of the Wizard)

                    It doesn't help when the problem data is on rows 46795, 46790, 46844,  and 5367231

                     

                    If there was only some way to add just a tiny bit of Transformation code that the insert statement used by SQL*Developer (in my example) becomes....

                    INSERT INTO TEST_LOAD (COL1, COL2, COL3, COL4) VALUES (to_number(?,'999999999'),?,to_number(trim(?),'999999999'),to_date(?, 'YYYY'));

                     

                    MK

                    • 7. Re: Problem with "Import Data..."
                      Mike Kutz

                      PS - the SAVE STATE feature does not seem to save the fact that the files are actually semi-colon delimited files even though the files have a ".csv" extension.