1

I Have sample CSV file which contains 10 records.

So I want to upload the CSV file Thru stored procedure. Is it possible to do that way. This is my stored function.

FOR i IN 1..v_cnt LOOP

    SELECT idx_date,file_path INTO v_idx_date,v_file_path FROM cloud10k.temp_idx_dates
    WHERE is_updated IS FALSE LIMIT 1;

    COPY cloud10k.temp_master_idx_new(header_section) FROM v_file_path;

    DELETE FROM cloud10k.temp_master_idx_new WHERE header_section NOT ILIKE '%.txt%';

    UPDATE cloud10k.temp_master_idx_new SET CIK          = split_part( header_section,'|',1),
                                            company_name = split_part( header_section,'|',2),
                                            form_type    = split_part( header_section,'|',3),
                                            date_filed   = split_part( header_section,'|',4)::DATE,
                                            accession_number = replace(split_part(split_part( header_section,'|',5),'/',4),'.txt',''),
                                            file_path    = to_char(SUBSTRING(SPLIT_PART(v_file_path,'master.',2) FROM 1 FOR 8)::DATE,'YYYY')
   ||'/'||to_char(SUBSTRING(SPLIT_PART(v_file_path,'master.',2) FROM 1 FOR 8)::DATE,'MM')
   ||'/'||to_char(SUBSTRING(SPLIT_PART(v_file_path,'master.',2) FROM 1 FOR 8)::DATE,'DD')
   ||'/'||CONCAT_WS('.','master',SPLIT_PART(v_file_path,'master.',2) )

    WHERE header_section ILIKE '%.txt%';
 END LOOP;

But its not executing. Can someone suggest me how to do that..

Tanks, Ramesh

e4c5
  • 52,766
  • 11
  • 101
  • 134
Ram
  • 199
  • 1
  • 2
  • 18
  • Check this link http://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table – Santhucool May 10 '16 at 12:53
  • As per the link execute format('copy temp_table from %L with delimiter '','' quote ''"'' csv ', csv_path); There are inserting multiple columns but my scenario is want to insert only one column – Ram May 10 '16 at 13:08
  • So if i remove delimiter its not executing – Ram May 10 '16 at 13:10
  • delimiter is the separator used to differentiate fields in csv. The query simply getting values using delimiters. Check what is your delimiter. Try open your file using notepad and analyse your delimiter. You need to change the query in accordance with your delimiter. – Santhucool May 10 '16 at 13:16
  • Break down the problem: first, does `COPY cloud10k.temp_master_idx_new(header_section) FROM '/blah/blah/path';` work if you hard-code the path? If so, you want to replace that hard-coded path with a placeholder; given the example you've already found, the placeholder is `%L`, which you give as part of a string to a function called `FORMAT()` and then execute with the command `EXECUTE`. – IMSoP May 10 '16 at 16:39

0 Answers0