2

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                                
  • So middle column width can be determined by this formula. (First Two Columns) + (X + 1 space) + (Last 3 column widths) for each row. I would use powershell to do a pre-processing and force width the NAME column to something you know will hold the largest possible value. – Jonathan Fite Apr 26 '16 at 12:46
  • I'd use a script component as a source and just loop through the lines in the file in C#. The code to parse the columns out of each line should be pretty straightforward. – Martin Smith Apr 26 '16 at 12:57
4

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.

  • Thanks, based on that idea I am going to use either a PowerShell or SSIS Script task to re-write the rows to a predetermined width and then let the SSIS flat file connection manager handle the import from there. Example of what I mean: pastebin.com/3YDEtVfD – SQL Hammer Apr 26 '16 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.