Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

4 Answers

up vote 1 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

OR

With macro, VB code:

Sheet1.UsedRange.SpecialCells(xlCellTypeBlanks)="NULL" 
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
add comment

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:

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.

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
add comment

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
add comment

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()
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.