Trying to create line items records from a string

  • Hi all-

    I've got an invoice coming in as a single sting and am trying to figure out how to best break the string up into individual line item records:

    The string looks like this (disregard carriage returns):

    HEADER*CompanyA*CompanyB*5001*8/22/2009^

    CATEGORY*Parts*PRT^

    LINE*PartA*11^

    LINE*PartF*5^

    LINE*PartG*37^

    CATEGORY*Supplies*SUP^

    LINE*SupplyK*1^

    LINE*SupplyY*88^

    LINE*SupplyG*72^

    CATEGORY*Materials*MTR^

    LINE*MaterialQ*202^

    And I'm trying to get the resultant table to look like this:

    DATE | ToCompany | FromCompany |InvoiceNum | ItemCategory|ItemCategoryCode| ItemDescr|ItemCode

    8/22/2009 CompanyA CompanyB 5001 Parts PRT PartA 11

    8/22/2009 CompanyA CompanyB 5001 Parts PRT PartF 5

    8/22/2009 CompanyA CompanyB 5001 Parts PRT PartF 37

    8/22/2009 CompanyA CompanyB 5001 Supplies SUP SupplyK 1

    8/22/2009 CompanyA CompanyB 5001 Supplies SUP SupplyY 88

    8/22/2009 CompanyA CompanyB 5001 Supplies SUP SupplyG 72

    8/22/2009 CompanyA CompanyB 5001 Materials MTR MaterialQ 202

    Any suggestions would be GREATLY appreciated.

    Thanks

    Nick

  • Hi Nick

    You could try the following. It makes use of PARSENAME and Jeff Modens DelimitedSplit8K function that can be found here[/url]

    declare @line varchar(8000) = 'HEADER*Company.A*Company.B*5001*8/22/2009^CATEGORY*Parts*PRT^LINE*PartA*11^LINE*PartF*5^LINE*PartG*37^CATEGORY*Supplies*SUP^LINE*SupplyK*1^LINE*SupplyY*88^LINE*SupplyG*72^CATEGORY*Materials*MTR^LINE*MaterialQ*202^';

    select

    -- Header components

    replace(

    parsename(

    replace(replace(replace(

    substring(@line,1,CHARINDEX('^',@line)-1) -- HEADER LINE

    ,'HEADER*',''),'.','^'),'*','.')

    ,1)

    ,'^','.') InvoiceDate

    ,replace(

    parsename(

    replace(replace(replace(

    substring(@line,1,CHARINDEX('^',@line)-1)

    ,'HEADER*',''),'.','^'),'*','.')

    ,4)

    ,'^','.') ToCompany

    ,replace(

    parsename(

    replace(replace(replace(

    substring(@line,1,CHARINDEX('^',@line)-1)

    ,'HEADER*',''),'.','^'),'*','.')

    ,3)

    ,'^','.') FromCompany

    ,replace(

    parsename(

    replace(replace(replace(

    substring(@line,1,CHARINDEX('^',@line)-1)

    ,'HEADER*',''),'.','^'),'*','.')

    ,2)

    ,'^','.') InvoiceNum

    -- Category Components

    ,substring(categoryLine,1,CHARINDEX('*',categoryLine)-1) category

    ,substring(categoryLine,CHARINDEX('*',categoryLine)+1,CHARINDEX('^',categoryLine)-CHARINDEX('*',categoryLine) - 1) categoryCode

    -- Item Parts

    ,Item

    ,ItemCode

    from

    -- FETCH EACH OF THE CATEGORY LINES OUT

    (

    select ItemNumber categoryNum

    ,Item categoryLine

    from dbo.DelimitedSplit8K(REPLACE(@line,'^CATEGORY*','~'),'~')

    where itemnumber > 1

    ) c

    -- AND CROSS APPLY EACH ITEM LINE TO THEM

    CROSS APPLY (

    select

    ItemNumber ItemLineNumber

    ,REPLACE(

    PARSENAME(

    REPLACE(REPLACE(REPLACE(Item, 'LINE*',''),'.','^'),'*','.')

    ,2)

    ,'^','.') Item

    ,REPLACE(

    PARSENAME(

    REPLACE(REPLACE(REPLACE(Item, 'LINE*',''),'.','^'),'*','.')

    ,1)

    ,'^','.') ItemCode

    from dbo.DelimitedSplit8K(categoryLine,'^')

    where itemNumber > 1

    and item <> '' --remove empty line at the end

    ) ip

    Hope this helps

    Edit: messed up link

  • Thanks a lot. Exactly what I'm looking for. Much obliged!

    Best

    Nick

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply