Home » SQL & PL/SQL » SQL & PL/SQL » loading data into table from csv file with spaces between data
loading data into table from csv file with spaces between data [message #303913] Mon, 03 March 2008 05:04 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I have to read a csv file and load the data into a table.

The table layout fits the number of delimited fields in the csv file. One thing to notice is that some of the text fields have quotes that I require to ignore when loading the data.

For example….

"2008 P02",001126,"KMART HOLDING CORPORATION ",

It needs to be Loaded as 2008 P02 into field SOP_PERIOD, 001126 into field CUSTOMER_ID, KMART HOLDING CORPORATION into field CUSTOMER_NAME and so forth………

I have earlier tried to load data to table but with quotes.
Please suggest me a way to load data into table where the records have spaces between them as given above and without quotes.

Thanks,
Soni
Re: loading data into table from csv file with spaces between data [message #303918 is a reply to message #303913] Mon, 03 March 2008 05:18 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
show us what you tried
Re: loading data into table from csv file with spaces between data [message #303920 is a reply to message #303913] Mon, 03 March 2008 05:24 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi,

If you are using sql*loader then search in the sql*loader manual for optional keyword.

HTH

Regards

Raj
Re: loading data into table from csv file with spaces between data [message #303921 is a reply to message #303918] Mon, 03 March 2008 05:25 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

The code is big
DECLARE
  inputdir          VARCHAR2(100) := '&1';
  inputfile         VARCHAR2(100) := '&2';
  inputhandle       UTL_FILE.FILE_TYPE;
  inputbuff         VARCHAR2(32767);
  n_rec_seq         NUMBER := 0;
  v_str             VARCHAR2(2000);
  n_pos             NUMBER := 1;
  n_rec_cnt         NUMBER := 0;
  v_temp_inputbuff  VARCHAR2(32767);
  n_first_pos       NUMBER :=1;
  n_second_pos      NUMBER ;
  n_last_pos        NUMBER ;
  n_end_pos         NUMBER ;
  n_err_cnt         NUMBER :=0;
  n_col_cnt         NUMBER :=0;

  PROCEDURE insert_rpt_err AS
    v_report        VARCHAR2(50) := 'SOP SUPPLEMENTAL SHEET ERRORS';
    v_item          VARCHAR2(25);
    v_dest          VARCHAR2(100);
    v_prog          VARCHAR2(100);
    v_type          VARCHAR2(100);
    v_size          VARCHAR2(100);
    v_color         VARCHAR2(100);
    v_detail        VARCHAR2(50);

  BEGIN

    n_first_pos := 1;
    n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos);
    v_item := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos,
                                  n_second_pos - 1),'"',''));

    n_first_pos :=  n_second_pos ;
    n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1);
    v_dest := TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
                                  n_second_pos - n_first_pos -1 ),'"',''));

    n_first_pos :=  n_second_pos ;
    n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1);

    n_first_pos := n_second_pos ;
    n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1);
    v_prog :=  TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
                                   n_second_pos - n_first_pos -1 ),'"',''));

    n_first_pos := n_second_pos ;
    n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1) ;
    v_type :=  TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
                                   n_second_pos - n_first_pos -1),'"',''));
    n_first_pos := n_second_pos ;
    n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1) ;
    v_size :=  TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
                                   n_second_pos - n_first_pos -1),'"',''));
    n_first_pos := n_second_pos ;
    n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1) ;
    v_color :=  TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
                                    n_second_pos - n_first_pos -1),'"',''));
    n_first_pos := n_second_pos ;
    n_second_pos := INSTR(v_temp_inputbuff,',',n_first_pos + 1) ;
    v_detail :=  TRIM(REPLACE(SUBSTR(inputbuff, n_first_pos + 1,
                                     n_second_pos - n_first_pos -1),'"',''));

    insert_info_rpt(v_report, v_item, v_dest, v_prog, v_type, v_size,
                    v_color, v_detail, NULL, NULL, NULL, NULL, NULL,
                    NULL, NULL, NULL, NULL, NULL
                    );
    n_err_cnt := n_err_cnt + SQL%ROWCOUNT;
  END insert_rpt_err;

BEGIN
  --Truncate the sop_supplemental_data table prior to load
  DBMS_OUTPUT.PUT_LINE('..Truncating sop_supplemental_data table');
  EXECUTE IMMEDIATE 'TRUNCATE TABLE zrobi10.sop_supplemental_data';

  --Opening .csv file
  inputhandle := UTL_FILE.FOPEN(inputdir, inputfile, 'r');
  n_rec_seq := 0;

  <<MAIN_LOOP>> LOOP
  BEGIN
    UTL_FILE.GET_LINE(inputhandle, inputbuff);
    inputbuff := TRIM(inputbuff);
    n_rec_seq := n_rec_seq + 1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    EXIT MAIN_LOOP;
  END;

  BEGIN
    v_temp_inputbuff := inputbuff;
    n_pos := 1;

    FOR i in 1..63
    LOOP
      n_first_pos := INSTR(v_temp_inputbuff,'"',n_pos) ;
      IF n_first_pos = 0 then
        EXIT;
      END IF;
      n_second_pos := INSTR(v_temp_inputbuff,'"',n_first_pos+1);
      v_temp_inputbuff:= SUBSTR(v_temp_inputbuff,1,n_first_pos)||
                         REPLACE(SUBSTR(v_temp_inputbuff,
                                         n_first_pos + 1,
                                         n_second_pos - n_first_pos
                                       ),'"',' ') ||
                         SUBSTR(v_temp_inputbuff,n_second_pos + 1);
      n_pos := n_second_pos + 1;
    END LOOP;

    FOR i in 2..15
    LOOP
      n_pos := INSTR(v_temp_inputbuff,',',n_pos + 1) ;
    END LOOP;

    v_str := TRIM(SUBSTR(inputbuff,1,n_first_pos - 1 ))||
               TRIM(SUBSTR(inputbuff,n_pos + 1,
                                     n_second_pos -  n_pos - 1));

    n_first_pos := n_second_pos ;
    n_second_pos := INSTR(v_temp_inputbuff,'"',n_first_pos + 1) ;
    n_first_pos := INSTR(v_temp_inputbuff,'"',n_second_pos + 1) ;

    n_last_pos := n_pos;
    FOR i in 16..63
    LOOP
      n_last_pos := INSTR(v_temp_inputbuff,'"',n_last_pos + 1);
    END LOOP;

    n_end_pos := INSTR(v_temp_inputbuff,'"',n_last_pos + 1) ;

    IF n_end_pos = 0 THEN
      v_str := v_str || REPLACE(REPLACE(REPLACE(REPLACE(
                                SUBSTR(v_temp_inputbuff,
                                       n_first_pos +1)
                                                  ,'"', ' ')
                                           ,' ', ' ')
                                    ,'(', '-')
                            , ')','');
    ELSE
      v_str := v_str || REPLACE(REPLACE(REPLACE(REPLACE(
                                SUBSTR(v_temp_inputbuff,
                                       n_first_pos +1,
                                       n_end_pos - n_first_pos - 1)
                                                    ,'"',' ')
                                            ,' ',' ')
                                      ,'(','-')
                            , ')','');
    END IF;

    dbms_output.put_line('v_str:'||v_str);

    EXECUTE IMMEDIATE 'INSERT INTO zrobi10.sop_supplemental_data
                      (sop_period, customer_id, customer_name,
                       channel, business, prod_group, 
                       program, prod_type, cloth_type, 
                       ord_pol, demand_type, make_buy,
                       facility, country, data_source, 
                       jan_units_cy, jan_amount_cy, feb_units_cy, 
                       feb_amount_cy, mar_units_cy, mar_amount_cy, 
                       apr_units_cy, apr_amount_cy, may_units_cy, 
                       may_amount_cy, jun_units_cy, jun_amount_cy, 
                       jul_units_cy, jul_amount_cy, aug_units_cy, 
                       aug_amount_cy, sep_units_cy, sep_amount_cy, 
                       oct_units_cy, oct_amount_cy, nov_units_cy, 
                       nov_amount_cy, dec_units_cy, dec_amount_cy, 
                       jan_units_cy, jan_amount_cy, feb_units_cy, 
                       feb_amount_cy, mar_units_cy, mar_amount_cy, 
                       apr_units_cy, apr_amount_cy, may_units_cy, 
                       may_amount_cy, jun_units_cy, jun_amount_cy, 
                       jul_units_cy, jul_amount_cy, aug_units_cy, 
                       aug_amount_cy, sep_units_cy, sep_amount_cy, 
                       oct_units_cy, oct_amount_cy, nov_units_cy, 
                       nov_amount_cy, dec_units_cy, dec_amount_cy)
                      VALUES ('||v_str||')';

     EXCEPTION WHEN OTHERS THEN
       insert_rpt_err;
     END;

  END LOOP MAIN_LOOP;

  n_rec_cnt := n_rec_cnt + n_rec_seq;
  UTL_FILE.FCLOSE(inputhandle);

  COMMIT;
