4

How do I write "NULL" for blank fields when I export a CSV file from Excel 2007? Is there a feature to do that?

  • Null meaning nothing there, or null meaning something else? – soandos Feb 15 '12 at 2:12
5

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" 
  • 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
1

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.

  • 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 '16 at 21:48
1

I know this works in newer Excel; not sure about the older version.

  1. Select Cell A1; then shift click the most bottom right selection
  2. Press Ctrl+H
  3. In the "Find" field leave it blank
  4. in the "Replace With" field put in NULL

This should replace all the nothings with 'NULL' in the selected cells.

0

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.

  • 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
  • 2
    Sorry you didn't say that or you would have gotten a better answer. – Dave Becker Feb 15 '12 at 5:24
-1

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()
  • 1
    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 '16 at 6:44
  • Offtopic ? anyways, requires install Python in Windows (not chocolatey ) – Kiquenet Feb 8 '17 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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