Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel to MS Access 2010

    Greetings,

    We use a third party application for data definition purposes.

    In order to export to the third party application:
    1. It’s limited to 10 fields
    2. The file has to be in CSV format

    Currently, we use Excel 2010 to input information (very time consuming). I’ve attached samples in both excel and CSV format.
    My goal is to use access to accomplish the above and any help is greatly appreciated.

    TIA

    Regards,
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Jul 2013
    Location
    Hamilton, ON
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your Excel format is far from being in a normalized state to make it possible to import to Access.

    If all the information in your spreadsheet is replicated in the 3rd party DD application, could you extract the data for use in Access tables? This would save you lots of time and grief.

    If not, then you will have to normalize cells that contain multiple data elements, example: cell B2 should be rationalized into separate cells containing the attributes: Description, Workflow, Keep History, Example Values, Form (multiple parents), Field name, Comments, Notes, Status. It would be possible to build a VBA Excel macro to parse a cell like your B2 into the necessary components.

    Once you create the necessary data structures to hold your data dictionary in Access then you can use VBA to extract and format the data for the 3rd party application. You might get some ideas by searching for some Access templates that manage assets.

    Of course, the best answer is to enter directly into your 3rd party application.

    Regards, Rick

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,513
    Thanks
    3
    Thanked 47 Times in 47 Posts
    It would be helpful to understand the purpose of the third-party application - does it document the design of another database, or is the app a database that is a user application? I agree with Rick that your Excel format is not conducive to Access table structures, although with multi-value fields in the .accdb format you could slam it into Access. It would be possible to build Access forms that make the data entry process easier, but you are still capturing a great deal of detail, and it's not clear to me that it would make the task significantly easier. Maybe some "outside the box" thinking is in order.
    Wendell

Posting Permissions

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