Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: transform data file based on field value

    Hi,

    I have a tab delimited flat file that has records grouped by batch. Each batch 'section' has a header and then multiple records for that batch. I need to look at a field in the header section of each batch and based on the value, either load the records from that batch into a table or skip the batch entirely.

    Here is an example of the file:

    D493 03 6 012511CL08RILABO0LAB053
    48000152246203 01241172735640 00001
    48000152246203 01241172737828 00001
    48000152246278 01241176029321 00001

    D493 03 6 012511CL08RILABO0PSY053
    48000152246203 01241172735640 00001
    48000152246203 01241172737828 00001
    48000152246278 01241176029321 00001

    D493 03 6 012511CL08RILABO0LAB053
    48000152246203 01241172735640 00001
    48000152246203 01241172737828 00001
    48000152246278 01241176029321 00001

    So the first line will be the header of the first batch. I need to look at the 4th column, position 13-15 and if it is 'LAB' then load the records that follow until the next header section. Then check the 4th field again for 'LAB' and either load the records that follow or skip them.

    So in the example above, i would need to load the first section, skip the second, and load the third.

    Is there a way to do it using SSIS?

    Thanks

    Scott

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What I would do is use msacess as a go between link the SQL to msaccess
    Then my 1st attempt to link the text file and use the tab to create the col then run a vb to scan down each record.
    If that does not work will have to do it the hard way
    Vb to open the text file until eof
    Use the instr() function in vb to find the LAB
    Then use the instr() to find the tab chr(9) off top of head
    Or the split() split it by the tab

    Think that ask some more question.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    A couple of things. While reviewing your example, I think you misstated the beginning position of the substring of interest. I think you meant position 28, because position 13 for all 3 groups is 'LAB'. Also, the script below will not work if you don't know the other values. I used 'PSY', but I suspect you may have other values that you may want to skip, in which case, if you know them, you can replace "... = 'PSY'" with "... in ('PSY','...')". You can also test for ISNUMERIC(substring(...)) to determine unwanted hear record, etc.
    Code:
    select t.* from dbo.test t
       inner join (
          select RightHeaderRowID = t1.rowid, WrongHeaderRowID = t2.rowid from (
             select * from dbo.test
                where substring(content, 28, 3) = 'LAB'
             ) t1
          left outer join (
             select * from dbo.test
                where substring(content, 28, 3) = 'PSY'
             ) t2
          on t1.rowid < t2.rowid
       ) t0
       on t.rowid > t0.RightHeaderRowID and t.rowid < isnull(t0.WrongHeaderRowID, t.rowid+2)
    ...Also, your first question, about SSIS, can be answered as YES. Just load the data into a staging table (in my example it was dbo.test), which has 2 columns, - rowid identity, and content, nvarchar(1024). the rest is shown in the script.
    Last edited by rdjabarov; 01-27-11 at 14:56. Reason: forgot another assumption
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •