title: How do I remove spaces while importing flat file containing intermediate blank lines?
body: <p>I have a very simple text file that contains two comma separated values that is about 100 lines long.  This file is created by an automated process (that I cannot control) and I import this file into SQL via SSIS.  </p>
  
  <p>My job works very well except when there is a blank line within the file.  By this, I mean it is completely blank - no commas or other characters.  When this exists in the file, the record directly after it will be imported with <strong>two spaces</strong> before the imported value.</p>
  
  <p>For example, if the text line contains this "ABC,123", the imported SQL value will be "  ABC" for the first column.  I have tried to remove this by using a derived column with the TRIM statement, but that had no effect.  The REPLACE function also did not work.  The really strange part is that if I add a Data Viewer directly before the data flow Destination, the value looks fine.  I even added asterisks so that I could "see" the spaces if they exist, like this:</p>
  
  <pre><code>"*" + REPLACE([Column 0]," ","") + "*"
  </code></pre>
  
  <p>This is an <strong>extremely</strong> annoying issue, and I would greatly appreciate any suggestions.
  Thank you!</p>