CODEWITHSUNDEEP
databaseexcel-2003ms-access-2003import-from-excel
Keeb13r
Keeb13r

Reputation: 235

Importing Excel spreadsheet data into existing Access DB

I've designed an Access 2003 DB with 3 tables: APPLICATIONS, SERVERS, and INSTALLATIONS. Records in the APPLICATIONS and SERVERS tables are uniquely identified by a synthetic primary key (in Access, an "auto number"). The INSTALLATIONS table is essentially a mapping table between APPLICATIONS and SERVERS: it's a list of records of which applications are installed on which servers. A record in the INSTALLATIONS table is also identified by a synthetic primary key, and it consists of an APPLICATION_ID and SERVER_ID for the records in their respective tables.

I have an Excel 2003 spreadsheet I would like to import into this database, but it's proving difficult. The spreadsheet is made up of several tabs/worksheets, each one representing a server with its own listing of installed applications. I'm not sure how to proceed with an import - the "Get External Data --> Import" feature in Access has an import "In an Existing Table" option, but it's greyed out. I'm also unsure how I build the relationships between applications and servers for importing records into the INSTALLATIONS table.

I had previously fooled around with adding some security to the Access DB file. I think I removed everything but perhaps I didn't and that's causing the problem?

Some sample data from the Excel spreadsheet:

SERVER101
* Adobe Reader 9
* BMC Remedy User 7.0
* HostExplorer 2008
* Microsoft Office 2003
* Microsoft Office 2007
* Notepad++

SERVER102
* Adobe Reader 9
* DameWare Mini Remote Control
* Microsoft Office 2003
* Microsoft .NET Framework 3.5 SP1
* Oracle 9.2

SERVER103
* AWDView
* EXTRA! Personal Client 32-bit
* Microsoft Office 2003
* Microsoft .NET Framework 3.5 SP1
* Snagit 9.1
* WinZip 12.1

The Access DB design is very simple:

APPLICATION
* APPLICATION_ID (autonumber)
* APPLICATION_NAME (varchar)

SERVER
* SERVER_ID (autonumber)
* SERVER_NAME (varchar)

INSTALLATION
* INSTALLATION_ID (autonumber)
* APPLICATION_ID (number)
* SERVER_ID (number)

Upvotes: 0

Views: 3789

Answers (1)

user571545
user571545

Reputation:

If importing into an existing table isn't working, I would recommend importing the data from "Get External Data --> Import" into a new table. Then, run an INSERT INTO query to insert the data into your tables as needed.

You will have to do one import per worksheet, if you import them this way.

If you post some structural data then I may be able to help with the relationships between applications and servers.

EDIT: SQL Queries

So to import the Applications into the APPLICATION Table, first make a query:

SELECT *
FROM [SERVER101]
UNION
SELECT *
FROM [SERVER102]
UNION
SELECT *
FROM [SERVER103]

Then run INSERT INTO:

INSERT INTO
[APPLICATION] (APPLICATION_NAME)
FROM [Query] ([Field Name])

To populate the INSTALLATION table, working with a table for each server, you really don't need many joins. Here's what I think would work, for the SERVER101 table:

INSERT INTO
[INSTALLATION] (APPLICATION_ID, SERVER_ID, ?other fields?)
FROM [APPLICATION] (APPLICATION_ID, 101, ?other fields?)
INNER JOIN [SERVER101]
ON [SERVER101].[Application Name] = [APPLICATION].[APPLICATION_NAME]

And I would just do data entry on the SERVER table, unless you have a separate spreadsheet describing the servers.

Upvotes: 0

Related Questions