Excel 2016 - Import to Access - How to Export from Microsoft MS Data to Database - Transfer Tutorial
ฝัง
- เผยแพร่เมื่อ 21 เม.ย. 2017
- In this tutorial I show you the best ways to export your Microsoft Excel 2016 spreadsheets to Microsoft Access. I show you the best way to fill out your Excel workbook for exporting to Access. This demo is done using Windows 10. Importing and Exporting is a key feature of the Microsoft Office 365 suite, and this tutorial is meant for beginners who would like to learn how to use the program more efficiently. I also discuss how to import and append an excel spreadsheet into a current Access table.
Here is a full list of tutorial videos available on my channel:
Windows 10:
Perform Basic Mouse Operations
Create Folders
Explore the Windows 10 Desktop, Taskbar, and Start Menu
Select Multiple Files and Folders
Download a File From a Website
Use File Explorer to Access OneDrive and Upload Student Data Files
Word 2016:
Create a New Document and Insert Text
Insert and Format Graphics
Insert and Modify Text Boxes
Create a Table
Format a Table
Present a Word Document Online
Create a Research Paper in MLA Format
Insert Footnotes in a Research Paper
Create Citations and a Bibliography
Save a Document
Correct Errors as You Type
How to Format a Document in APA Format
Convert Word Document to a PDF File
Microsoft Office Specialist Certification Exam Practice Study Guide
APA Format from Default Formatting
Table of Contents Tutorial
Format Paragraphs
Create a Custom Word Template
Excel 2016:
Create, Save, and Navigate an Excel Workbook
Enter Data in a Worksheet
How do you Export Access to Excel and Apply Conditional Formatting
Use Flash Fill, SUM, Average, Median, and MAX Functions and Formulas
Move Data and Rotate Text
Graph Data with a Pie Chart
Format a Pie Chart
MOS Prep - Basic Certification Exam Practice Study Guide
Change Fonts, Font Style, and Font Color
The NOW Function
Export Excel Spreadsheet to Access Table
The VLookup Function
The MIN or MINIMUM Function
Histogram Charts
Use the Sum Button to Sum a Range of Cells
Enter Formulas Using the Keyboard
Access 2016:
Identify Good Database Design
Create a Table and Define Fields in a Blank Desktop Database
The Primary Key
Import Excel Spreadsheet into Access
Create a Table in Design View
Modify the Structure of a Table
Create a Subform
MOS Prep - Basic Certification Exam Practice Study Guide
Add Existing Fields to a Form
PowerPoint 2016:
Create a New Presentation
Edit a Presentation in Normal View
Add Pictures to a Presentation
Format Numbered and Bulleted Lists
Customize Slide Backgrounds and Themes
Animate a Slide Show
Apply a Theme Used in Another Presentation
Search for and Download an Online Theme
Outlook 2016
Basic Tutorial
TH-cam Analytics:
100 Subscribers
200 Subscribers
300 Subscribers
500 Subscribers
Computer Fundamentals:
Computer Case Types - Dell Inspiron AMD 3656
Printer Ports and Types
The Boot Up Process
How to Get Your Computer Questions Answered
Undo Your Mistakes on Windows 10 or Apple Mac
Routers vs. Modems
What is the Cloud? Storage as a Service
Types of Internet Services on Google Android or Apple iPhone
Browsing the Web
Why Use the Cloud?
Microsoft OneDrive - Creating Uploading Downloading and Syncing
Explain the Importance of File Management
Troubleshoot Common Computer Problems
Job Search Skills:
Values, Attitude, and Goals
Top 5 Job Search Websites
Prepare For Your Interview
Negotiating Your Salary
Video Requests:
Download GMetrix Test Preparation Software
Remember, the goal of my channel is for you to learn. You can request a video at any time in the comment section, and I will make the video for you. I will make tutorials and simulations and demos for whatever you'd like to learn in our class. So, I encourage you to make a request. I also TH-cam Live Stream once a week to answer your questions!
Instructor A Morgan
access
Excel
to
2016
import
how
export
in
data
spreadsheet
importing
from
ms
and
microsoft
into
database
file
office
convert
converting
sheet
an
table
365
transfer
tables
use
as
acs
windows
10
save
tutorial
360
spreadsheets
or
of
is
possible
imports
exports
name
worksheet
for
users
exporting
adding
move
entire
columns
job
costing
make
program
permission
input
As someone who is frantically watching your videos during my timed integrated word/excel/access project, thank you oh Microsoft gods 🙏🏻🙏🏻🙏🏻
you just saved 10 students their lives, thankyou legend 🤍👌👍
man i could not figure out why my excel file wasnt appending to my table. until i patiently watched your video and you said you have to spell it right. turns out i misspelled some words and mixed up some things. thank you so much man.
I appreciate the efforts you do to make these concise and apt videos. More to come from you.......Great work
Thanks
Hi, Professor Thank you so much for this video! I am having trouble Importing my file, but thanks to your video I think I got it.
No problem Angelina, thank you for watching. I'm glad you were able to import your file, have a nice day!
Nice job staying on point while explaining in sufficient detail.
thank you for the video. I'm having trouble in creating a database for my calibration lab and this video is really helpful for me.
Hey I'm glad this has been helpful for you Dyana! Thank you for watching and best of luck creating your database.
This was super helpful. Im taking a course and didn't understand Access and your video was great. Thank you.
Hi Michele,
Great, I'm glad my video was helpful for you. Let me know if you have any other questions in your course.
Keep doing all the good works, sir it helps lots of students. Thank you, sir.
Hey thanks Kazi, I'm always happy to help. Thanks for watching and for the comment, have a nice day!
This was extremely useful to convert information from Google Forms to Excel and then to Access. Thanks!
Hi Phychological,
Yeah, luckily those 3 programs all work pretty well together so I'm glad this worked for you. Thanks for watching!
Thank you so much professor this has been really helpful you saved me a lot of time.
Thank you so much. Even though this was not for me, it still helped.
No problem Sabrina, I'm glad you found it helpful. Thank you for watching.
Thank you very much. Your video explained exactly what I was looking for. Thanks!
Dude! Nice job on cutting through the muck and teaching an otherwise sermon to a quick text with an emoji at the end 👌🤣👍 well done sir!
You saved many lives today my brother!!! Da wordt ne traktatie op foubert eh moatt 👍
kom mij trakteren a broeder
U really know how to explain...,I loved the video and I have gotten the concept,,thank you so much
Adam, thank you for your video! It is very helpful. I haven't used ACCESS for over 20 years and needed some brushing up. So thankful i found your videos.
However, I am coming across an issue. I am importing my excel file and it has several rows that are duplicated in columns. Such as the SSN and Name. The rest is not duplicated. Then, my file won't import. The error i am getting is: type conversion failure.. do you have a video for such error?
Thanks Adam!!! it was easy to upload excel to Ms Access
Kudos
This video saved me lol... thanks for your help! 🙏
Thank you so much for the tutorials. It has been so helpful.
I'm glad the videos have been helpful. Thank you for watching and have a nice day!
Thanks man. I realized where I was doing wrong when I watched this.
No problem Ramazan, glad this video helped. Thanks for watching!
Great videos, if is possible can you do a series on creating a simple Vehicle Maintenance Access database, to keep track of repairs or maintenance, gasoline usage, etc, I haven't found one in the web or in youtube. Thanks
Hi, My name is Tyrone and Love your videos. At age 60 am deciding to build a database in Access. After building a Table 30 fields am having problem importing pdf files or text files in to my present Table in Access and also will like to update and keep the previous data for records to look at. Thank you
All right, man! Thank you for your attention.
No problem Prof. Joaquim, thank you for watching!
Thank you for making it so simple. Helped me
HI, Great tutorial, I need to import multiple excel files into Access (approx 50). Can you advise if there is a quick way to bulk import? Thanks
Very nice video tutorial, I've been search this tutorial. thank you so much. Can you make tutorial for Account Receivable with details of customer? tks.
Hi, i used Access back when i was in college, but at work im getting back into it as it should be easier than excel for our data analysis. I'm struggling to get my monthly data to import to the same table. Each month i will have additional new customers and right now i have customer name as my primary key. During my import from excel into an existing table, Access provides an error message stating # records lost due to key violations. Do you have any suggestions on how to best upload data monthly and what to select for a primary key?
Thanks your videos are super great!
Thank you Adam. What if my backend has a unique numeric identifier choose automatically by access. How to generate this number
Thank you, Professor Morgan. Great videos.
subscribed and still surfing on ur channel's videos (Access), have a great day!
Thank you for subscribing Deny, that really helps out my channel! I'm glad you're enjoying my Access videos!
Hello thanks for sharing this video.. I am beginner to Ms access.. wat skills i must know in access as a beginner?
Thanks so much for this video. It was actually a help for me. I was trying to append an excel file into my access table but I was clicking excel under Export instead of under Import & Link.
I'm glad this helped you out, Upton Kanei. That's an easy mistake to make. I'm glad you got it figured out.
hi and thanks. can you advice how to choose few fields from excell and export it to access. thanks again
Thank you for your post and appreciate it!
Hey, I have a question, how do you import a spreadsheet with more than one worksheet? Can you please make a video based on it? Or if not please tell me how to do it. Thanks
Naeem
Excellent ... simple and to the point
Great video. If your getting "subscript out of range" error each time you try importing from a worksheet to a table -- try the radio button for new table instead. It worked for me. I got that notion about 4 seconds into this great vid after being frustrated all night trying one idea after another. Looking for solutions that would fix the "to a table" import problems was humiliating and antagonizing.
Hi Greg,
Thank you for posting your problem and solution. I hope you have a nice day.
I am having this same problem, I’ll try that. Thanks
@@merrymeri4700 Good luck. It still works for me.
Clear and concise!!! Thanks a lot!!!
No problem, Shahin. Thank you for watching!
Thank you for this video! I was having trouble importing from excel for my CIS class and this was really helpful.
No problem, Kat. I'm glad this helped you with your CIS class, thank you for watching.
Hi there, really like your videos. I have a bit of problem. I have data I need to mail merge from Excel to Power Point. Is there a way to do that? I need something similar to Word or Publisher mail merge. Thanks.
Hello Professor Adam; You are my favorite TH-cam teacher that I have found. I would like to know why my Ribbon bar is not active for me to import a workbook from Excel to a table. I click on the External Data icon but the other icons are in color gray. I will apréciate your help.
thank you so much for the tutorials! help me a lot. :)
I'm glad the tutorials are helpful for you Doha. Thank you for watching and have a nice day.
Thank you sir for this. but i have an issue where it stated that field ‘f5’ doesn’t exist in destination. however in my excel and access just consist of four fields . can you help me? tqq
This video helped a lot. Thanks
Very helpful indeed. Thanks Morgan!
That's great to hear, thank you for watching Doris. Have a nice day.
Thanks for this video, it's really helpful. Though I think i have a problem with my laptop, when ever I click on the External data, it doesn't show saved import, I can only see saved export. This has made it difficult for me to import into my access. I'm using ms access 16. Pls is there anything I'm not doing? Pls help me out here.
Thank you so much my man!
Hello
is it possible to receive datas only from excel?
I mean build the DB & Table at Access and handle daily works at Excel then upload excel data to Acess DB weekly or monthly
I mean I when I try upload from
excel , new table is created at Access while my aim is to upload the data to the table that is already exited in access.
Thanks so much, this was very helpful at my job!
I'm happy this helped you out, Laura. Thanks for watching and have a nice day!
Nice thanks...easy to follow
Great instructions! Thank you for making it so simple.
Thank you for watching, I'm glad it worked for you. Have a nice day!
@@ProfessorAdamMorgan Hello Prof. Adam, how do I cross reference my data from excel to my existing database in access? I want to know if my data from excel have the same matches in access. Thank you and great video!
This helped so much thank you
short and to the point like videos like this should be.
I agree, IsNgUp01. Thanks for watching!
so what if you already have data but might have new data in the excel form and you just want to update the access database without duplicating information that was already updated prior, can that be done?
Yes, thank you! This has helped me a lot! 👍🏽
Glad it helped! Thanks for watching, Lola.
Thanks, Pro Morgan! This is very helpful. New subscriber here :-)
Hi, do you know how to create a CV from various tables in access?
Hi sir , will it be possible to import execl and directed screen flow automatically as per rows and columns
Helpful, thank you so much
You are the best! Thank you for this video.
Hey thanks for watching MarCel, I am glad you enjoyed this video. Thanks for watching!
Best tutorial Professor Morgan!
nice work dude. quick and easy to understand.
Hey thanks scholesy1000, I appreciate that. Have a nice day.
Hi.I like your video very much. It's really great. I'll keep an eye on your channel. I am your fan and I will support you.
Thank you, pronounce word. I appreciate you watching, have a nice day!
Another excellent set of videos. Could you make a video on merging two worksheets with similar information? I have two worksheets that contain some identical fields, but other information. I would like to get that information onto one sheet. Thank you.
Hi Mike,
Thank you for watching the videos and for the comment, I appreciate it. My recommendation is to export the two tables to Access, then use a Query to get just the data that you want from each sheet. Here is a video of me explaining combining multiple fields into a query:
th-cam.com/video/YWjQcra9dOc/w-d-xo.html
Hi Professor,
I have a query from an Access database exported to Excel (so i can manipulate the data) and then a link from that excel file back to access. The problem i am having is that the data going back to access doesnt refresh unless i open and close the excel file. I have changed the setting for refresh in excel under the query and the external data menues but it still wont auto refresh. What can i do to accomplish auto refresh without having to manually open excel?
I am doing an append and getting a script out of range is that due to the headers?
Hi Morgan, I would like to make the price list of parts with 7000 items in access with front page to put the part number only and get all the price and other details in the excel format and then make a list and export to excel, Do let me know we can do
Thank you! This video help me a lot! :)
No problem, I'm glad it helped! Have a nice day.
Great video Professor! I was wondering, how do you add bulk prices to Access or import from Excel. I created a database with a unit price and Qty, but would like to add a bulk price so when entering a high Qty the price will automatically drop to the correct price.
Thank you D Bailey,
It's a little hard for me to know exactly what you mean without seeing your worksheet and database. I am not entirely sure how to do that in Access. You can always add a total line to the bottom of your table, but this seems like something that would require a calculation.
How do import the excel data into an existing table but overwrite the entries so that up-to-date data is in the table? Some background: I have an excel sheet with all my data and the data changes every month so I would like to overwrite the old date and insert the new data,.The headings remain the same it's just the data that changes.
Can I import one spreadsheet into Access, then later, add columns to the table that it makes? AND Can I later make a different table and link data from one column in one table to data in another column in the other table. Can an Access DB grow in this manner? Sorry to be a stranger asking questions. Thanks for the video. I'll start watching your other videos. Thanks again, B.
I tried to add more data to an already existing one but when i got to the last page ot says 'subscript out of range' ..please help
Heloo. Just want to ask if there's a way to automatic transfer the data from excel to access? Whenever I'll add something on Excel, it will automatically update to my access? Thank you.
nice easy to follow video., prof. also nice nail polish collection
Hey that is good to know Gerald, I'm glad the video was helpful. Yes, my wife loves her nail polish, glad you like it!
So helpful! Thank you very much!
I'm glad to hear it linhlan, thank you for watching!
Thank you for this easy to follow video. Unfortunately, I still get the error message when I import - The Microsoft Access database engine cannot find the input table or query. Make sure it exists and that name is spelled correctly. How do I fix this? SOS 😅
Thank you so much professor👍👍👍
No problem, Saepul. Thank you for watching!
Saved my life, thankyou
I'm glad it was helpful for you le gran mnu, thank you for watching!
Thanks so much for this video!
Hey no problem Amanda, thank you for watching!
Hi. What are indexes? Is it important to know about them in terms of import? Thanks in advance
Thanks a lot.... This one helped in my exam
No problem Soubhagya, thank you for watching my video!
Awesome!!!!! THANKS!!!!!!!
Yeah Sir thanks you it was helpful
This was a really easy and simple video to follow. When I tried it, it worked beautifully. Thanks a lot! BTW, I have an Excel things I'm trying to do, but don't know how to do some of the things I'm trying to accomplish. How can I get in contact with you to see if you might be able to lend me a hand with it, or just give me some pointers?
Hi Dome,
Thanks, I'm glad this was helpful for you. The best way to ask me questions is through the comment section on videos. I try to respond to all comments and help out whoever I can. As for learning Excel, I already have a pretty extensive playlist that you can view on my channel, I'll link it below. Just let me know if you have any questions:
th-cam.com/video/tkSkIGrijOY/w-d-xo.html
Hello Sir, I it very helpful for me. Thank you so much. 😊
Hi Adam, thank you for this video. I have a question - How shall I Import the excel data from a particular sheet into the access if I have more than 1 sheets in the excel file?
Hi Chetana,
Thanks for watching. When you get to the 2nd step of the Import Wizard in Access, you'll see all of the worksheet names listed at the top. Choose the one you want and follow the prompt as needed. I hope this helps!
thank you good sir a big help
Thx a lot. I'm from Sri Lanka
thnx u so much for explanation
nice clear explanation well paced thank you
Thank you James, glad this video worked for you. Thank you for watching and have a nice day.
really helpful 👌🏼 👏🏼
Hi Adam, Great tutorial!! I have a table in Access that I took and added more columns to it in Excel. Now I want to link it back with the new columns to Access, what is the best way to do so? Also, I don't have a primary key in excel or access data, so I am afraid that when I link both together, they won't be correctly sorted. Is there a way to fix that? Thank you so much
Hi Ali,
Thank you for watching! I would import the Excel table back into Access as a new table, then delete the original. The other option is to add the columns to the original Access table and append the columns, which can be tricky sometimes. If your tables aren't linked with a relationship, I wouldn't worry about deleting the original. You can always resort the table in Access, or create a column in number order in Excel and use that as the primary key. You have some options here, try a few different ways and see which is the best. I hope this helps.
Please help me with a video on computation of parameters
Hi professor Morgan!
Your videos are so helpful and helped me learn a lot, thank you!
I had to use Access for the first time at my job, that’s when I discovered your channel and your videos have been extremely informative and easy to follow.
If it’s possible, can you make a video about macros? I’m trying to make a button that would automate Importing and exporting data from Access into Excel and vise versa (basically automating data ingestion for both softwares). I know it can be done but I don’t seem to figure out how to do it. If you can’t find the time to make a video about it, can you please explain the process or give me the formula (idk if that’s what it’s called?) for it. It’s kind of urgent and I would really appreciate it. Thanks again :)
Or maybe if it’s troublesome for you, please just direct me to a video or a website or anything that could help me achieve what I’m looking for.
Thank you, and waiting for your reply :D
Hi Randa,
I'm so glad to hear that my videos have been helpful for you. Access can be pretty intimidating if you've never used it before, so I'm glad you feel more comfortable within the program.
Regarding Macros, I do not currently have any Macro videos for MS Access. I found a website that could help you with this, so I will link it below. Hopefully it works for you, so let me know. I may be able to make a video on this in the future, but right now I'm still catching up on other student content. Have a great day and thanks for watching!
support.office.com/en-us/article/importexportspreadsheet-macro-action-9973a631-6586-4c1d-9c61-ae167696b750
Thanks you very much sir.
when importing data from an excel sheet into an access table... will the imported data mess up things when the access table your dumping the data into is linked to other forms and queries within that access file? (sorry to be long winded) Joe C
Hi Joe C,
No, the imported table won't link to any other data in your database unless you connect it in the future with a relationship. I hope this helps, thanks for watching.
it was very helpful thank you so much
Hi Roqiyah,
No problem, I'm glad you found the video helpful. Thanks for watching and have a nice day.
Prof Morgan... the access file I am working with has data in the tables presently and is already set up with the tables tied to reports, queries, and dashboard buttons. I want to copy in data from an excel file into an existing table. Trying to save hours entering one record at a time. So when I paste these records in.. will it compromise those reports , etc.?? Thanks for your time sir.
Hi J Cascio,
What I would recommend doing is saving a copy of your database somewhere else on your computer. From there, try to import the information and see what happens. Since it's a copy of the file, it shouldn't cause any problems to the original file. I doubt you'll have issues with the reports, queries, etc. but it's better to be safe than sorry. I hope this helps.
Excellent! Thanks.