Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2011
    Location
    philadelphia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can I import an Excel File into Access?

    I am new with this. I likely am posting this question at wrong place. Need help with Microsoft Access. Can one imports the the Excel formatted file into the Microsoft Access? If yes, c an yo tell me where I can find training/Help to facilitate this? Thank you!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lakusiso,

    Welcome to the lounge as a new poster.

    Yes you can import Excel tables into Access. Actually, you have two methods of doing this.
    1. You can Link to the table which allows you to use the Excel data in Access with out actually putting the data there or...
    2. You can Import the data into an Access table then you no longer need to keep the Excel table if you don't need it.

    The following instructions are for Access 2003 {they will vary slightly for 2007 & 10 due to the ribbon interface}.
    1. Select File from the menu
    2. Select Get External Data
    3. Choose Import... (for option 2 above) or Link Tables... (for option 1 above).
    4. Follow the prompts forward.

    Please note that your data table in Excel should have Column Labels as these will be used for the Field names in Access, of course you can change them later in the case of Importing.

    Good luck and post back if you have more questions. For the Help page in Access type "Importing Excel" into the help box.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    In addition to RG's response, you cannot import cells containing formulas. Access performs calculations in a completely different way, so you import data but not calculations.
    Regards
    John



  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Seems the import has been done already: http://windowssecrets.com/forums/sho...-Imported-File. This post is previous to the other one.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by lakusiso View Post
    I am new with this. I likely am posting this question at wrong place. Need help with Microsoft Access. Can one imports the the Excel formatted file into the Microsoft Access? If yes, c an yo tell me where I can find training/Help to facilitate this? Thank you!
    Yes you can, and others have mentioned how you can directly import an Excel file or link to it.

    But quite frankly, you may run into problems with certain fields, as Access assumes it is a specific datatype based on how Excel is handling it (often only looking at first 10 rows or so). You can't direct Access to handle it a different way. That is why I often export the spreadsheet from Excel into a comma-delimited text file first, then import the text file. I can now use import specifications to precisely define particular columns.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Mark I suspect that you have done so many programmed automated imports that you have forgotten that manual imports are a bit different. With a manual import from Excel you do get an opportunity to specify the data type.
    Regards
    John



  7. #7
    New Lounger
    Join Date
    Nov 2011
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    The answer was right, but the question was too vague

    Just like in "The hitch hikers guide to the galaxy" I believe that The answer was right, but the question was too vague.

    The corporate situation which I have seen often is that It departments that want to protect their turf provide no database access to internal tools, but provide extracts in excel form. Then great amonts of time is lost trying to analyze this date with excels tools which are great for on-shot but impossible to use all over.

    So the tool which would really be needed is the tool to import an excel table (automatically detecting the field types and lengths) and perhaps even figuring out how to structure the data. But that is not all. For next mont the tool should be able to update the table with the new data fro the month. Of course the peron exporting the data next month has teken care to change the filed names, so the tool should be able to figure out if: columns changed places, column names are almost the same (punctuation was added, spaces were added, etc, etc).

    Since I'm doing some acess and excel Vba I'm doing that for the moment, but it sure would be interesting if a stable freeware existed. The files I get every month are 8 megabutes, so the import takes quite some time.

Posting Permissions

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