13 Replies Latest reply on Aug 31, 2019 12:26 PM by Beauty_and_dBest

    How to import csv in SQLdev

    Beauty_and_dBest

      12c

       

      Hi ALL,

       

      I am importing csv into our 12c database using Sqldeveloper.

      Out of 270,000+ rows in csv, I managed to load only 187,000+

      Due to a partial error below:

       

      I checked the line (2,530)  but I can not find any invalid format?

      Or how do move cursor the specific (line,column)  on the file? in Windows notepad, word or in Linux VIM?

      Any tips, technics to handle this error?

       

      Please help....

       

      Kind regards,

        • 1. Re: How to import csv in SQLdev
          John Thorton

          was the last time this worked without error?

          What changed since then?

           

          How can we reproduce what you report?

           

          How do I ask a question on the forums?

          • 2. Re: How to import csv in SQLdev
            Gaz in Oz

            The first date "16-AUG-04" looks totally wrong. As do all the other dates in the error message. Where is the century part of the date string?

            How do you know if that date is:

            16-COT-0004 or any other century?

            You will have to look at your data and figure what is wrong with it. Get the producer of the bad csv to recreate it using proper date strings including century, fix their corrupt file and resend it to you.

             

            Note there is a forum dedicated to sqldev questions, although really this is a question about what is wrong with a record in a csv that we can not see.

            For future reference:

            SQL Developer

            • 3. Re: How to import csv in SQLdev
              Beauty_and_dBest

              Thanks ALL,

               

              The sqldev was able to load 187k rows with that date format, so it was able to handle it

              I do not  know  where do I get the bad file? where does it saved it?

              What is that in this sqldev always encounter error like > data is larger that column size, but does not show which column is affected

              It should known the column and display it, why Oracle not so helpful with this tool? to think that I already used 19c? But still a primitive Ai used?

               

               

              Kind regards,

              • 4. Re: How to import csv in SQLdev
                Beauty_and_dBest

                --Insert failed for rows  1  through  1000

                --ORA-01438: value larger than specified precision allowed for this column

                --Row 100

                INSERT INTO test1 (MODEL, PROD_ORDER_NO, SERIAL_NO, PROD_MODEL, LOT_NO, PLAN_PROD_N_START, PLAN_PROD_N_END, BODY_COLOR_CODE, BODY_COLOR, TRIM_COLOR, ACT_PROD_N_START, ACT_PROD_N_END, FM_DATE, PDI_COMP, PDI_CONTROL, VIN_NO, BODY_NO, ENGINE_TYPE, ENGINE_NO, KEY_NO, CS_NO, WB_NO, BATTERY, TIRE_BRAND, FUEL_TYPE, CC, CYLINDER, MAXGVW, AIRCON_NO, AIRCON_BRAND, STEREO_NO, STEREO_BRAND, SALES_ORDER, DEALER_CODE, SCH_DELIVERY, RESERVATION_DATE, PAYMENT, PULLOUT, INVOICE_NO, INVOICE_DATE, AMOUNT, TAX, PAYMENT_TERMS, PAYER, FLEET_ACCT_CODE, FLEET_TYPE, MR_FLAG, MR_PROCESS_DATE, TRAN_ID, CSR_NUMBER, OR_NO, REMARKS, LAST_UPDATE, LAST_USER, ROWVERSION, DOP_DESCRIPTION, CSRDATE, SUBMISSIONDATE) VALUES ('00TFR54HDL-L','000020027052','207042','00TFR54HDL-LS',1.0,to_date('20-SEP-00', 'DD-MON-RR'),to_date('20-SEP-00', 'DD-MON-RR'),752.0,'CREAM WHITE','MUSHROOM GRAY',to_date('21-SEP-00'),to_date('04-OCT-00', 'DD-MON-RR'),to_date('23-SEP-00', 'DD-MON-RR'),to_date(''),'0','PABTFR54HDY207042',5611.0,'','PC2242','N5617','87 KEH','20260','MOTOLITE','GOODYEAR215R15-75','DIESEL',1000000.0,4.0,0.0,'2478906032','SANDEN','0011565','CLARION','5033313','P514',to_date('04-OCT-00', 'DD-MON-RR'),to_date('24-OCT-00', 'DD-MON-RR'),to_date('27-OCT-00', 'DD-MON-RR'),to_date('31-OCT-00', 'DD-MON-RR'),'33147',to_date('24-OCT-00', 'DD-MON-RR'),569227.27,56922.73,0.0,'P514','','','X',to_date(''),'','','','Import from Vehicle 1 for IFS.csv',to_date('07-NOV-00', 'DD-MON-RR'),'IPCSLS2',to_date('04-APR-04', 'DD-MON-RR'),'1',to_date(''),'');

                 

                 

                 

                I even check the actual row 100  and check the size and lenght 1 by 1 as below, but I do not see any larger data size than column size

                 

                 

                Please help....

                • 5. Re: How to import csv in SQLdev
                  B.Delmée

                  just a wild guess based on your screenshot: maybe you have newlines (NL) in your CSV, say between GOODYEAR and 650x14  ?

                  If you load your CSV as plain text in an editor (or run "wc -l" on unix), does it show the same number of lines as excel does ?

                  • 6. Re: How to import csv in SQLdev
                    Gaz in Oz

                    The "bad file" is the file you are trying to load.

                    What is the DDL for the table you are trying to load?

                    Does that insert statement fail with ORA-01438?

                    • 7. Re: How to import csv in SQLdev
                      Beauty_and_dBest

                      I also formated the Data and mapped Vs the table structure and the data is all correct

                       

                        

                      Col#Data
                      1'00TFR54HDL-L',
                      2'000020027052',
                      3'207042',
                      4'00TFR54HDL-LS',
                      51.0,
                      6to_date('20-SEP-00', 'DD-MON-RR'),
                      7to_date('20-SEP-00', 'DD-MON-RR'),
                      8752.0,
                      9'CREAM WHITE',
                      10'MUSHROOM GRAY',
                      11to_date('21-SEP-00'),
                      12to_date('04-OCT-00', 'DD-MON-RR'),
                      13to_date('23-SEP-00', 'DD-MON-RR'),
                      14to_date(''),
                      15'0',
                      16'PABTFR54HDY207042',
                      175611.0,
                      18'',
                      19'PC2242',
                      20'N5617',
                      21'87 KEH',
                      22'20260',
                      23'MOTOLITE',
                      24'GOODYEAR215R15-75',
                      25'DIESEL',
                      261000000.0,
                      274.0,
                      280.0,
                      29'2478906032',
                      30'SANDEN',
                      31'0011565',
                      32'CLARION',
                      33'5033313',
                      34'P514',
                      35to_date('04-OCT-00', 'DD-MON-RR'),
                      36to_date('24-OCT-00', 'DD-MON-RR'),
                      37to_date('27-OCT-00', 'DD-MON-RR'),
                      38to_date('31-OCT-00', 'DD-MON-RR'),
                      39'33147',
                      40to_date('24-OCT-00', 'DD-MON-RR'),
                      41569227.27,
                      4256922.73,
                      430.0,
                      44'P514',
                      45'',
                      46'',
                      47'X',
                      48to_date(''),
                      49'',
                      50'',
                      51'',
                      52'Import from Vehicle 1 for IFS.csv',
                      53to_date('07-NOV-00', 'DD-MON-RR'),
                      54'IPCSLS2',
                      55to_date('04-APR-04', 'DD-MON-RR'),
                      56'1',
                      57to_date(''),
                      58'');

                       

                       

                       

                       

                       

                      Kind regards,

                      • 8. Re: How to import csv in SQLdev
                        Beauty_and_dBest

                        Hi ALL,

                         

                        The "bad file" is the file you are trying to load.

                        What is the DDL for the table you are trying to load?

                        Does that insert statement fail with ORA-01438?

                         

                        Yes thats  exactly the bad file I cut n paste above

                        • 9. Re: How to import csv in SQLdev
                          Beauty_and_dBest

                          Hi ALL,

                           

                          just a wild guess based on your screenshot: maybe you have newlines (NL) in your CSV, say between GOODYEAR and 650x14  ?

                          If you load your CSV as plain text in an editor (or run "wc -l" on unix), does it show the same number of lines as excel does ?

                           

                          You can see in the bad sql that "IS IT NOT BAD" at all.

                          All the data captured is all correct as pick-up.

                          • 10. Re: How to import csv in SQLdev
                            Gaz in Oz

                            So far you have not posted a reproducible sample.

                            Screenshots are not useful as a reproducible sample.

                            Please post table DDL and a sample record that fails.

                            No screenshots,

                            • 11. Re: How to import csv in SQLdev
                              Beauty_and_dBest

                              Hi Gaz,

                               

                              This is the actaul error:

                               

                              --Insert failed for rows  1  through  1000

                              --ORA-01438: value larger than specified precision allowed for this column

                              --Row 100

                               

                              This is the actual insert command, If I run this I will exactly get the same ORA-01438 error.

                               

                              INSERT INTO test1 (MODEL, PROD_ORDER_NO, SERIAL_NO, PROD_MODEL, LOT_NO, PLAN_PROD_N_START, PLAN_PROD_N_END, BODY_COLOR_CODE, BODY_COLOR, TRIM_COLOR, ACT_PROD_N_START, ACT_PROD_N_END, FM_DATE, PDI_COMP, PDI_CONTROL, VIN_NO, BODY_NO, ENGINE_TYPE, ENGINE_NO, KEY_NO, CS_NO, WB_NO, BATTERY, TIRE_BRAND, FUEL_TYPE, CC, CYLINDER, MAXGVW, AIRCON_NO, AIRCON_BRAND, STEREO_NO, STEREO_BRAND, SALES_ORDER, DEALER_CODE, SCH_DELIVERY, RESERVATION_DATE, PAYMENT, PULLOUT, INVOICE_NO, INVOICE_DATE, AMOUNT, TAX, PAYMENT_TERMS, PAYER, FLEET_ACCT_CODE, FLEET_TYPE, MR_FLAG, MR_PROCESS_DATE, TRAN_ID, CSR_NUMBER, OR_NO, REMARKS, LAST_UPDATE, LAST_USER, ROWVERSION, DOP_DESCRIPTION, CSRDATE, SUBMISSIONDATE)

                               

                              This is the actual values which I checked are all correct and do not have exceeding data length that of its target column.

                               

                              VALUES ('00TFR54HDL-L','000020027052','207042','00TFR54HDL-LS',1.0,to_date('20-SEP-00', 'DD-MON-RR'),to_date('20-SEP-00', 'DD-MON-RR'),752.0,'CREAM WHITE','MUSHROOM GRAY',to_date('21-SEP-00'),to_date('04-OCT-00', 'DD-MON-RR'),to_date('23-SEP-00', 'DD-MON-RR'),to_date(''),'0','PABTFR54HDY207042',5611.0,'','PC2242','N5617','87 KEH','20260','MOTOLITE','GOODYEAR215R15-75','DIESEL',1000000.0,4.0,0.0,'2478906032','SANDEN','0011565','CLARION','5033313','P514',to_date('04-OCT-00', 'DD-MON-RR'),to_date('24-OCT-00', 'DD-MON-RR'),to_date('27-OCT-00', 'DD-MON-RR'),to_date('31-OCT-00', 'DD-MON-RR'),'33147',to_date('24-OCT-00', 'DD-MON-RR'),569227.27,56922.73,0.0,'P514','','','X',to_date(''),'','','','Import from Vehicle 1 for IFS.csv',to_date('07-NOV-00', 'DD-MON-RR'),'IPCSLS2',to_date('04-APR-04', 'DD-MON-RR'),'1',to_date(''),'');

                               

                               

                              Kind regards,

                              • 12. Re: How to import csv in SQLdev
                                Mike Kutz

                                It appears that your data doesn't fit the specs you assumed.

                                 

                                If you load the data into a "generic" staging table, you can verify the size of each column prior to placing the data into the actual table.

                                If you can't load the data into a "generic" staging table, then, you have some bad data in your file.  (eg newline characters in a "notes" column is the worst)

                                 

                                • Create a staging table
                                • EXTRACT the data into the staging Table
                                • LOAD the data into there -- if you have problems here, you have a data problem.
                                • VALIDATE the data
                                  • validate the data is the correct data size
                                  • validate that the data is the correct format
                                  • validate that the data can properly be converted
                                • TRANSFORM the data (as needed) as part of an INSERT/SELECT

                                 

                                 

                                Example Staging Tabble

                                create table example_stage (
                                    C001  varchar2(32767),
                                    C002  varchar2(32767),
                                    ...
                                    C200  varchar2(32767),  -- or, however many you need
                                    seq_id  int generated by default on null as identity
                                );
                                

                                 

                                Creating full validation code is tedious but very formulaic.  (ie you can create code to write the code)

                                If you want a large test, use a template based code generator.  (I believe jk64 has a Validator component)

                                 

                                My $0.02

                                 

                                MK

                                • 13. Re: How to import csv in SQLdev
                                  Beauty_and_dBest

                                  Thanks,

                                   

                                  The solution was to use sqlloader on Linux.

                                  It displays the column affected.

                                   

                                  Regards,