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
Improve this question
2
  • 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, 2016 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, 2016 at 12:57

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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, 2016 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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