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?
Do you mean word “NULL”? Or “value” NULL (empty cell)?
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.
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.
And also Ctrl+H
@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 …