.Net Parsing Fixed Width Data... From a Concatenated, Single, Fixed-Width Column
<p>I was bored and looking at old code that runs like molasses on a cold day.  I found that a group of tables in our accounting system - each with 500,000 records of ~20 datapoints - that use a single column of concatenated, fixed-width values instead of separate columns.  (Fixing the tables isn't an option.)  An old .net ETL project is grabbing all records, doing a bunch of substrings on each record to set an object's corresponding attributes, then sending the object to merge with production data via a stored proc.  </p>

<p>The way it is working is fine.  It works.  And, to be perfectly honest, I doubt I'll be given the go-ahead to fix it even if I come up with a better solution, but <strong>I was curious to see if anyone knew of a better way of doing this</strong>, because it's not entirely unlikely that I'll face a situation like this in the future.</p>

<p>I was thinking that if there was a way to use the TextFieldParser to parse a static string instead of a file/stream that might be a valid idea.  Or, instead, I could write the entire table to a text file and then use the TextFieldParser to send data to the SProc.  <a href="http://www.dotnetperls.com/textfieldparser" rel="nofollow">http://www.dotnetperls.com/textfieldparser</a> does show that TextFieldParser is quite a bit faster than split, which I would assume is tantamount to the string manipulation our project is currently doing with substring.  So there may be something to that idea.</p>

<p>Or perhaps the whole, old project should be dumped for a shiny new SSIS project.  Would it also have to write the records to a flat file before importing into SQL?  Or can it import directly from the table?</p>

<p>Thank you in advance!</p>