END;


Can I attach the csv file! I do not have to use sql*Loader.

[Updated on: Mon, 03 March 2008 05:27]

Report message to a moderator

Re: loading data into table from csv file with spaces between data [message #303922 is a reply to message #303921] Mon, 03 March 2008 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Loader or external table.

Regards
Michel
Re: loading data into table from csv file with spaces between data [message #303925 is a reply to message #303922] Mon, 03 March 2008 05:32 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Please suggest me something in the way I am trying to do this as I do not have to do this using sql*loader or external table according to specifications.
Re: loading data into table from csv file with spaces between data [message #303927 is a reply to message #303925] Mon, 03 March 2008 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But SQL*Loader and external ARE the way to do it.
PL/SQL is the wrong way.
Please suggest to modify the specifications.
I doubt the specifications say to do it in PL/SQL, I bet they just say you have to load the data.

Regards
Michel
Re: loading data into table from csv file with spaces between data [message #303930 is a reply to message #303927] Mon, 03 March 2008 05:47 Go to previous messageGo to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Michel,
I am going to write a mail to ask if I can do it through loader/external table. Before this please let me know if I can really do this using them as I am new to loader/external table.

My csv file has data like :
For example….

"2008 P02",001126,"KMART HOLDING CORPORATION ",


It needs to be Loaded as 2008 P02 into field SOP_PERIOD, 001126 into field CUSTOMER_ID, KMART HOLDING CORPORATION into field CUSTOMER_NAME and so forth………

Please let me know if we can easily o this using external table/loader.....so that I can write a mail for specifications.
I just want to be sure before saying that I can do this using sql*loader.

Thanks,
Soni

[Updated on: Mon, 03 March 2008 06:19] by Moderator

Report message to a moderator

Re: loading data into table from csv file with spaces between data [message #303938 is a reply to message #303930] Mon, 03 March 2008 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is very easy to write this with an external table or SQL*Loader. It is basic syntax.

Regards
Michel
Re: loading data into table from csv file with spaces between data [message #304431 is a reply to message #303938] Wed, 05 March 2008 03:14 Go to previous message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Thanks a lot Michel,
I have tried it through sql*Loader and it has been done so simply and approved by client.

Thanks again for your good advice.

Regards,
Soni
Previous Topic: The OR operator
Next Topic: How call java parameters from PL/SQL procedure
Goto Forum:
  


Current Time: Tue Dec 06 08:10:09 CST 2016

Total time taken to generate the page: 0.27275 seconds