title: C# - Double spaces in DB field causing unwanted newlines when writing to a text file
body: <p><em>Let me preface this by saying I have never really coded in C# (I'm a Python programmer) and I coded in C++ back in college a little bit</em></p>
  
  <p>I have an SSIS package that has several C# script components (found the script online somewhere) that essentially reads data from a database and writes it to a text file. When I started data validation I noticed my file had about 3500 more rows in it than the database table itself. After writing a python script to compare the data, I realized that we have a database field called "REMARKS" that has been appended to several times. Whenever it is appended it apparently causes a double space (hitting space bar twice). </p>
  
  <p>When the script encounters this double white space issue it will write to a new line. I do NOT want it to do this. I need all those remarks to stay in its original field. Please see my script below.</p>
  
  <pre><code>public void Main()
      {
          // TODO: Add your code here
          string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
          string date = DateTime.Now.ToString("yyyyMMdd");
          try
          {
              //Declare Variables
              string FileNamePart = Dts.Variables["$Project::pvarWorkorderFlatFileNamePart"].Value.ToString();
              string DestinationFolder = Dts.Variables["$Project::pvarDestinationFolder"].Value.ToString();
              string TableName = Dts.Variables["$Project::pvarWorkorderTableName"].Value.ToString();
              string FileDelimiter = Dts.Variables["$Project::pvarFileDelimiter"].Value.ToString();
              string FileExtension = Dts.Variables["$Project::pvarFileExtension"].Value.ToString();
  
              //USE ADO.NET Connection from SSIS Package to get data from table
              SqlConnection myADONETConnection = new SqlConnection();
              myADONETConnection = (SqlConnection)(Dts.Connections["DB_CNXN"].AcquireConnection(Dts.Transaction) as SqlConnection);
  
              //Read data from table or view to data table
              string query = "SELECT * FROM " + TableName;
              SqlCommand cmd = new SqlCommand(query, myADONETConnection);
              //myADONETConnection.Open();
              DataTable d_table = new DataTable();
              d_table.Load(cmd.ExecuteReader());
              myADONETConnection.Close();
  
              string FileFullPath = DestinationFolder + "\\" + FileNamePart + "_" + date + FileExtension;
  
              StreamWriter sw = null;
              sw = new StreamWriter(FileFullPath, false);
  
              // Write the Header Row to File
              int ColumnCount = d_table.Columns.Count;
              for (int ic = 0; ic &lt; ColumnCount; ic++)
              {
                  sw.Write(d_table.Columns[ic]);
                  if (ic &lt; ColumnCount - 1)
                  {
                      sw.Write(FileDelimiter);
                  }
              }
              sw.Write(sw.NewLine);
  
              // Write All Rows to the File
              foreach (DataRow dr in d_table.Rows)
              {
                  for (int ir = 0; ir &lt; ColumnCount; ir++)
                  {
                      if (!Convert.IsDBNull(dr[ir]))
                      {
                          sw.Write(dr[ir].ToString());
                      }
                      if (ir &lt; ColumnCount - 1)
                      {
                          sw.Write(FileDelimiter);
                      }
                  }
                  sw.Write(sw.NewLine);
              }
              sw.Close();
  
              Dts.TaskResult = (int)ScriptResults.Success;
          }
          catch (Exception exception)
          {
              // Create Log File for Errors
              using (StreamWriter sw = File.CreateText(Dts.Variables["$Project::pvarLogFolder"].Value.ToString() + "\\" +
                  "ErrorLog_" + datetime + ".log"))
              {
                  sw.WriteLine(exception.ToString());
                  Dts.TaskResult = (int)ScriptResults.Failure;
              }
          }
          Dts.TaskResult = (int)ScriptResults.Success;
      }
  
      #region ScriptResults declaration
      /// &lt;summary&gt;
      /// This enum provides a convenient shorthand within the scope of this class for setting the
      /// result of the script.
      /// 
      /// This code was generated automatically.
      /// &lt;/summary&gt;
      enum ScriptResults
      {
          Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
          Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
      };
      #endregion
  
  }
  </code></pre>
  
  <p>}</p>
  
  <p>So the database table row might look something like this:</p>
  
  <pre><code>tip|date      |remarks                                                     |completed|device_id
  n  |2018-01-03|1st att 9/7/17  2nd att 9/12/17  3rd att 11/7/17 need code  |Y        |123
  </code></pre>
  
  <p>(Notice double spaces in remarks field between each of those entries including after the last one)
  When the script runs and outputs to the file this is what it would look like (file is pipe (|) delimitted):</p>
  
  <pre><code>Line 1:n|2018-01-03|
  Line 2:1st att 9/7/17
  Line 3:2nd att 9/12/17
  Line 4:3rd att 11/7/17 need code
  Line 5:
  Line 6:|Y|1234
  </code></pre>
  
  <p>I just need the script to take the rows as-is and keep everything on one line like this:</p>
  
  <pre><code>Line 1:n|2018-01-03|1st att 9/7/17  2nd att 9/12/17  3rd att 11/7/17 need code  |Y|1234
  </code></pre>
  
  <p>I am pulling the data from a SQL database and if there are no double spaces in the remarks field, then the script functions as intended.</p>
  
  <p>If someone could PLEASE help I would be forever grateful! Thank you in advance!</p>