Excel to MS Access 2010

Topic

New Reply

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,

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote