Empty Numeric Field shows Zero in Export to Excel 1

[July Derek]

Hi All,

I'm exporting a Table to Excel format. The Field is numeric. But when there is no value to that field, the empty numeric fields show as a 0 in the Excel cells.

How can I ensure the these fields show a Blank instead of a Zero when I export to Excel ?

Thanks in advance.

 

[Chris Miller]

There is no blank in other databases or excel. Just NULL.

So make the field nullable and NULL as default.

Note: If you start Excel you can fill in single cells with values and non-edited cells remain empty. That's because it's a spreadsheet and not a table, any cell can have any data type. In a table, the structure is defined per record or row, you can't mix a visually empty cell (an empty string) with numbers. That's not the nature of tables. VFPs browse might be able to show you blank fields in its grid (visually similar to the excel spreadsheet), but if you inspect a blank number field with ? numberfield it will show up as 0, too.

There's a flag VFP remembers, when you APPEND BLANK, you can check ISBLANK('numberfield') and that will show .T., but that's not transferred to excel in an export.

Chriss

 

[Chris Miller]

To be precise: I checked it with int (I) vs numeric N(n,d) and integer fields can't be blank.

How "VFP remembers" blank fields differs from what I thought, but that doesn't matter in detail. It's just VFP stores N(n,d) fields as actual strings in the DBF, so 0.00 (in an N(4,2) field, for example), can be differentiated from blank by storing "0.00" for the 0 and, well, " " (the blank string of 4 spaces) for not populated fields in the DBF.

Well, you could export this to excel as strings. The cell data types then would be strings. Not sure whether different Excel versions will autoconvert a string that represents a number to a numeric cell and keep spaces as blank cell in the spreadsheet. Could well work, as I know if you want to keep things as strings in Excel you better set a cell value to a single quotebefore the actual value (no closing quote), which tells Excel to not infer a data type.

It's not working to change a num field with blank fields with ALTER TABLE to char fields, that introduces 0.00 for the blank fields. If you copy To TYPE CSV? You guessed it, it becomes 0.00 again. Export as SDF? 0.00 again. Select Isblank(field) from table - actually resulted .f. for all rows/fields, so that also doesn't help.

So I guess scanning the dbf and determining ISBLANK() per row will be your only helper tool. After exporting you'd need to go into the result shield and blank those cells that correspond to isblank() VFP table fields.

Well, all that said NULL surely is the simpler solution to the problem, conditional formatting or a search&replace can blank nulls in the Excel sheet, too.

Chriss

 

[July Derek]

Thanks Chriss, will check it out and get back.

Ive come back to VFP after nearly 10 years for revision to a software I had written, and am rusty as hell !

Thanks a lot for your help !