How to export data from spss to excel with out nulls

excel spss
user2543786 picture user2543786 · Feb 5, 2014 · Viewed 8k times · Source

I have a large data set in SPSS (v20) with null values for some observations.

I tried saving as an excel 2007 file, but when I open excel file "#NULL!" appears in cells where values are null. I'm going out of ram when trying to use 'find and replace' function in Excel.

T tried saving as a csv file then I got a space in the cells where values are null.

Could anybody advice on this please?

Answer

dra_red picture dra_red · Mar 9, 2014

I typically save as CSV and then in excel save as .xlsx. All missing values are then, as you noted, allocated a space which I accept as representing sysmis values.

When I work with a file that has been saved directly to Excel, (ie many "#NULL" values), I use a VBA macros which does a find/replace row by row. The macro is quicker than doing it all at once as this typically starts to slow to infuriating speeds. The macro is still not as fast as one would want...which is why I go via CSV.

Related questions

How to open spss data files in excel?

I want to open spss .sav data files in excel without open the spss files(i don't want to convert spss data file into excel file), I know this is possible using OLDB connection, but i don't know how to …

Read More
Efficient way for converting Yes/No to 1/0 in Excel or SPSS

I am trying to find an efficient way for converting yes/no to 1/0 in Excel. Since I am doing the data analyzing at SPSS. Or if there is a way converting YES/NO to 1/0 in SPSS directly?

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More