Member Avatar for dippy78

Hi,

Need help on exporting data to csv from oracle database
when i export ...
the field which has null values (in database) appear as 'null' word in the csv output.
Is there a way to avoid it?

thanks

  • 4 Contributors
  • 4 Replies
  • 330 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jwenting

Dani AI

Generated

Most likely cause: the exporter (or code that formats rows) is converting SQL NULL into the textual token "null" rather than leaving an empty CSV field. As noted, NULL is a distinct database state; as asked, the correct action when NULLs should become empty CSV fields is to replace them with empty strings at either the SQL or the exporter layer.

SQL-level fixes (recommended when producing simple exports)

SELECT NVL(varchar_col, '')     AS varchar_col,
       NVL(TO_CHAR(num_col), '') AS num_col,
       NVL(TO_CHAR(date_col,'YYYY-MM-DD'), '') AS date_col
FROM my_table;

Or ANSI: COALESCE(col, ''). For SQL*Plus-style spooling, set the NULL substitution before SPOOL:

SET NULL ''
SPOOL out.csv
... SELECT ...
SPOOL OFF

Java-level fixes (recommended when generating CSV from JDBC)

String val = rs.getString("col");               // may be null
val = Objects.toString(val, "");                // returns "" when null
// avoid String.valueOf(rs.getObject("col")) — that produces "null"
writer.write(escapeCsv(val));                   // use a proper CSV escape method

Alternatives: org.apache.commons.lang3.StringUtils.defaultString(...). Always avoid calling toString/String.valueOf on nullable objects if the empty field is required.

Practical notes and caveats

  • Post-processing with sed (as suggested) can work but is brittle: it will corrupt quoted fields containing the literal "null" or commas/newlines.
  • Best practice is to handle NULLs during SELECT or while writing CSV with a tested CSV library (OpenCSV, Apache Commons CSV) so quoting/escaping and NULL semantics are handled correctly.
  • For numeric/date columns, convert to text (TO_CHAR) before replacing NULL to avoid type errors.

Recommended Answers

All 4 Replies

Member Avatar for JamesCherrill

What do you want it to do when it encounters a null value in the database?

Member Avatar for dippy78

hi,

I want it go as blank value ",," and not as ",null," values.

Thanks

Member Avatar for rubberman

Run it through a sed script to remove the "null" entries.

Member Avatar for jwenting

NULL is a real value in SQL, hence it gets exported as a real value.
If you want it replaced with something else, adjust the method that writes the output to do the replacement...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.