Formatting Issue RRS feed

  • Question

  • I work for a company that operates stores in multiple divisions. We use a program called essbase to retreive information on:

    Store # (4 digits)

    District # (3 digits)

    and Division

    the data retreive looks something like this:

    0008 - 7375 Centenial, Co
    0017 - 201 Englewood, Co
    0029 - 4950 East Denver, Co
    168 - Colorado Springs

    2648 - THATCHER, AZ
    2834 - TUCSON, AZ
    097 - W TUCSON&SE AZ

    So what its doing is spitting out first, the store numbers and addresses (#### - address), then a 3 digit district number (### - District location) , and then finally the division in which the stores and districts are located.

    My problem is: I'd like excel (in a new column using a formula based on text characters) to somehow tell me that everything above 'Denver' to give me a number associated with the 'Denver' division, anything above 'Phoenix' to give me a number associated with the 'Phoenix' division. If anyone has any creative ideas on how to accomplish such a thing, please let me know. Thanks!

    Wednesday, August 22, 2012 4:18 PM

All replies

  • Could you please elaborate your requirements, unable to get how you want the output?
    Thursday, August 30, 2012 7:32 PM
  • It easily can be done in the BizTalk in such steps:

      • convert text document to Xml using Flat File disassembler.
      • transform Xml to the desired format using Xslt map
      • convert Xml to .csv format using Flat File assembler

    Similar steps can be done in .NET code by using the hand made parser for steps 1 and 3. I think you can find the open source parsers for it in internet.

    Leonid Ganeline [BizTalk MVP] BizTalkien: Advanced Questions

    Friday, August 31, 2012 4:19 AM