How to write null into csv from Excel for blank fields

Question

How do I write null for blank fields when I export a csv file from Excel 2007? Is there a feature to do that.

Answer 1

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 + Enter

OR

With macro, VB code:

Sheet1.UsedRange.SpecialCells(xlCellTypeBlanks)="NULL" 

Answer 2

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,,54

Nothing 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.

Answer 3

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.

Answer 4

I 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()