As some of you may remember, I had a project recently where I needed Blanks to show up in Excel file output - where normally numerical data is displayed. The suggestion was to use NULL values. So - I implemented that - and it worked great! That folks here that use the system were very happy with that results.
Problem is - they didn't look closely at the resulting output. You see, they need to look at an Excel file output to confirm the data looks correct. But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong. Turns out - they are Missing a Blank character for the field that was set to accept NULL values. And, this morning - I reviewed as much of the online documentation in VFP (release 9) - and could find NO References to this type of problem.
Have other experienced this before? Do I merely need to add an additional digit (currently its 17,2 - and I'm looking to make it 18,2) to resolve this problem? Does this make sense???
Thanks in advance to all the ProFoxers!
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 ---
On Mon, Aug 15, 2016 at 11:48 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong.
Are "they" generating text files from the Excel output, or are they using the VFP app that generated the Excel files to do it?
The VFP app gens the Txt file. It does the generation of the 2 files in sequence - and they can choose to generate or Not generate each file during the processing.
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 Ted Roche Sent: Monday, August 15, 2016 11:54 AM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
On Mon, Aug 15, 2016 at 11:48 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong.
Are "they" generating text files from the Excel output, or are they using the VFP app that generated the Excel files to do it?
[excessive quoting removed by server]
So, the changes you made to put NULLs in the data for the Excel files work for Excel, but don't work for the text generation? How do you generate the text files?
On Mon, Aug 15, 2016 at 11:58 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
The VFP app gens the Txt file. It does the generation of the 2 files in sequence - and they can choose to generate or Not generate each file during the processing.
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 Ted Roche Sent: Monday, August 15, 2016 11:54 AM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
On Mon, Aug 15, 2016 at 11:48 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong.
Are "they" generating text files from the Excel output, or are they using the VFP app that generated the Excel files to do it?
[excessive quoting removed by server]
No - not true. Everything works - in as far as data is generated. It's just that the Text file is short by a blank in the one data column that contains a field that accepts the NULL values.
Text files are basically generated using: COPY TO ... TYPE SDF
I say basically because a data header type file is generated with the same COPY TO type command, and then low-level file handling is used to put the header and data into the same Txt file.
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 Ted Roche Sent: Monday, August 15, 2016 12:21 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
So, the changes you made to put NULLs in the data for the Excel files work for Excel, but don't work for the text generation? How do you generate the text files?
On Mon, Aug 15, 2016 at 11:58 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
The VFP app gens the Txt file. It does the generation of the 2 files in sequence - and they can choose to generate or Not generate each file during the processing.
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 Ted Roche Sent: Monday, August 15, 2016 11:54 AM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
On Mon, Aug 15, 2016 at 11:48 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong.
Are "they" generating text files from the Excel output, or are they using the VFP app that generated the Excel files to do it?
[excessive quoting removed by server]
SDF? Woah, what a blast from the past!
Perhaps you need to transform the NULL data to actual blank blanks before exporting, something like:
SELECT field1, field2, NVL(field3,space(1)) as field3, field4 from mydata... COPY TO ... TYPE SDF
On Mon, Aug 15, 2016 at 1:45 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
No - not true. Everything works - in as far as data is generated. It's just that the Text file is short by a blank in the one data column that contains a field that accepts the NULL values.
Text files are basically generated using: COPY TO ... TYPE SDF
I say basically because a data header type file is generated with the same COPY TO type command, and then low-level file handling is used to put the header and data into the same Txt file.
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 Ted Roche Sent: Monday, August 15, 2016 12:21 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
So, the changes you made to put NULLs in the data for the Excel files work for Excel, but don't work for the text generation? How do you generate the text files?
On Mon, Aug 15, 2016 at 11:58 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
The VFP app gens the Txt file. It does the generation of the 2 files in sequence - and they can choose to generate or Not generate each file during the processing.
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 Ted Roche Sent: Monday, August 15, 2016 11:54 AM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
On Mon, Aug 15, 2016 at 11:48 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong.
Are "they" generating text files from the Excel output, or are they using the VFP app that generated the Excel files to do it?
[excessive quoting removed by server]
OK Ted - here's what I did - and what assumptions I am making. As mentioned in my previous reply - I tested making the change from a numeric field of 17,2 to 18,2 - and that seems to have fixed the problem!
My assumption? My guess is that by enabling NULL for a field - that it actually has to use a character of that field to store extra info about the NULL usage. So - by making the field a total length of 18 instead of the prior length of 17 - I get back that character that I lost to the NULL data implementation.
Does that make sense? Logical sense??
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 Ted Roche Sent: Monday, August 15, 2016 3:07 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
SDF? Woah, what a blast from the past!
Perhaps you need to transform the NULL data to actual blank blanks before exporting, something like:
SELECT field1, field2, NVL(field3,space(1)) as field3, field4 from mydata... COPY TO ... TYPE SDF
On Mon, Aug 15, 2016 at 1:45 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
No - not true. Everything works - in as far as data is generated. It's just that the Text file is short by a blank in the one data column that contains a field that accepts the NULL values.
Text files are basically generated using: COPY TO ... TYPE SDF
I say basically because a data header type file is generated with the same COPY TO type command, and then low-level file handling is used to put the header and data into the same Txt file.
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 Ted Roche Sent: Monday, August 15, 2016 12:21 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
So, the changes you made to put NULLs in the data for the Excel files work for Excel, but don't work for the text generation? How do you generate the text files?
On Mon, Aug 15, 2016 at 11:58 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
The VFP app gens the Txt file. It does the generation of the 2 files in sequence - and they can choose to generate or Not generate each file during the processing.
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 Ted Roche Sent: Monday, August 15, 2016 11:54 AM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
On Mon, Aug 15, 2016 at 11:48 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong.
Are "they" generating text files from the Excel output, or are they using the VFP app that generated the Excel files to do it?
[excessive quoting removed by server]
When you add a NULLable field to a table, VFP adds a hidden column (another field) it uses to store a bitmap representing the NULL status of each field in the table.
I'm not sure why your solution fixed it, but I'm glad it did.
SDF is a space-padded file format, "columnar data" and an ancient XBase command. I suspect there may be a subtle bug here where VFP queries the fields length and gets a bogus reponse that cause it to calculate padding wrong. Interesting bug.
On Mon, Aug 15, 2016 at 4:03 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
OK Ted - here's what I did - and what assumptions I am making. As mentioned in my previous reply - I tested making the change from a numeric field of 17,2 to 18,2 - and that seems to have fixed the problem!
My assumption? My guess is that by enabling NULL for a field - that it actually has to use a character of that field to store extra info about the NULL usage. So - by making the field a total length of 18 instead of the prior length of 17 - I get back that character that I lost to the NULL data implementation.
Does that make sense? Logical sense??
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 Ted Roche Sent: Monday, August 15, 2016 3:07 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
SDF? Woah, what a blast from the past!
Perhaps you need to transform the NULL data to actual blank blanks before exporting, something like:
SELECT field1, field2, NVL(field3,space(1)) as field3, field4 from mydata... COPY TO ... TYPE SDF
On Mon, Aug 15, 2016 at 1:45 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
No - not true. Everything works - in as far as data is generated. It's just that the Text file is short by a blank in the one data column that contains a field that accepts the NULL values.
Text files are basically generated using: COPY TO ... TYPE SDF
I say basically because a data header type file is generated with the same COPY TO type command, and then low-level file handling is used to put the header and data into the same Txt file.
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 Ted Roche Sent: Monday, August 15, 2016 12:21 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
So, the changes you made to put NULLs in the data for the Excel files work for Excel, but don't work for the text generation? How do you generate the text files?
On Mon, Aug 15, 2016 at 11:58 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
The VFP app gens the Txt file. It does the generation of the 2 files in sequence - and they can choose to generate or Not generate each file during the processing.
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 Ted Roche Sent: Monday, August 15, 2016 11:54 AM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
On Mon, Aug 15, 2016 at 11:48 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong.
Are "they" generating text files from the Excel output, or are they using the VFP app that generated the Excel files to do it?
[excessive quoting removed by server]
Thanks for your feedback Ted. Guess my AssUmption was wrong - but, that's what happens when we AssUMe!
I'm glad this strange Bug was interesting and potentially brightened your day! :-)
So - yeah - not sure then why my 'fix' actually worked! But - as they say - if something Works (even though we don't exactly know Why) - then just leave it. The users are going to test my fix and confirm if things are good. I suspect that they will be. Should another problem pop-up in relation to this NULL thing - I will try to remember to follow-up on this same thread...
L8r! -K- Senior Systems Analyst
Tel. +1-212-747-9100 www.GlobeTax.com
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Ted Roche Sent: Monday, August 15, 2016 4:26 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
When you add a NULLable field to a table, VFP adds a hidden column (another field) it uses to store a bitmap representing the NULL status of each field in the table.
I'm not sure why your solution fixed it, but I'm glad it did.
SDF is a space-padded file format, "columnar data" and an ancient XBase command. I suspect there may be a subtle bug here where VFP queries the fields length and gets a bogus reponse that cause it to calculate padding wrong. Interesting bug.
On Mon, Aug 15, 2016 at 4:03 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
OK Ted - here's what I did - and what assumptions I am making. As mentioned in my previous reply - I tested making the change from a numeric field of 17,2 to 18,2 - and that seems to have fixed the problem!
My assumption? My guess is that by enabling NULL for a field - that it actually has to use a character of that field to store extra info about the NULL usage. So - by making the field a total length of 18 instead of the prior length of 17 - I get back that character that I lost to the NULL data implementation.
Does that make sense? Logical sense??
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 Ted Roche Sent: Monday, August 15, 2016 3:07 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
SDF? Woah, what a blast from the past!
Perhaps you need to transform the NULL data to actual blank blanks before exporting, something like:
SELECT field1, field2, NVL(field3,space(1)) as field3, field4 from mydata... COPY TO ... TYPE SDF
On Mon, Aug 15, 2016 at 1:45 PM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
No - not true. Everything works - in as far as data is generated. It's just that the Text file is short by a blank in the one data column that contains a field that accepts the NULL values.
Text files are basically generated using: COPY TO ... TYPE SDF
I say basically because a data header type file is generated with the same COPY TO type command, and then low-level file handling is used to put the header and data into the same Txt file.
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 Ted Roche Sent: Monday, August 15, 2016 12:21 PM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
So, the changes you made to put NULLs in the data for the Excel files work for Excel, but don't work for the text generation? How do you generate the text files?
On Mon, Aug 15, 2016 at 11:58 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
The VFP app gens the Txt file. It does the generation of the 2 files in sequence - and they can choose to generate or Not generate each file during the processing.
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 Ted Roche Sent: Monday, August 15, 2016 11:54 AM To: profoxtech@leafe.com Subject: Re: Complications with NULLS
On Mon, Aug 15, 2016 at 11:48 AM, Kurt Wendt Kurt_Wendt@globetax.com wrote:
But, then they need to generate this Text file output - which gets sent to clients. Those Txt files are then imported into their systems. And, needless to say - the format of the Text files must stay consistent and MUST be properly formatted.
So - imagine my surprise to learn that the Text files were wrong.
Are "they" generating text files from the Excel output, or are they using the VFP app that generated the Excel files to do it?
[excessive quoting removed by server]
-
Kurt Wendt -
Ted Roche