What is the best way to import this non-standard text file into SQL Server? I have a text file that is generated daily. It is a fixed width format with one frustrating exception. The third column (NAME) has a variable width. The column is always exactly as long as the longest name in the data set + 1 space. This makes each day's files have different column start and end points.

Does anyone have a clever way to handle importing this file on a daily basis? Changing the source program is not an option.

I am willing to modify the file before import. 

I am willing to use almost any tool to include, but not limited to, SSIS, C#, PowerShell.

I am looking for the least "messy" solution with high-reliability.

Examples

A/R                                      Patient                                  PICASI, LINDA  00691685             90409843             Inpatient                                


Revenue                                  Patient                                  SIMPSON, RHASIRA B 00000000             00000000             Emergency
Based on your sample data it looks like there are 6 columns in total.

COL1, COL2, COL4, COL5, COL6 = All fixed width .

COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding).

You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed width. Take the line total and subtract COL1,2,4,5,6 to get COL3's width each day.

COL3 = LINE_WIDTH - (COL1 + COL2 + COL4 + COL5 + COL6)


After you get COL3's width you will be able to figure out all of your start and end points.