Generating CSV file from Oracle DB

Question

I am trying to generate CSV file from my oracle DB and send it as an attachment via mail . I have an issue with the CSV file generated. I could see that certain values from the table is not read properly. Like the value in description column is written in CSV file in two different cells instead of a single cell .

you can see that a value is written in two cells instead of a single cell as rest of the values. Here 'Testing pls ignore mails' should be treated as a single value and should be in the same cell .

I noticed one more thing that the value is in the table as follows:

could this be because my code is unable to read the value in multiple lines ?

Here is my code for creating the CSV file and the field FC_ED_DESC is causing the problems and I am including " l_clob " in the attachment section of the code that I have written for mailing .

   DECLARE
        l_clob CLOB;
        l_attach_text VARCHAR2 (32767);
        l_attach_text_h VARCHAR2 (32767);
        FC_SV_STATUS_DESC VARCHAR2(200) := 'open';
    
        -- select query from table to get the values needed
        CURSOR c1 IS 
           select FC_ED_RECORD_ID,to_char(FC_ED_UPLOADTIME,'DD.MM.YY')FC_ED_UPLOADTIME,FC_ED_USER_ID ,FC_ED_BROKER,FC_ED_ACT_NUM,FC_ED_POLICY_NUM,FC_ED_AMOUNT,FC_ED_TRANS_TYPE,FC_ED_CURRENCY,to_char(FC_ED_DUE_DATE,'DD.MM.YY')FC_ED_DUE_DATE,FC_ED_SENDER_NAME,FC_ED_DESC,to_char(FC_ED_CREDIT_DATE,'DD.MM.YY')FC_ED_CREDIT_DATE from  MYTABLE where FC_ED_EXPCASH_STATUS = 1 ;   
        
    BEGIN   
        -- csv file columns are these
        l_attach_text_h :=
        'FC_ED_RECORD_ID ,FC_ED_UPLOADTIME ,FC_ED_USER_ID ,FC_ED_BROKER ,FC_ED_ACT_NUM ,FC_ED_POLICY_NUM ,FC_ED_AMOUNT ,FC_SV_STATUS_DESC ,FC_ED_TRANS_TYPE ,FC_ED_CURRENCY ,FC_ED_DUE_DATE ,FC_ED_SENDER_NAME ,FC_ED_DESC ,FC_ED_CREDIT_DATE';   
        
        FOR employee_rec in c1    
        LOOP
            DBMS_OUTPUT.put_line('Before loop COUNT Boss ...'||c1%ROWCOUNT);
            -- each value is read using loop   
            l_attach_text :=                                  
            employee_rec.FC_ED_RECORD_ID ||','||
            employee_rec.FC_ED_UPLOADTIME ||','||
            employee_rec.FC_ED_USER_ID ||','||
            employee_rec.FC_ED_BROKER ||','||
            employee_rec.FC_ED_ACT_NUM ||','||
            employee_rec.FC_ED_POLICY_NUM ||','||
            employee_rec.FC_ED_AMOUNT ||','||
            FC_SV_STATUS_DESC ||','||
            employee_rec.FC_ED_TRANS_TYPE ||','||
            employee_rec.FC_ED_CURRENCY ||','||
            employee_rec.FC_ED_DUE_DATE ||','||
            employee_rec.FC_ED_SENDER_NAME ||','||
            employee_rec.FC_ED_DESC ||','||
            employee_rec.FC_ED_CREDIT_DATE ||chr(13);
   
            l_clob := l_clob || l_attach_text;    
        END LOOP;

        -- adding values
        l_clob := l_attach_text_h ||chr(13)|| l_clob;

Can anyone please help me with this ?

does this answer your question: https://stackoverflow.com/questions/10546933/inserting-multiline-text-in-a-csv-field — pmdba, Aug 02 '21 at 15:01
can you please tell how can i bring that solution in my code — TJ32, Aug 02 '21 at 18:07

EJ Egyed · Accepted Answer · 2021-08-02T18:19:21.607

3

The problem is that you are not surrounding your fields with double quotes. Anything that contains a carriage return will be read as a new record in your CSV file.

Since you are using SQL Developer, the easiest way to generate a CSV file is to use the /*csv*/ hint in your query. This will properly quote all the fields.

Put this code into SQL Developer and execute it using F5 to run it in Script mode and you should get a properly quoted CSV output in DBMS_OUTPUT.

SELECT /*csv*/
       FC_ED_RECORD_ID,
       TO_CHAR (FC_ED_UPLOADTIME, 'DD.MM.YY')      FC_ED_UPLOADTIME,
       FC_ED_USER_ID,
       FC_ED_BROKER,
       FC_ED_ACT_NUM,
       FC_ED_POLICY_NUM,
       FC_ED_AMOUNT,
       FC_ED_TRANS_TYPE,
       FC_ED_CURRENCY,
       TO_CHAR (FC_ED_DUE_DATE, 'DD.MM.YY')        FC_ED_DUE_DATE,
       FC_ED_SENDER_NAME,
       FC_ED_DESC,
       TO_CHAR (FC_ED_CREDIT_DATE, 'DD.MM.YY')     FC_ED_CREDIT_DATE
  FROM MYTABLE
 WHERE FC_ED_EXPCASH_STATUS = 1;

Another option is to just run the query you want in SQL Developer, then highlight the results you want to export (or press Control + A), then right click and select Export and choose your Format to be CSV

Update

To fix your existing code, put double quotes around each of the fields you are combining into your l_attach_text variable. See example below.

    l_attach_text :=       '"' ||                           
            employee_rec.FC_ED_RECORD_ID ||'","'||
            employee_rec.FC_ED_UPLOADTIME ||'","'||
            employee_rec.FC_ED_USER_ID ||'","'||
            employee_rec.FC_ED_BROKER ||'","'||
--          ...(the rest of your fields here)
            employee_rec.FC_ED_DESC ||'","'||
            employee_rec.FC_ED_CREDIT_DATE || '"' || chr(13);

edited Aug 02 '21 at 18:19

answered Aug 02 '21 at 16:23

EJ Egyed

5,791
1
8
23

thanks for the answer ....BUT Here I am generating a CSV file and Attaching it to mail . I have written separate code for mailing and from my above code I am including " l_clob " in the attachment section ..... So I get a CSV file as an attachment via mail – TJ32 Aug 02 '21 at 18:00
"The problem is that you are not surrounding your fields with double quotes. Anything that contains a carriage return will be read as a new record in your CSV file " - can you please tell me what changes should I do – TJ32 Aug 02 '21 at 18:08
1

Just added an Update. See the bottom of the edited answer. – EJ Egyed Aug 02 '21 at 18:19
And each column should be processed with `replace(column_, '"', '""')` before adding quotes to avoid unexpected field brakes – astentx Aug 02 '21 at 19:23
Correct. you will also need to handle any `"` that appear in your data. – EJ Egyed Aug 02 '21 at 19:27
@astentx can you please tell me where exactly i should use that – TJ32 Aug 03 '21 at 08:40
1

@TJ32 Before you enclose each column with quotes. Just try to export as CSV any spreadsheet with linebreaks, quotes, semicolons (or whatever delimiter you use) inside the cell. Then check how exported file is formatted – astentx Aug 03 '21 at 09:16

Generating CSV file from Oracle DB

1 Answers1

Update