Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Nodata in Excel
 Login/Join
 
Gold member
posted
Does anyone have a solution to setting NODATA in EXCEL (EXL2K is not an option yet). I'm getting 'NA' in all of the missing cells and I need it to just be a null value instead.
 
Posts: 90 | Registered: April 15, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
yep
i do this
SET NODATA = ''
no space between the quotes
By default NODATA comes out as a dot
SET NODATA='.'
That's what you see when data are missing.
But a dot in an excel sheet is a messy problem.
(You indicated you had SET NODATA='NA' or something like that.)
So for any output that will be excel, i use this command at the top of the fex.
then i get empty cells, truly empty. Excel is happy with that.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
You know, I've never been so mad at a program as I am at this one...SET NODATA='' seemingly worked yesterday, but now I'm getting NA in the empty cells even after setting nodata to the two tick marks. It is frustrating as all get out and I can't decide if someone re-configured my Excel version on me or not. Guess I'll keep trying things, but it does drive me crazy because I've done computes where I set it to '' or missing or whatever and it still won't come up as a plain ol' empty cell.
 
Posts: 90 | Registered: April 15, 2004Reply With QuoteReport This Post
<monte2000>
posted
Speaking to Excel guru here at IBI...have you tried going into "Tools" on your Excel menu and un-checking "View Zero Values"? Just curious if you've tried this....
 
Reply With QuoteReport This Post
Gold member
posted Hide Post
Just tried it and still stuck with the NA in the field...Have debated changing the value to be zero when missing and seeing if that will work, but I have values in these that can be equal to zero so I'd probably cause myself more headaches doing that. Am going to see what happens in LOTUS format also.
 
Posts: 90 | Registered: April 15, 2004Reply With QuoteReport This Post
Silver Member
posted Hide Post
Also, make sure you're not just re-using a cached copy of the file. When you use a browser, it may see a recently cached copy of a request and use it, so the report doesn't actually get re-run and you just open up a file that has already be generated.
 
Posts: 40 | Registered: March 10, 2004Reply With QuoteReport This Post
Gold member
posted Hide Post
Just FYI, I did reset my missings to a zero and when it came up I was able to see the zeros and turn off zeros in options which took them away, but this still doesn't solve my issue because I can have legitimate zeros in the column anyway and I also need the blanks to truly reflect a null value and not a zero.
 
Posts: 90 | Registered: April 15, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Stan, preview your output in a text file...to see if SET NODATA='' is working.
If your Text file is ok, then its not focus, its excel somehow.
If your text file has NA in it, then try a new really small focexec, in a very controlled environment, and set your nodata to '' and see if you can make a file that's right. If you can, then perhaps go back to your original fex and see whats going on.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
Susannah,

It turns out it is Excel that is indeed the problem. Excel from 1997 doesn't know how to handle null values so it places an NA in there when you use the DIF format. It blows away if you try EXL97 and when I use the EXCEL format it doesn't recognize what is being passed a truly null, and places spaces in the cell to take up the space. We are going to have a macro written to handle the DIF file format, but now I'm trying to figure the best solution to cause that macro to be called...I'm told I can have a VBA program call the macro and Excel and find a File that is saved, but it really isn't that ideal of a solution. It is an interesting program otherwise in that it never touches a database, but simply parses out a flat file into user defined columns and at the same time eliminates headers, limits on user defined phrases and the like. Unfortunately the users take the parsed file and apparently run these macros that expect null value in the cells rather than spaces.

Thanks again for everyone's suggestions.

Stan
 
Posts: 90 | Registered: April 15, 2004Reply With QuoteReport This Post
Gold member
posted Hide Post
For the record, I found a workaround by setting all of the space in an empty cell equal to the Hex for low values '00' and in the DIF format this appears to be a null cell.

Just wanted to add the solution in case someone searches on a similar topic in the future.

Stan
 
Posts: 90 | Registered: April 15, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.