Importing data from spreadhseet (1 Viewer)

[ajaymansata]

hi

Well i have a database and tables with me.I have a spreadhseet which has data in it.I would like to import that data into my database and also maintaining the relationships.
Can anyone tell me how can i do it.
Can it be done using wizards or will i have to write some code.
Thanks in advance
Ajay Mansata

 

[neileg]

The import bit is easy using the Get External Data wizard from the File menu.

What do you mean by 'maintaining the relationships'?

 

[ajaymansata]

data from excel

Hi
I have an access database with say three tables
1>Customer
2>vendor
3>Inventory

Now customer and vendor table have an id field which is an autonumber field
Now in the inventory table i have itemid , customerid, vendorid and quantity.
Now i have an excel sheet with data in it.The excel sheet has lot many columns.
Now i wish to extract data and feed them into the tables.
But when i fill up the tables the relationshipo should be maintianed.
For e.g when i fill up the customer and vendor table their id's are automatically generated.
So when i wish to fill up inventory table is there a way where the autogenerated numbers are automatically filled in when retrieving data
Ajay

 

[neileg]

OK, couple of issues here.

When you import the data, so long as you append it to the existing tables, the relationships between the tables will be maintained. Data appended to a table with an autonumber field will be autonumbered. Don't try and write a number into the autonumber field.

Secondly, I think you have relationships in the data in your spreadsheet and you want to replicate these relationships in the database, yes? Exactly how you do this will depend on how the data is structured in the spreadsheet. If each inventory item is associated with only one customer and one supplier, you are going to have to import a key piece of data for each of these links. Once the data is imported, you can run an update query to lookup the key in the customer table and insert the autonumber for that customer. Similarly, do this for the vendor.

When you do an import like this, you can't rely entirely on the wizards. You will need to do some manipulation in Access. Sometimes, a bit of work on the Excel tables can make the job easier too.

If you need more help, come back to us.