Skip to main content
Inspiring
December 18, 2006
Question

Replacing Null Values

  • December 18, 2006
  • 8 replies
  • 1891 views
I receive a binary file every month that I must read and insert into a database. The file used to contain spaces when data was omitted, and now it contains null characters. If I open the file in a text editor like NoteTab Pro and replace all of the Null characters with spaces, the file process correctly, but using the file I'm getting without manipulating it only gives me part of the first line.

Is there a way to replace all instances of NULL so that they're spaces? Or really any kind of character that actually shows up (not backspace, for example).

Any help is much appreciated, thanks!
    This topic has been closed for replies.

    8 replies

    kodemonkiAuthor
    Inspiring
    March 2, 2007
    I thought I'd take the super-easy way out and try this:
    <cffile action="readbinary" file="C:\Inetpub\wwwroot\CFDOCS\ADMI\PROD\TEMP_IMPORT\#File.ServerFile#" variable="myBinary">
    <cfset myBinary = Replace(myBinary, Chr(0), Chr(45), "ALL")>

    which produces this error:
    Parameter 1 of function Replace must be a basic value, i.e., a string, number, logical, or date/time value
    so it can't be the whole binary file . . .

    Thoughts?
    kodemonkiAuthor
    Inspiring
    December 19, 2006
    All of the records in my file are complete. Each line consists of 2000 characters (if you consider NULL a character). It has 339 fields of information, of which I receive good (complete) data for about 37. If data is ommitted, the hex value 00 is used, which is the same as null or chr(00). If I open this file in a more advanced editor than Notepad (I am currently using NoteTab Pro), the NULL characters show up as periods but in the middle of the char space (so where a plus or hyphen would be).

    Here is a line:
    01+11065+05202+USA+ +···+P +S06+F+···+···+······+·····+NE +11 +B2 +W3 +···+···+······+·····+·····················+11 +·+······+USA+···+···+NE+1+B2+ +·+·+08926+·+······+000015235+ +·····+·+·+········+·········+·+·+·+·+·+·+·+·+·+·+·+·+·+·+·+·+·+·+20010218+·························+····+································+································+································+Framingham Ford +····················+1130 + +Framingham +MA +017011130 +017+ +1200 Worcester Road +Framingham +MA +01702 +···+···+···+···+····+····+····+····+····+····+·····+·····+·····+·····+·····+·····+20060919+·+·+·+·+·+ +·+·+·+·+·+·+·+·+········+········+·+·+·+·+·+·+·+·+·+·+·+·+·+·····+····································································································+····································································································+ +····················+····················+····················+·+·+·+·+··+·+·····+·+ +·+69+09+97+11+·········+·+·+2006-11-30-08.30.27.595704+ 00.000000+ 000.000000+·+·+········································································································································································································································································································································································································································································································································································································································································································································

    Also, I have tried the following piece of code and it didn't work. It said LIST value cannot be converted to a string because it is not a simple value. Simple values are booleans, numbers, strings, and date-time values.

    <cffile action="readbinary" file="C:\Inetpub\wwwroot\CFDOCS\ADMI\PROD\TEMP_IMPORT\#File.ServerFile#" variable="myBinary">
    <cfloop index="myLine" item="" list="#myBinary#" delimiters="#chr(10)#">
    <cfset MyLine2 = replace(MyLine,00000000,00010000,"ALL")>
    <cffile action="write" file="C:\Inetpub\wwwroot\CFDOCS\ADMI\PROD\TEMP_IMPORT\goldd_char.txt" variable="myFile" addnewline="yes" output="#myLine2#">
    </cfloop>
    Inspiring
    December 19, 2006
    What does a complete record look like in your file? How about an incomplete one?
    kodemonkiAuthor
    Inspiring
    December 18, 2006
    The delimeters are chr(10) (new line), so I would hope that they are not being replaced . . . is that what you meant by "getting" all the delimeters?
    Inspiring
    December 18, 2006
    Is this a delmited file? If so, are you getting all the delimiters?
    kodemonkiAuthor
    Inspiring
    December 18, 2006
    Unfortunately, the second parameter of Replace() must be a non-empty string. I am getting the error: Parameter 2 of function Replace which is now "" must be a non-empty string
    kodemonkiAuthor
    Inspiring
    December 18, 2006
    Thank you very much, I was indeed talking about ASCII values. I completely forgot that replace() can be used like that . . . why? I have no idea.

    Thanks for convincing me to get some coffee on a Monday!
    December 18, 2006
    When you refer to the null character, are you talking about ASCII 0? If so, the following should work:
    <CFSET foo = Replace(foo, Chr(0), Chr(32), "ALL")>