How to write "NULL" into CSV from Excel for blank fields
How do I write "NULL" for blank fields when I export a CSV file from Excel 2007? Is there a feature to do that?
15 Answers
While exporting, i think it may not be possible.
But you can also try this way before saving or after saving
1. Click F5
2. Click Special
3. Select Blanks
4. Click OK
5. Type NULL
6. Press CTRL + EnterOR
With macro, VB code:
Sheet1.UsedRange.SpecialCells(xlCellTypeBlanks)="NULL" Open the CSV file in a text editor, such as Notepad, and do a find/replace all ,, => ,NULL,. It's not within Excel, but it will still work.
I know this works in newer Excel; not sure about the older version.
- Select Cell
A1; then shift click the most bottom right selection - Press Ctrl+H
- In the "Find" field leave it blank
- in the "Replace With" field put in
NULL
This should replace all the nothings with 'NULL' in the selected cells.
Short answer: You don't do anything.
There is no difference between an Excel cell with an empty string or one with no value (null) for purposes of exporting a CSV. The fields in CSV files don't actually have data types. So you will get an empty field in your CSV file either way. Example:
Will generate a CSV that contains:
12,,54Nothing between the two delimiters (commas) means empty or null. How the empty value is dealt with depends on the software that is reading the CSV file.
2I don't know Excel but this python script will do it
import csv,sys
fin = open(sys.argv[1],'rb')
fout = open(sys.argv[2],'wb')
reader = csv.reader(fin, delimiter=',', quotechar='"')
writer = csv.writer(fout, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
for row in reader: new = [] for s in row: if s=='': new.append('null') else: new.append(s) writer.writerow(new)
fin.close()
fout.close()
