Hi Jim,
It can be done, but not simply - not least because Access doesn't allow
more than 255 fields in a table.
By the sound of it you have the names or IDs of the organisations across
the top row of the worksheet, the names of the attributes down the left
column, and the data at the intersections.
Here's how I'd probably tackle it:
1) Scrutinise the organisation names or IDs to see whether they are all
valid as Access fieldnames (For best results, use only alphanumeric
characters and underscores - no spaces or punctuation - and keep them
reasonably short - 64 characters is the absolute limit but 24 or so is
more practical). Also look out for duplicate names.
2) If the names don't pass those tests, insert a new top row and put a
unique valid fieldname into each cell (e.g. a shortened version of the
company name).
3) By now you should have a top row of valid fieldnames, one per company
(whether they are the actual names or the unique names you created at
(2). Make sure the top left cell also contains a valid fieldname such as
"Attribute".
4) Now insert a new second row, immediately below the top row with the
fieldnames. Every cell in this row must contain a text value; it doens't
matter what (I usually use "DUMMY"). The purpose of this row is to avoid
field type conversion problems by forcing Access to import all fields as
Text.
5) Next import the data into Access. It will go into a table (I'll call
it tblAttributes) with one field per company (using the fieldnames we
set up above) and one record per attribute.
6) The next stage is to import this "wide" data with multiple companies
in each record into a "narrow" table with one company and one attribute
per record.
First, create a new table (tblNarrow) with an Autonumber primary key
field called ID and three text fields called CompanyID, AttrName,
AttrValue. Then create an Append query that appends all the data for the
first company. If the fieldname for the company is "ABCInc" the query
will look like this:
INSERT INTO tblNarrow (CompanyID, AttrName, AttrValue)
SELECT 'ABCInc' As CompanyID, Attribute, ABCInc
FROM tableexpression
We're using the company name(or abbreviation) twice, one to generate the
value to put into the "narrow" table, and once to specify the column in
the "wide" table.
After appending the data for the first company, modify the query to get
the data for the second and repeat for all companies. Don't worry if
this is creating a large number of records: Access can easily handle
millions of them.
6) You now have the data in a simple structure where each record
contains of a value, the attribute it represents and the company it
applies to. From here by using further queries it's possible to
manipulate the data in just about any imaginable way.
Probably you will want to start by creating tables that represent the
real-world objects, such as a tblCompanies with their names and
addresses. If you need help with this step, post to the tablesdbdesign
newsgroup with more information about the data.
Hi all,
I have a bunch of data about a bunch of organizations with the
attributes in the first column (as opposed to the first row) and would
like to know if I can import it into Access using column headers. It's
formatted this way because I have more than 256 attributes for each
organization. Thanks much in advance!
Jim Lee
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
