Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Spreadsheet (2000)

    I posted a similar question as this over a year ago ( <post#=267874>post 267874</post#> )and received help from a number of people.

    I need to import a spread sheet into a number of tables in a database. I've learnt a lot since then but this time the problem is further complicated by a many to many relationship in the database.

    I've tried importing sections of the spread sheet and re-exporting the ID numbers all to no avail.

    Database and spreadsheet (cutdown) attached.

    Any guidence appreciated.

    Regards

    Graeme

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import Spreadsheet (2000)

    Would you describe what fields from the spreadsheet are meant to go into what fields in the various tables.

    Is an Order defined by a Distinct SiteName and UpliftDate?
    If so, then import the sheet into a temporary table that also holds SiteID and MaterialsID.
    Set the SiteID and MaterialsID in the temporary table.
    Finally populate the tables in order of:
    tblSite - distinct sites from the temporary table
    tblMaterials - distinct Materials from the temporary table
    tblOrder - distinct SiteID/UpliftDate entries from temporary table
    tblItem - one for one from the temporary table

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Spreadsheet (2000)

    Pat

    Thanks for the response, sorry for the delay in getting back to you, it's been all go here!!!

    Interesting that you had an input on the other similar problem too. Do you have a special interest in spreadsheet inporting?

    I might have cut down the database a bit too much to get under the 100Kb, there are a lot more tables and a lot more fields. An order is defined by OrderID in TblOrder and referenced by the outside world by an order number comprising of the Order ID and the date the order was input.

    I'll try the temporary table thing tonight and post again tomorrow.

    Regards

    Graeme

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import Spreadsheet (2000)

    >>Do you have a special interest in spreadsheet inporting?<<
    Clients keep asking me to import their spreadsheets into Access. After a while you tend to get quite good at it, what with all that practice.

    >>An order is defined by OrderID in TblOrder and referenced by the outside world by an order number comprising of the Order ID and the date the order was input.<<
    I understand what an Order is, what I meant was, is an Order defined in the spreadsheet by SiteID and UpliftDate.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Spreadsheet (2000)

    Pat

    Once again, sorry for the delay in getting back to you.

    I

  6. #6
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Spreadsheet (2000)

    Well we finally have all the information in the spreadsheet to import it!

    I've added all the SiteIDs from the database TblSite table and all the PeriodIDs from the TblPeriod table and saved a spreadsheet which is set out as per the TblOrder table in the database.

    It was my intention to import this spreadsheet into the TblOrder table in the database to generate the OrderIDs. Then export this to create a spreadsheet to import into the TblItem table in the database. Job done!!!

    Trouble is, it didn't work. All was going well until I clicked the finish button, then I got an error message telling me that the file could not be imported. No reason was given.

    I've attached the spreadsheet to import and the back end of the database.

    Any help would be greatly appreciated.

    Regards

    Graeme

  7. #7
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Spreadsheet (2000)

    Ok, the error was because I had a ? in a date field.

    I copied the spreadsheet and pasted it into the table and it worked, other than the records with the above problem. I'll sort those manually.

    I should now be able to export the TblOrder table, OrderIDs included and put a spread sheet together to import into the TblItems table. This is the bit I had a problem with at the begining of this thread, with the many to many relationship. But I've got my head round it more soundly now.

    Watch this space!!

    Regards

    Graeme

  8. #8
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Spreadsheet (2000)

    Pat

    Well that wasn't too successful! Each row in the spreadsheet will end up as a separate order in the database. I bet you knew that would happen!

    At least now I understand your original question. Each order in the spreadsheet is defined by the Issue Voucher number. Problem is some of them are missing since the issue vouchers were only one source of information.

    The only way to give every row some sort of order ID would be to create a new field and populate it using whatever information is available in each row. Then I can attempt to import the spreadsheet as per your first reply.

    Please let me know if there

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Import Spreadsheet (2000)

    From what you have described, it seems to me that you have a handle on the problem.
    When you say you import into a table then export it out to Excel then import that into Access. This seems a strange way to go, but then maybe I don't understand the problem.
    What could be a solution is to import into a temporary table then do a series of appends to populate the other tables, there should be no need to go back to excel.

    What's confusing me is the 3 Excel sheets that you have defined in the various posts.

  10. #10
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Spreadsheet (2000)

    We

  11. #11
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Spreadsheet (2000)

    All done!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •