Import from Excel

W

williamr

I have an Excel spreadsheet that has a name, address, city, state & zip
columns, then it has columns for contributions made between 1985-2006 for
these people. Not all rows are filled in. How do I make this spreadsheet 2
tables, 1 for the names, and 1 for the contributions?
Thanks
 
N

niuginikiwi

Before you proceed, make sure that the data in the spreadsheet is arranged in
an appropriate tabular format, and the spreadsheet has the same type of data
in each field (column) and the same fields in every row.

Open a database, or switch to the Database window for the open database.
Do one of the following:
To import a spreadsheet, on the File menu, point to Get External Data, and
then click Import.
To link a spreadsheet, on the File menu, point to Get External Data, and
then click Link Tables.
In the Import (or Link) dialog box, in the Files of type box, select
Microsoft Excel (*.xls).
Click the arrow to the right of the Look in box, select the drive and folder
where the spreadsheet file is located, and then double-click its icon.
Follow the directions in the Import Spreadsheet Wizard dialog boxes. If you
are importing from a Microsoft Excel version 5.0 or later workbook, then you
can import from one worksheet within a workbook. You cannot import from any
other multiple-spreadsheet files, such as Microsoft Excel version 4.0
workbooks. To import from these files, you must first save each spreadsheet
as an individual file.
 
R

rms

There are a couple of different ways to do this. One way would be to link
the excel file as a table and do a couple make table queries, but I think an
easier way would be to break apart the information in excel as two separate
sheets before importing into Access.

I'm assuming your excel file has duplicate names and you are trying to make
a table of just the names without having any duplicates, which you would use
to link as a parent and create referencial integrity for your contributions
table.

In excel copy the name, address, city, state and zip columns into it's own
sheet with the name field in column A and sort based on the name in ascending
order. Be sure to have column headings at the top of all columns. Insert a
column between A and B so that Column B is now blank. In cell B1 type a
header title of "Sort". In cell B2 type the following formula.

=if(a2=a1,1,0)

Copy the formula in column B down to the end of your data in column A. This
returns a value of 1 next to every duplicate entry if you've previously
sorted column A.

Highlight column B, right click and Copy, right click a second time and
paste special values. This will allow you to sort by column B and get all
the 1's either at the top or bottom of your dataset. Since the data with a 1
next to it in column B is your duplicate data, go ahead and delet those rows.
You are left with a pure list of names without any duplicates. Delete
column B, since you don't need it anymore and import this information into
Access.

If you have data where the names are equal but the address are different,
concatenate the names and addresses into one cell in another column and write
the formula based on that column.

Good Luck,

Rick
 
Top