We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to fill blank cells in range with NULL

asked 2020-08-03 19:16:00 +0200

kasloman gravatar image

I have a csv file with 30 fields and 500 rows with many blank cells. I create a range of all cells. How do i then search for blank cells and replace the blank with NULL?

edit retag flag offensive close merge delete


Do you mean word "NULL"? Or "value" NULL (empty cell)?

JohnSUN gravatar imageJohnSUN ( 2020-08-03 19:38:52 +0200 )edit

I am trying to produce a csv file suitable for MySql to import. As an example to follow I exported a tables data from MySql in csv format. Any field with no value is shown in the csv file as NULL with no quotes. I am trying to emulate that so that MySql will import the file. I also will be doing this for SQLite3.

kasloman gravatar imagekasloman ( 2020-08-03 20:46:41 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-08-03 19:55:43 +0200

keme gravatar image

updated 2020-08-03 20:01:28 +0200

Not sure what you are asking here. The term NULL can be used to mean a number of different things. Also, "csv file" can mean a file you have opened for editing in a plaintext editor (with separator characters visible) or in a spreadsheet application (Calc), or a file you want to modify programmatically without manual editing.

The blank cell is Calc's best representation of NULL (or VOID, i.e. no value, different from number zero and different from an empty text string). Sadly, comparing an empty cell to either of those non-null "zero" values will yield a match. The only sure fire test is the ISBLANK() function.

Also, a Calc formula cannot return such a NULL value. The usual choice for simulating that is the #N/A error, which can be generated by the NA() function and tested for with ISNA().

To insert the text "NULL" into all empty cells, the easiest way is to do it backwards:

  • Create an empty sheet.
  • Enter NULL in one cell, then copy that cell.
  • Select a range large enough to hold your data
  • Paste the NULL to the range
  • Go back to your data sheet
  • Copy all (ctrl+A, ctrl+C)
  • Select your "nullified sheet"
  • Paste. Accept the overwrite.
edit flag offensive delete link more


And also Ctrl+H


JohnSUN gravatar imageJohnSUN ( 2020-08-03 20:08:41 +0200 )edit

@keme: imho paste special with 'skip empty' will work better, trying your recipe overpastes the 'NULL' text cells with blank cells from the data sheet ...

newbie-02 gravatar imagenewbie-02 ( 2020-08-03 20:12:26 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-03 19:16:00 +0200

Seen: 551 times

Last updated: Aug 03 '20