Field of blanks converting to null

thomash

• January 15, 2013 00:00

• Answered

Follow

I am read in a file that has string and binary info.
The file has fixed length records. If the string field only contains white space
it is being converted to a null. I would like the field to contain the spaces.

Thanks
Tom

Extra info

Call from Graph file

<Node id="INPUT_0" type="DATA_READER" fileURL="myFile.data" charset="Cp037" dataPolicy="Controlled" skipLeadingBlanks="false" trim="false" />

simple fmt file

<?xml version="1.0" encoding="UTF-8"?>
<Record name="myFile" type="fixed">
<Field name="myFile_String" type="string" nullable="false" size="5" />
<Field name="myFile_Bytes"  type="byte" nullable="false" size="4"/>
</Record>

• 

  imriskal

  • January 16, 2013 14:26

  0

  Comment actions Permalink

  Hi, Tom,
  
  with fixed length records, this described behavior is intended. Strings are trimmed during the reading so the string containing only whitechars is trimmed to null. And this is done no matter the settings of the reader are. If you want to have spaces instead of null, you can add a simple Reformat replacing null with spaces.
  
  Best regards,
• 

  thomash

  • January 23, 2013 19:09

  0

  Comment actions Permalink

  Thanks you for your reply.
  
  I would like to confirm that for fixed length records containing all spaces the following attributes have no effect and a null will be returned.
  
  skipLeadingBlank
  skipTrailingBlanks
  trim
  
  Does this apply to delimited fields?
  
  If the 3 commands are turned off will leading spaces be trimmed?
  
  Is there a web page that you can point me to on this information.
  
  Thank you
  Tom
• 

  imriskal

  • January 24, 2013 09:11

  0

  Comment actions Permalink

  Hi, Tom,
  
  this behavior does not apply in case of delimited metadata. I.e. spaces are left untouched in the default settings. You can find some information in our documentation, here: http://doc.cloveretl.com/documentation/ ... eader.html in the section Advanced Description, but I am affraid that this specific piece of information is missing there, sorry for that.
  
  Best regards,
• 

  thomash

  • January 25, 2013 17:10

  0

  Comment actions Permalink

  I have done some more testing and this is what I have found.
  
  If a string field is not nullable and the field contains only spaces then the system stops processing the rest of the file.
  This only applies to fix length records that have at least one byte field in it.
  
  My test file has 2 records and the Blank field of the first record contains only spaces. I am using cloverETL-3.3.0
  
  Is this the correct behavior?
  
  Thanks for your help.
  Tom
  
  P.S.
  If this is a problem I think problem is
  with line 236 of CharByteDataParser the command "recordSkipper.skipInput(consumerIdx);" This is clearing out the input buffer.
  
  
  The call from the grf
  

  <Node id="INPUT_0" type="DATA_READER" fileURL="fixLenNullTest.txt" dataPolicy="Controlled" skipLeadingBlanks="false" trim="false" />

  
  
  The fmt
  

  
  <Record name="RECORD_fixLenNullTest" type="fixed">
       <Field name=CLOVER_ROW_NUM" type="numeric" nullable="false" size="10" auto_filling="source_row_count" />
       <Field name="Record" type="string" nullable="false" size="3" />
       <Field name="Number" type="byte" nullable="false" size="3" />
       <Field name="Blank" type="string" nullable="false" size="4" />
  </Record>
  
  

• 

  imriskal

  • January 28, 2013 10:28

  0

  Comment actions Permalink

  Tom,
  
  this behavior is expected. As I mentioned before, strings containing just whitechars are trimmed to null in fixed-length metadata.
  
  Next, nullable property does not change content of the field if this was your intention. It just prevents null from appearing in the field by throwing an error. So if you set the field to nullable=false and the field contains null value, an error is raised and reading is stopped.
  
  And regarding the fact that only metadata containing byte field behaves this way, it is actually a bug, see https://bug.javlin.eu/browse/CL-2669. Fixed-length metadata should always trim the whitechars strings to null but it does not happen in some configurations currently.
  
  Let me know, if you have any further questions.
  
  Best regards,
• 

  thomash

  • January 28, 2013 16:59

  0

  Comment actions Permalink

  Thank you for your response.
  
  I am interested in the error handling. When an error of this type occurs should the system throw the record out and continue on to the next record or stop the program?
  
  Now it throws a warning and quits.
  
  

  WARN [INPUT_0_0] - Blank has incorrect default value in record 1, field 3 ("Number"), metadata "RECORD_fixLenNullTest"; input source: fixLenNullTest.txt

  
  
  Thanks again
  Tom
• 

  imriskal

  • January 29, 2013 11:34

  0

  Comment actions Permalink

  Hello again,
  
  Errors in reading can be handled in various ways, each has some pros and cons. It is a matter only of your personal preference. I think this topic is well covered here:
  http://doc.cloveretl.com/documentation/ ... olicy.html
  
  Best regards,

Please sign in to leave a comment.