Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a data source that groups data by company, with several rows of data for each company. I want to get the data into a flat file (one record/Row) for each company to more easily join the data to other data tables. I could live with multiple records for each company (as is currently shown, but still need to be able to get the identifying information (Company Name) on each line and remove all the extra field names/spaces in the current worksheet. I've attached a sample of how the data is downloaded. There are about 70 companies with data, currently taking some time to format the data to use in a database. Plus, since the data is updated periodically, I would like a solution that can be easily used for each update. I am (currently) limited to using Access 2003 and/or Excel 2003.

    Any help would be appreciated!

    David
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    David,

    Attached is your worksheet with a macro added that will flatten the data. Note: I changed the field value for the beginning & ending fields in each set for visual verification of the flattened data. The flattened data appears in sheet 2.

    Note: You will have to add the field names to the flattened sheet by inserting a row 1.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I take it the Excel spreadsheet is the incoming format, and you have to start with that. Unfortunately the data isn't normalized at all, so doing something to create a flat file record is not a trivial task. If the format of the data is always in exactly that format, then you could use VBA to create a flattened record for each company. I would do it using Automation to Excel by opening the workbook and then extracting data from each of the cells and putting it into a flattende record in Access. If you aren't comfortable out Automation 101 Tutorial would be helpful. If you aren't really comfortable with VBA in Excel, you could record a macro while you manually copy cells to a flattend row in Excel, and then use the resulting VBA to construct the Access record inside automation. For that matter you could also just run the VBA in Excel and then link to the Excel sheet and use an append query to put the data into and Access database. And Office 2003 will do this sort of thing just fine.
    Wendell

  4. #4
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your quick response. I haven't worked with macros much or with VBA at all, but there are a couple people I work with that looked at the responses and are helping me work with my live data.

    I appreciate your help!

Posting Permissions

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