Solution for Generating CSV file from Oracle DB
is Given Below:
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 ?
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
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);