I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png ---
IIRC, Excel will treat .NULL. values as blanks, so if you have a temporary cursor cFoo with a numeric column nBar something like UPDATE cFoo SET nBar = .NULL. WHERE nBar IS NULL should do the trick.
hth,
dt
D'oh!
Of course that should be:
UPDATE cFoo SET nBar = .NULL. WHERE nBar = 0
I forgot about NULL - as I haven't really used it much in VFP in the past.
I just tried doing a simple Replace of a value in a DBF file - that's numeric - with NULL - but, it gave an Error.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Thayer Sent: Tuesday, June 14, 2016 4:32 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
D'oh!
Of course that should be:
UPDATE cFoo SET nBar = .NULL. WHERE nBar = 0
-- Dave Thayer Denver, CO On Tue, Jun 14, 2016 at 2:28 PM, Dave Thayer davethayer@gmail.com wrote:
IIRC, Excel will treat .NULL. values as blanks, so if you have a temporary cursor cFoo with a numeric column nBar something like UPDATE cFoo SET nBar = .NULL. WHERE nBar IS NULL should do the trick.
hth,
dt
Dave Thayer Denver, CO
On Tue, Jun 14, 2016 at 2:02 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
[excessive quoting removed by server]
The DBF file has to allow NULL values.
Fred
On Tue, Jun 14, 2016 at 1:41 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
I forgot about NULL - as I haven't really used it much in VFP in the past.
I just tried doing a simple Replace of a value in a DBF file - that's numeric - with NULL - but, it gave an Error.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Thayer Sent: Tuesday, June 14, 2016 4:32 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
D'oh!
Of course that should be:
UPDATE cFoo SET nBar = .NULL. WHERE nBar = 0
-- Dave Thayer Denver, CO On Tue, Jun 14, 2016 at 2:28 PM, Dave Thayer davethayer@gmail.com wrote:
IIRC, Excel will treat .NULL. values as blanks, so if you have a temporary cursor cFoo with a numeric column nBar something like UPDATE cFoo SET nBar = .NULL. WHERE nBar IS NULL should do the trick.
hth,
dt
Dave Thayer Denver, CO
On Tue, Jun 14, 2016 at 2:02 PM, Kurt Wendt Kurt_Wendt@globetax.com
wrote:
I know - it's a Crazy request. Trying to figure out the Easiest way to
try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel
file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically
during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a
numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
[excessive quoting removed by server]
Really??
OK - here's the quick test I did. I took an existing DBF file, copied a couple records to a new Dummy.dbf. One of the fields was Numeric 5 0 Then - I simply went to one of the records and ran the following command: REPLACE errline WITH .NULL.
VFP then reports back - Field Errline does not accept null values.
Hmm...
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Fred Taylor Sent: Tuesday, June 14, 2016 4:44 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
The DBF file has to allow NULL values.
Fred
On Tue, Jun 14, 2016 at 1:41 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
I forgot about NULL - as I haven't really used it much in VFP in the past.
I just tried doing a simple Replace of a value in a DBF file - that's numeric - with NULL - but, it gave an Error.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Thayer Sent: Tuesday, June 14, 2016 4:32 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
D'oh!
Of course that should be:
UPDATE cFoo SET nBar = .NULL. WHERE nBar = 0
-- Dave Thayer Denver, CO On Tue, Jun 14, 2016 at 2:28 PM, Dave Thayer davethayer@gmail.com wrote:
IIRC, Excel will treat .NULL. values as blanks, so if you have a temporary cursor cFoo with a numeric column nBar something like UPDATE cFoo SET nBar = .NULL. WHERE nBar IS NULL should do the trick.
hth,
dt
Dave Thayer Denver, CO
On Tue, Jun 14, 2016 at 2:02 PM, Kurt Wendt Kurt_Wendt@globetax.com
wrote:
I know - it's a Crazy request. Trying to figure out the Easiest way to
try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel
file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically
during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a
numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
[excessive quoting removed by server]
I've only done this with cursors, I'd probably want to make a temp read/write cursor intermediate just as an intermediate before excel export, this can also save you from accidentally polluting other dataflows with unwanted null values.
I have one project where I can do this with cursors defined with a CREATE CURSOR command with nulls explicitly allowed, and with cursors generated from SQL Pass through. I don't know if VFP is clever enough to only allow nulls on cursors made by SQL Select To...Curser cFoo READWRITE depending on the source table definition though.
On Tue, Jun 14, 2016 at 2:48 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
Really??
OK - here's the quick test I did. I took an existing DBF file, copied a couple records to a new Dummy.dbf. One of the fields was Numeric 5 0 Then - I simply went to one of the records and ran the following command: REPLACE errline WITH .NULL.
VFP then reports back - Field Errline does not accept null values.
Hmm...
Regards,
OK - part of the code in the system pulls data from MS SQL. So - I was trying to mod this code - to add NULL as an option into the Select. But, I suspect I'm not doing it correctly. Was looking around in online help - but, could not quickly find the answer. So - I took a shot at trying to implement this. This is only Part of the line of code - but, you can see the attempt to define NULL: SELECT d.secid ,d.sec_name AS sec_nam ,d.coi AS issue_cnty , t.taxrate_el NULL FROM [dbo].[Dividends] as d JOIN [dbo].[Trandata] as t ON t.dividend_id = d.dividend_id
Any further input would be greatly appreciated.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Dave Thayer Sent: Tuesday, June 14, 2016 5:16 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
I've only done this with cursors, I'd probably want to make a temp read/write cursor intermediate just as an intermediate before excel export, this can also save you from accidentally polluting other dataflows with unwanted null values.
I have one project where I can do this with cursors defined with a CREATE CURSOR command with nulls explicitly allowed, and with cursors generated from SQL Pass through. I don't know if VFP is clever enough to only allow nulls on cursors made by SQL Select To...Curser cFoo READWRITE depending on the source table definition though.
On Tue, Jun 14, 2016 at 2:48 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
Really??
OK - here's the quick test I did. I took an existing DBF file, copied a couple records to a new Dummy.dbf. One of the fields was Numeric 5 0 Then - I simply went to one of the records and ran the following command: REPLACE errline WITH .NULL.
VFP then reports back - Field Errline does not accept null values.
Hmm...
Regards,
-- Dave Thayer Denver, CO
[excessive quoting removed by server]
On Tue, Jun 14, 2016 at 3:41 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
OK - part of the code in the system pulls data from MS SQL. So - I was trying to mod this code - to add NULL as an option into the Select. But, I suspect I'm not doing it correctly. Was looking around in online help - but, could not quickly find the answer. So - I took a shot at trying to implement this. This is only Part of the line of code - but, you can see the attempt to define NULL: SELECT d.secid ,d.sec_name AS sec_nam ,d.coi AS issue_cnty , t.taxrate_el NULL FROM [dbo].[Dividends] as d JOIN [dbo].[Trandata] as t ON t.dividend_id = d.dividend_id
Maybe something like ...CASE WHEN t.taxrate_el = 0 THEN NULL ELSE t.taxrate_el END AS taxrate_el FROM ....
On 6/14/2016 5:41 PM, Kurt Wendt wrote:
OK - part of the code in the system pulls data from MS SQL. So - I was trying to mod this code - to add NULL as an option into the Select. But, I suspect I'm not doing it correctly. Was
Look at the CAST function to allow and create nulls in a query result cursor.
SELECT d.secid ,d.sec_name AS sec_nam ,d.coi AS issue_cnty , t.taxrate_el NULL FROM [dbo].[Dividends] as d JOIN [dbo].[Trandata] as t ON t.dividend_id = d.dividend_id
So if coi is the field that is supposed to be null if it is zero, you would do something like this:
SELECT d.secid, d.sec_name as sec_name, CAST(IIF(d.coi=0, .null., d.coi) as N(12.2) NULL) as issue_cnty ... (and so on)
I think someone else mentioned it already but having a separate SELECT (into a temporary cursor) for the Excel export is ideal. Remember, this is VFP, you're not limited to SQL-only-kinds of data handling or other junk from other languages. Temporary cursors are extremely powerful, flexible, fast, and efficient. If you pull from MySQL into a table or cursor, you could immediately do another select from that table or cursor into your Excel exporting cursor. You could maybe do another query into MySQL, but if you've already pulled the data you need it'll be way faster to work with machine-local data.
-Charlie
FoxPro supports blank values in DBF files. Numeric values are blank by default and can be blanked using the BLANK command. The ISBLANK() function tells you if a numeric field is zero or blank.
When you use GATHER, REPLACE or INSERT and a memory value is present for this column, then you end up with a zero in the column. To maintain a blank value you must not write anything into it, at all. Any kind of UI bound editing whether in BROWSE, EDIT or as a ControlSource will also result in the field becoming zero. That's because we do not have blank values in memory.
When other systems read DBF files and do not have a concept of blank numeric value, then usually the blank field is replaced by 0 for numeric values or some minimum date for date and time values.
.NULL. is the standard way of doing this with almost any other database system, though.
Thanks Charlie - seems you may have the answer I needed. Although - its actually the taxrate_el field that needs to contain NULL. Coi is Country of Issue - and is thus a Country code...
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Charlie Sent: Tuesday, June 14, 2016 10:48 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
On 6/14/2016 5:41 PM, Kurt Wendt wrote:
OK - part of the code in the system pulls data from MS SQL. So - I was trying to mod this code - to add NULL as an option into the Select. But, I suspect I'm not doing it correctly. Was
Look at the CAST function to allow and create nulls in a query result cursor.
SELECT d.secid ,d.sec_name AS sec_nam ,d.coi AS issue_cnty , t.taxrate_el NULL FROM [dbo].[Dividends] as d JOIN [dbo].[Trandata] as t ON t.dividend_id = d.dividend_id
So if coi is the field that is supposed to be null if it is zero, you would do something like this:
SELECT d.secid, d.sec_name as sec_name, CAST(IIF(d.coi=0, .null., d.coi) as N(12.2) NULL) as issue_cnty ... (and so on)
I think someone else mentioned it already but having a separate SELECT (into a temporary cursor) for the Excel export is ideal. Remember, this is VFP, you're not limited to SQL-only-kinds of data handling or other junk from other languages. Temporary cursors are extremely powerful, flexible, fast, and efficient. If you pull from MySQL into a table or cursor, you could immediately do another select from that table or cursor into your Excel exporting cursor. You could maybe do another query into MySQL, but if you've already pulled the data you need it'll be way faster to work with machine-local data.
-Charlie
[excessive quoting removed by server]
WAG warning!
Have an extra character field in the dbf/cursor. Scan the cursor and copy the contents of the numeric field into the character field if it is a number then delete the numeric field from the dbf/cursor or do a select from the cursor excluding the numeric field into another cursor which can then be copied to an Excel file.
HTH
John
John Weller 01380 723235 07976 393631
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: 14 June 2016 21:03 To: profoxtech@leafe.com Subject: A Blank Value for a Number?
I know - it's a Crazy request. Trying to figure out the Easiest way to try
and do
this - and hoping to Avoid having to do more complicated solutions like
Office
Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file.
And, in
the past - if there are certain records that have a Zero in a particular
column -
they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the
problem. For
the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that
has a
Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a
numeric
data field hold any kind of blank data. They even suggested just a Dash.
But,
again - I figured it's not possible. However, I figured for the hell of it
- I would
post here - in case someone had an interesting insight that I hadn't
thought
of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or
legal
advice. This email and any attachments hereto is intended only for use by
the
addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at
+1-
212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
Part of the complication is this.
The Temp data file that holds the data - prior to it getting exported to Excel - it Also is used to update an MS SQL Table. That's why the numeric field has to really stay numeric.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of John Weller Sent: Tuesday, June 14, 2016 4:35 PM To: profoxtech@leafe.com Subject: RE: A Blank Value for a Number?
WAG warning!
Have an extra character field in the dbf/cursor. Scan the cursor and copy the contents of the numeric field into the character field if it is a number then delete the numeric field from the dbf/cursor or do a select from the cursor excluding the numeric field into another cursor which can then be copied to an Excel file.
HTH
John
John Weller 01380 723235 07976 393631
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kurt Wendt Sent: 14 June 2016 21:03 To: profoxtech@leafe.com Subject: A Blank Value for a Number?
I know - it's a Crazy request. Trying to figure out the Easiest way to try
and do
this - and hoping to Avoid having to do more complicated solutions like
Office
Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file.
And, in
the past - if there are certain records that have a Zero in a particular
column -
they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the
problem. For
the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that
has a
Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a
numeric
data field hold any kind of blank data. They even suggested just a Dash.
But,
again - I figured it's not possible. However, I figured for the hell of it
- I would
post here - in case someone had an interesting insight that I hadn't
thought
of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or
legal
advice. This email and any attachments hereto is intended only for use by
the
addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at
+1-
212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
Hi Kurt:
I see that Management stupidity is not only here ;-)
I think that you are trying to solve from the "Data" side something that belong to the "Interface". In other words, the only thing they want is to "not see" a zero, right?
So what if you format the cells in a way that the mask applied does not show the value when it is zero?
I see 2 options:
1) Use a "Conditional Format" to make the foreground white when the value is zero
2) If you search on the Office help for "hide zero" (I have the Spanish version), you can found a setting applied for a specific worksheet on which zero values can be hidden.
I try to traduce the instructions I've found:
- Click the rounded "Office" menu at top-left - Select "Excel Options" at the bottom - Select "Advanced" - Look for "Options for this sheet" at the middle of the scrolling - Uncheck "Show zero in cells that have a value of zero"
Best Regards.-
2016-06-14 22:02 GMT+02:00 Kurt Wendt Kurt_Wendt@globetax.com:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
Fenando - thanks for your input. However, the problem is a little deeper - and, yes - the request is kinda crazy indeed!
If in the Temp file there is a Zero - then, when these other 2 additional Excel files are generated during the process - it generates data lines based upon a Zero - as the Zero is actually the Tax Rate field. That's why they want the Tax Rate field as blank for certain records where the Share counts are Zero.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Fernando D. Bozzo Sent: Tuesday, June 14, 2016 4:45 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
Hi Kurt:
I see that Management stupidity is not only here ;-)
I think that you are trying to solve from the "Data" side something that belong to the "Interface". In other words, the only thing they want is to "not see" a zero, right?
So what if you format the cells in a way that the mask applied does not show the value when it is zero?
I see 2 options:
1) Use a "Conditional Format" to make the foreground white when the value is zero
2) If you search on the Office help for "hide zero" (I have the Spanish version), you can found a setting applied for a specific worksheet on which zero values can be hidden.
I try to traduce the instructions I've found:
- Click the rounded "Office" menu at top-left - Select "Excel Options" at the bottom - Select "Advanced" - Look for "Options for this sheet" at the middle of the scrolling - Uncheck "Show zero in cells that have a value of zero"
Best Regards.-
2016-06-14 22:02 GMT+02:00 Kurt Wendt Kurt_Wendt@globetax.com:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
Ok, got it.
About the NULL values in DBFs, to actually allow using them you need to explicitly select "NULL" option when defining a field, because if not, then you can't insert NULLs.
Try "*MODIFY test*" in command Window and you will see that option at the right of the field definition.
What I don't know is if later you will have errors because using NULLs with numbers
2016-06-14 22:50 GMT+02:00 Kurt Wendt Kurt_Wendt@globetax.com:
Fenando - thanks for your input. However, the problem is a little deeper - and, yes - the request is kinda crazy indeed!
If in the Temp file there is a Zero - then, when these other 2 additional Excel files are generated during the process - it generates data lines based upon a Zero - as the Zero is actually the Tax Rate field. That's why they want the Tax Rate field as blank for certain records where the Share counts are Zero.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Fernando D. Bozzo Sent: Tuesday, June 14, 2016 4:45 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
Hi Kurt:
I see that Management stupidity is not only here ;-)
I think that you are trying to solve from the "Data" side something that belong to the "Interface". In other words, the only thing they want is to "not see" a zero, right?
So what if you format the cells in a way that the mask applied does not show the value when it is zero?
I see 2 options:
- Use a "Conditional Format" to make the foreground white when the value
is zero
- If you search on the Office help for "hide zero" (I have the Spanish
version), you can found a setting applied for a specific worksheet on which zero values can be hidden.
I try to traduce the instructions I've found:
- Click the rounded "Office" menu at top-left
- Select "Excel Options" at the bottom
- Select "Advanced"
- Look for "Options for this sheet" at the middle of the scrolling
- Uncheck "Show zero in cells that have a value of zero"
Best Regards.-
2016-06-14 22:02 GMT+02:00 Kurt Wendt Kurt_Wendt@globetax.com:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank
values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
Got it - that worked - now I can do a NULL replace!!!
But - yes, I totally hear you - there may be further ramifications or complications in doing this. Will have to see. Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Fernando D. Bozzo Sent: Tuesday, June 14, 2016 4:59 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
Ok, got it.
About the NULL values in DBFs, to actually allow using them you need to explicitly select "NULL" option when defining a field, because if not, then you can't insert NULLs.
Try "*MODIFY test*" in command Window and you will see that option at the right of the field definition.
What I don't know is if later you will have errors because using NULLs with numbers
2016-06-14 22:50 GMT+02:00 Kurt Wendt Kurt_Wendt@globetax.com:
Fenando - thanks for your input. However, the problem is a little deeper - and, yes - the request is kinda crazy indeed!
If in the Temp file there is a Zero - then, when these other 2 additional Excel files are generated during the process - it generates data lines based upon a Zero - as the Zero is actually the Tax Rate field. That's why they want the Tax Rate field as blank for certain records where the Share counts are Zero.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Fernando D. Bozzo Sent: Tuesday, June 14, 2016 4:45 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
Hi Kurt:
I see that Management stupidity is not only here ;-)
I think that you are trying to solve from the "Data" side something that belong to the "Interface". In other words, the only thing they want is to "not see" a zero, right?
So what if you format the cells in a way that the mask applied does not show the value when it is zero?
I see 2 options:
- Use a "Conditional Format" to make the foreground white when the
value is zero
- If you search on the Office help for "hide zero" (I have the
Spanish version), you can found a setting applied for a specific worksheet on which zero values can be hidden.
I try to traduce the instructions I've found:
- Click the rounded "Office" menu at top-left
- Select "Excel Options" at the bottom
- Select "Advanced"
- Look for "Options for this sheet" at the middle of the scrolling
- Uncheck "Show zero in cells that have a value of zero"
Best Regards.-
2016-06-14 22:02 GMT+02:00 Kurt Wendt Kurt_Wendt@globetax.com:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank
values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
I'm not sure I understand your problem, but I often have to read an Excel file and pass the data to a table. Sometimes there is no value in a cell that should have a certain value (character or numeric)
Reading it line by line in those cases Excel sends a NULL value.
I just do something like this:
cFile = 'c:\exceldata\myexcelfile.xls'
this.oExcel=createobject("Excel.Application")
With this.oExcel
.Workbooks.Open("&cFile")
.Range("A27").Select
oRange = .Range("A27:L27")
Do while .t.
with oRange
cDescription = Nvl(Alltrim(.Columns[3].value),'')
etc., etc. && read more cells into ad hoc variables
endwith
oRange = oRange.Offset(1,0) && jump to next line on Excel sheet
EndDo
and then it is a simple thing to replace the dbf DESCRIP field with cDescription
I re read your post and see that you want to blank certain cells automatically. Following the above technique it should be an easy thing to do. Just look at my example with the cDescription variable. The nvl function will give it a value of '' if it contains a null value, or the actual value if it is not null
Rafael Copquin
On 14/06/2016 17:02, Kurt Wendt wrote:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
Rafael - its quite the reverse. Its manipulating data in VFP - then simply Exporting the data to an Excel file using COPY TO command.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of rafael copquin Sent: Tuesday, June 14, 2016 6:27 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
I'm not sure I understand your problem, but I often have to read an Excel file and pass the data to a table. Sometimes there is no value in a cell that should have a certain value (character or numeric)
Reading it line by line in those cases Excel sends a NULL value.
I just do something like this:
cFile = 'c:\exceldata\myexcelfile.xls'
this.oExcel=createobject("Excel.Application")
With this.oExcel
.Workbooks.Open("&cFile")
.Range("A27").Select
oRange = .Range("A27:L27")
Do while .t.
with oRange
cDescription = Nvl(Alltrim(.Columns[3].value),'')
etc., etc. && read more cells into ad hoc variables
endwith
oRange = oRange.Offset(1,0) && jump to next line on Excel sheet
EndDo
and then it is a simple thing to replace the dbf DESCRIP field with cDescription
I re read your post and see that you want to blank certain cells automatically. Following the above technique it should be an easy thing to do. Just look at my example with the cDescription variable. The nvl function will give it a value of '' if it contains a null value, or the actual value if it is not null
Rafael Copquin
On 14/06/2016 17:02, Kurt Wendt wrote:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
I felt I did not understand the issue but replied anyways.
However, check the SET NULL command in help. If ON it will allow you to store a null value.
Also you can create a cursor with fields that accept null values thus:
create cursor blabla(number I null,desc C(10))
append blank
replace number with null, desc with 'null value'
browse
will show null in the number column and 'null value' in the desc column
Then, when you save the cursor as an excel file _perhaps_ you will have a null value in the applicable cell (_this I did not try_)
Rafael
On 15/06/2016 9:57, Kurt Wendt wrote:
Rafael - its quite the reverse. Its manipulating data in VFP - then simply Exporting the data to an Excel file using COPY TO command.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of rafael copquin Sent: Tuesday, June 14, 2016 6:27 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
I'm not sure I understand your problem, but I often have to read an Excel file and pass the data to a table. Sometimes there is no value in a cell that should have a certain value (character or numeric)
Reading it line by line in those cases Excel sends a NULL value.
I just do something like this:
cFile = 'c:\exceldata\myexcelfile.xls'
this.oExcel=createobject("Excel.Application")
With this.oExcel
.Workbooks.Open("&cFile") .Range("A27").Select oRange = .Range("A27:L27") Do while .t. with oRange cDescription = Nvl(Alltrim(.Columns[3].value),'') etc., etc. && read more cells into ad hoc variables endwith oRange = oRange.Offset(1,0) && jump to nextline on Excel sheet
EndDoand then it is a simple thing to replace the dbf DESCRIP field with cDescription
I re read your post and see that you want to blank certain cells automatically. Following the above technique it should be an easy thing to do. Just look at my example with the cDescription variable. The nvl function will give it a value of '' if it contains a null value, or the actual value if it is not null
Rafael Copquin
On 14/06/2016 17:02, Kurt Wendt wrote:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
Rafael,
Thanks again for your feedback. I actually applied suggestions by others - and largely got things working. And, yes, once I had NULL in a field - it would simply output as Blank in Excel - which is Exactly what I was looking for. So - yes, the use of NULLS for this project works - giving the users what they actually wanted.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of rafael copquin Sent: Thursday, June 16, 2016 7:32 AM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
I felt I did not understand the issue but replied anyways.
However, check the SET NULL command in help. If ON it will allow you to store a null value.
Also you can create a cursor with fields that accept null values thus:
create cursor blabla(number I null,desc C(10))
append blank
replace number with null, desc with 'null value'
browse
will show null in the number column and 'null value' in the desc column
Then, when you save the cursor as an excel file _perhaps_ you will have a null value in the applicable cell (_this I did not try_)
Rafael
On 15/06/2016 9:57, Kurt Wendt wrote:
Rafael - its quite the reverse. Its manipulating data in VFP - then simply Exporting the data to an Excel file using COPY TO command.
Regards, Kurt Wendt Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of rafael copquin Sent: Tuesday, June 14, 2016 6:27 PM To: profoxtech@leafe.com Subject: Re: A Blank Value for a Number?
I'm not sure I understand your problem, but I often have to read an Excel file and pass the data to a table. Sometimes there is no value in a cell that should have a certain value (character or numeric)
Reading it line by line in those cases Excel sends a NULL value.
I just do something like this:
cFile = 'c:\exceldata\myexcelfile.xls'
this.oExcel=createobject("Excel.Application")
With this.oExcel
.Workbooks.Open("&cFile") .Range("A27").Select oRange = .Range("A27:L27") Do while .t. with oRange cDescription = Nvl(Alltrim(.Columns[3].value),'') etc., etc. && read more cells into ad hoc variables endwith oRange = oRange.Offset(1,0) && jump to nextline on Excel sheet
EndDoand then it is a simple thing to replace the dbf DESCRIP field with cDescription
I re read your post and see that you want to blank certain cells automatically. Following the above technique it should be an easy thing to do. Just look at my example with the cDescription variable. The nvl function will give it a value of '' if it contains a null value, or the actual value if it is not null
Rafael Copquin
On 14/06/2016 17:02, Kurt Wendt wrote:
I know - it's a Crazy request. Trying to figure out the Easiest way to try and do this - and hoping to Avoid having to do more complicated solutions like Office Automation to manipulate an Excel file.
So - here's the deal. This system I work on - it outputs this Excel file. And, in the past - if there are certain records that have a Zero in a particular column - they would Manually wipe out the Zero - and also blank out several of the other fields in that Row.
But, the new request is to make those things happen automatically during process when the Excel file gets exported. So that they do NOT have to manually blank out certain fields. But, of course, therein lies the problem. For the other rows of data - this particular field is supposed to be a numeric value. And, AFAIK - there really is NO Way to have a DBF or Cursor that has a Numeric field - and yet have blank values in it.
As such, I suspect the answer is that this can't be done - to have a numeric data field hold any kind of blank data. They even suggested just a Dash. But, again - I figured it's not possible. However, I figured for the hell of it - I would post here - in case someone had an interesting insight that I hadn't thought of...
Kurt Wendt Senior Systems Analyst [GlobeTax_SecondaryLogo_TwoColor_Lo]https://www.globetax.com/ One New York Plaza - 34th Floor New York, NY 10004-1936, USA Tel. +1-212-747-9100 Direct +1-212-405-8246 www.GlobeTax.comhttps://www.globetax.com/
GlobeTax does not provide or offer, and this is not, tax, investment or legal advice. This email and any attachments hereto is intended only for use by the addressee(s) and may contain confidential information. If you are not the intended recipient of this email, please immediately notify the sender at +1-212-747-9100 and permanently delete the original and any copies of this email.
--- StripMime Report -- processed MIME parts --- multipart/related multipart/alternative text/plain (text body -- kept) text/html image/png
[excessive quoting removed by server]
-
Charlie -
Dave Thayer -
Fernando D. Bozzo -
Fred Taylor -
John Weller -
Kurt Wendt -
rafael copquin -
Wollenhaupt, Christof