blank field data

[John192]

Hi,

While transferring file's data some blank fields in excel are coming as '0000' while some come correctly as blanks in excel file what could be the reason behind the same how to correct this issue?


Thanks

[John192]

...just to clarify bit more it is happening with in the same column of extracted excel from Data Transfer tool of IBM i Access Client Solutions. In green screen the value of for the field is blank and data type is character. Not sure why in excel some values are correct as blanks but other values are 0 (numeric zero).


Thanks..

[Scott Klement]

Tell us everything we would need to know in order to reproduce the problem. How (very specifically) to create the file you're copying from, and the exact parameters you are using to transfer the file, etc.

It is always a good rule of thumb when asking for help in a computer programming forum: Give all of the details necessary to reproduce the problem. If possible, make it completely simple to reproduce. This makes it far more likely that someone will take the time to help you.

[John192]

to simplify it if we have a file and it's field value shows as blank value in green screen when we do strsql (select fldname from filename) but the moment we download it using from Data Transfer tool of IBM i Access Client Solutions in excel format then we find that amazingly this field start showing 0(zero) for some of the field values wrongly and correctly blanks for some of the field values.(here Field values means I am talking about a single field which has multiple records for some of the records green screen (result of SELECT FLDNAME FROM FILENAME) Values are same like blanks both in Green screen and download extract of excel file but some records of this field in excel file show 0 (numeric Zero) values which conflict from Greenscreen(AS/400 SQL query result) blanks values of that field.

What could be the reason behind this and how to rectify this?


Thanks...

[Barbara Morris]

Perhaps some of the values that appear as blank in the green screen do not actually contain blanks.

Try this, to select for the records that are not blank.
SELECT FLDNAME FROM FILENAME WHERE FLDNAME <> ' '

If some of the FLDNAME values for that query appear blank, they actually have non-blank data that only appears blank in green screen.

[Barbara Morris]

To also see the hex value of the data, try

SELECT FLDNAME, HEX(FLDNAME) FROM FILENAME WHERE FLDNAME <> ' '

[John192]

How can I find manually whether there is any blank data for this field because there are thousands of records in this file for this specific field.
Also what to do with this HEX value query how could it help here?

Thanks..

[Barbara Morris]

I was assuming that you would not have thousands of records with this blank-not-blank problem. So after you do the query, you would be able to just look with your eyes to find records where the field *looks* blank, and maybe you would see a pattern for what the actual hex value is.

[Barbara Morris]

But now that I think about it more, I guess most of the thousands of records would have normal not-blank values. So I guess that would require a lot of page-down ...

[Vectorspace]

What are the valid characters for this field? If we assume alphanumeric (A-Z 0-9 and space not case sensitive) then you could use regex to find every field that does not conform

SELECT FLDNAME FROM FILENAME WHERE regexp_like(FLDNAME,'[^A-Za-z0-9 ]')

Select all those where the field contains that does not match one of: range A-Z, range a-z, range 0-9, space
(In the regex pattern '[^A-Za-z0-9 ]', note the space between the 9 and the ])

[John192]

when i simply do select * from filename where fldname='000000'

i get result as 'No data selected for output' and manually I have not been able to find any blank value for this SQL query(SELECT FLDNAME, HEX(FLDNAME) FROM FILENAME WHERE FLDNAME <> ' ') so far for this specific field name.


when i ran this SQL query :-
SELECT FLDNAME FROM FILENAME WHERE regexp_like(FLDNAME,'[^A-Za-z0-9 ]')


Got this error:- 'QQQSVREG in QSYS type *SRVPGM not found'..


Thanks...

[John192]

Please ignore my last post with respect to 'elect * from filename where fldname='000000'' i was able to find select * from filename where fldname='000000000000' values but just amazing it's hex value showing as 'F0F0F0F0F0....4040..40'

and excel extract is correct with respect to these '0' values for this field from IBM i Green screen values.

But from this link https://www.binaryhexconverter.com/d...-hex-converter


if we convert this value in hex format it comes as '0'.

why is it different from SQL query's hex value ?


Thanks...