Null value Excel

[MikeC]

Hi,
I'm exporting a dbf table to Excel via AlphaDAO. A Numeric field, of course will show a zero if the field has ever been "disturbed" in Alpha. I can easily run an xbasic routine to set the fields with a 0 to be null in the Alpha dbf table dependent upon whether a certain field has data.

I would like to have the same happen in the Excel file. Whether the field is blank or zero in the Alpha table, it shows up in Excel as a zero---I want it to be blank (not show the zero) when a certain Excel column has data.

So....
Can this be done inside of Excel (with a formula perhaps?)
Can this be accomplished via Alpha either prior to or after export?

Now I'll see just how many Excel gurus there are here in Alphaland (besides Stan of course!).

[G Gabriel]

Re: Null value Excel

In Excel:
Go to the Tools menu, click Options, click the View tab, uncheck zero.

[MikeC]

Re: Null value Excel

Thanks G.....but that will make all the zeroes become blank instead of just the specific column that when having data become blank when zero.

Here is a screenshot to make it hopefully a bit more clear.

[G Gabriel]

Re: Null value Excel

Create a column next to the ones with the values then put this formula:
=IF(AND(C1=0,A1<>""),"",C1)
Then hide the column (In this formula that would be column C)

[MikeC]

Re: Null value Excel

Close for a single column---except for the very first which is basically a header and needs to remain the same, each cell would need such a formula---what actually works for a specific cell such as the 2nd row down is:
=IF(A2<>"","",C2)



EDIT---cross-postings--I will check out your new formula.....

[G Gabriel]

Re: Null value Excel

The formula is:
=IF(AND(C1=0,A1<>""),"",C1)
Copy it to all pertinent rows.

[MikeC]

Re: Null value Excel

So if I knew what rows were to be there, I could create the formula for each cell....is there a way to have a generic formula for the entire column that would accommodate this situation? That is, a single formula that is defined that would convert whatever rows to blank that met the criteria for the specific row.....IOW, in this excel file I want the amounts to be blank anytime there is data in the TEXT column---except for the very first line which acts as the header.

This is where I am definitely beyond my expertise in Excel. If it cannot be a formula that can be created and placed as a whole without having to do it for each individual cell then it is a no go for this project (unless a formula that works and then a highlight the parts of the column to apply it to and paste the formula in one fell swoop).

Asking for a lot here I know---main thing is to find out IF it can even be done...am having my doubts.

[G Gabriel]

Re: Null value Excel

You only write the formula once then copy it down the column.
Here is a trick for a quick copy:

Highlight the cell that has the formula, you will notice a very tiny square at the right lower corner
Put the mouse on this square, a cross will appear. Hold & Drag down the rows, as many rows as you want, the formula will be copied to all these cells in that column.

[MikeC]

Re: Null value Excel

G,
I appreciate your time on this.

I knew there was a way to paste formulas to multiple cells but could not remember it--thanks.

What pleasantly surprised me is that the formula changed to represent each cell so that it works . A bit more testing but should be a doable way for the end user.

Thanks again.

Still would be great if it could be totally automated, but then I guess I have to remember I am dealing with Excel!

[Garry Flanigan]

Re: Null value Excel

It can be completely automated from within Alpha with activex.
A search of "activex excel" yields lots of useful information. It is not a trivial task but if you want to export data regularly it may be worth the time required.
Garry

[MikeC]

Re: Null value Excel

Hi Garry,
Thanks for the info.

Oddity here though.....I had tested prior to posting the export of my dbf with several zeroes being blanked out and they all came into Excel as zeroes instead of blanks.

After G let me know how to use a formula in Excel to do this manually, I went back and tried it again----now, and very consistently, all dbf blank numeric fields export to Excel as Blanks! Go figure!!!

Have no clue as to why....and now will have to spend more time testing just to make sure it was a fluke before.

So now all is well and have finished my scripting to set all necessary numerics to null with dynamic code that allows for the variable amount of fields due to the user choosing one or more company files for the cross-tab/join operations prior to export. A lot of work, but now completely automated with a single button push.

In case others need to set numerics to null, I modified the help file script a bit to do so---the main hurdle was getting myself to understand that the table field itself has to be set to null_value().....a variable cannot be.

The Bal_Names variable is a crlf list of the changing fields that needed to have certain zeroes blanked out. Generating that list was a whole other issue/learning experience.

'tbl is a table pointer dimmed in the list generating script mentioned
dim ptr_Co_name as c
dim comp_num as n 'number of company fields
query.order=""
query.filter="isnotblank(\"Text\")" 'the field that if it has data then the amount field is to be blanked

tbl.query_create("N")
for i=1 to fld_no 'from a separate script that determined how many fields to consider
  tbl.fetch_first()
  ptr_Co_name ="tbl."+word(Bal_Names,i,crlf())
  Comp_num=eval("tbl."+word(Bal_Names,i,crlf()))
    while .not. tbl.fetch_eof()
        if Comp_num=0 ' if the amount field has a zero, blank it
          tbl.change_begin()
          eval(ptr_Co_name) = NULL_VALUE()
          tbl.change_end(.t.)
        end if
      tbl.fetch_next()
    end while
next
tbl.close()

It runs fast as there will only be a few thousand records ever so have not spent any time cleaning/optimizing it up really....it works so am not going to break it! :)

[G Gabriel]

Re: Null value Excel

You could add a character field to the table, make it a calculated one with an expression; if(field_1<>"".and.field_3=0,"",field_3) and export the table without having to run any scripts in advance.

[MikeC]

Re: Null value Excel

G,
Thanks for the option.

That definitely was a consideration I had as have done that with other projects.

But considering the table in question is created via first a crosstab and then a join operation, I believe (but could be mistaken) that I would have to script in the additional field as well---which I have also done for past projects but this seemed to be a bit less work overall.

The main thing is that it is working now with basically no speed issues. If there were any, I would for sure try other methods to obtain the fastest way possible. But playtime is over for me tonight and so will leave this and consider it finished! :)

[Stan Mathews]

Re: Null value Excel

If it runs fast enough now it will run faster if you wrap the for...next loop in batch statements.

tbl.batch_begin()
for i=1 to fld_no 'from a separate script that determined how many fields to consider
  tbl.fetch_first()
  ptr_Co_name ="tbl."+word(Bal_Names,i,crlf())
  Comp_num=eval("tbl."+word(Bal_Names,i,crlf()))
    while .not. tbl.fetch_eof()
        if Comp_num=0 ' if the amount field has a zero, blank it
          tbl.change_begin()
          eval(ptr_Co_name) = NULL_VALUE()
          tbl.change_end(.t.)
        end if
      tbl.fetch_next()
    end while
next
tbl.batch_end()

[Steve Andrews]

Re: Null value Excel

Another way to blank out zeroes for selected columns is by using the Format Cells feature. For any column, simply select the entire column and press Format/Cells. Choose "Custom" for the category.

In the prompt for "Type", it will often have a value of "General". Change that to simply a pound sign (#). Then any zeroes in that column should be blanked out.

My 2 Cents,
Steve