title: SQL Server trim whitespace from flat file on import
body: <p>I'm using the SQL Server Import/Export wizard to upload several large (>100 GB) flat files to a SQL Server. These files are delimited, not fixed-width, but still have extraneous trailing whitespace within columns:</p>
  
  <pre><code>100,VALUE         ,VALUE           ,VALUE
  1002,VALUE         ,VALUE           ,VALUE
  </code></pre>
  
  <p>I don't have control over how these files are created. Unfortunately, the whitespace is maintained in SQL following the upload. I noticed that I can append <code>SET ANSI_PADDING OFF;</code> to the SQL query auto-generated by the wizard, but it looks like changing that setting is ill-advised. Is there a better means to go about this process? Is it even worth doing? </p>
  
  <p>Cleaning the files in advance is not possible, given that in total I have about 10TB of data to upload and the code would take days, possibly weeks, to run. I imagine running an <code>ALTER COLUMN ... LTRIM(RTRIM([column_name]))</code> is going to be very time-intensive as well.</p>