Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
Null meaning nothing there, or null meaning something else? – soandos Feb 15 '12 at 2:12
up vote 4 down vote accepted

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


With macro, VB code:

share|improve this answer
Add a second line to the macro: ActiveWorkbook.SaveAs FileFormat:=xlCSV, CreateBackup:=False -- The macro will now search and replace with "NULL" and save the file, all in one step. – Dave Becker Feb 15 '12 at 5:27

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=='':
share|improve this answer
Your Python skills are commendable, but what does this have to do with the question that was asked? – Dave Becker Feb 15 '12 at 2:35
It would solve the problem a different way. I just had that script laying around and though OP would find it useful. – Nathan Feb 15 '12 at 10:43
This is helpful, btw. Shouldn't be downvoted. – Aditya Jan 12 at 6:44

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:

enter image description here

Will generate a CSV that contains:


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.

share|improve this answer
My question was is there a way to write the word "NULL" for each empty field from Excel. – user823527 Feb 15 '12 at 3:15
Sorry you didn't say that or you would have gotten a better answer. – Dave Becker Feb 15 '12 at 5:24

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.

share|improve this answer
Just a warning: This doesn't handle quotes or cells at the beginning or end of each line. Also if there are multiple empty cells next to each other you have to do it twice. – user988346 Jun 30 at 21:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .