Excel 2016 - Import to Access - How to Export from Microsoft MS Data to Database - Transfer Tutorial
HTML-код
- Опубликовано: 2 окт 2024
- 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
RUclips 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 RUclips 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
you just saved 10 students their lives, thankyou legend 🤍👌👍
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!
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!
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.
Hello thanks for sharing this video.. I am beginner to Ms access.. wat skills i must know in access as a beginner?
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!
Very useful. Thanks.
I'm glad to hear that J Rabinow, thanks for watching!
Nice thanks...easy to follow
Best tutorial Professor Morgan!
Nice video. But I seek to append just one column of data from Excel into an already existing Access table. I somehow had that field deleted from Access and now I want to import just that column. Is that possible?
Hi,
Try adding in a new column (field) in Access and name it the same name as the column in Excel. From there, import the data and it should work. I hope this helps, thanks for watching.
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!
Adam, is it possible to have the linking allow updates in both directions? That is if I can update the data in access it is also updated in excel? Thanks!
Same doubt here
Good video, thanks
Thanks for watching, Chris. Have a nice day.
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.
thnx u so much for explanation
Bro you legend you saved my life thank you so much liked and subbed 💪👌
Hey thank you Unique. Glad the video was helpful and thanks a bunch for subscribing. Have a good one!
Instructor A Morgan No worries man your awesome!!
Hey thanks! Have a good one!
Hi. What are indexes? Is it important to know about them in terms of import? Thanks in advance
THANK YOU SO MUCH FOR THIS INORMATION
Hi professor. Is there a way to import excel files to access all at the same time? Also, can I import specific columns from excel to access?
Hi JohnD Great,
I recommend combining all of the files on to one specific Excel Workbook. From there, you can import it all from one file. You can choose which columns to include in the Import settings.
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.
Hi, I need your help. The Import Spreadsheet Wizard dialog box wasn't coming up after importing the excel file to an access database. Thank you.
Hi Jha DC,
Double check that you have an active version of Access on your computer. Access is not available on Mac OS X, so that could be a problem. Otherwise, double check that you used the export wizard correctly.
Hi Professor,
I'd like to append a Excel worksheet to my access table. I have over 2500 records and I do not want duplicates to be recorded. How can I ensure that I avoid the duplicating of records?
Hi Jenifer,
On your Access database, you need to create a Primary Key. Let's say I wanted to import a database of students into another database of students. I would get all student ID numbers, and assign that as the Primary Key. That way, any new imports that have the same ID number as the previous list would not be imported, as Primary Key's cannot be duplicated. Here is a video I made on Primary Keys, I hope it helps:
ruclips.net/video/7lJS5tklOrE/видео.html
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.
Life saver!
Thanks Bryanna, have a nice day!
Hi Adam,
thanks for your videos ..
i hope you can help with my question.
I need to link Access application with google map. so when i select 2 points i get the location names and the direction line between them. then i ll be able to print a report shows the location and the map as picture.
Is that possible dear?thanx
Hi Eyad,
Unfortunately I don't think that it possible. If it is, unfortunately I do not know how to do that. Sorry, I cannot help with this but let me know if you're able to figure this out.
Problem: Example: KeyID is contained in several lists, but some columns are same, some are different. LIST "A" : Customer 1001 Doe | John | 123 Maple Street | Anytown | AZ -- - - - LIST "B" Customer 1001 Doe | John | 333 Pine Street | Anytown | AZ | Purchase date 1/28/19 | Attribute 32 | Attribute 07 ? Would I have to add columns to match so that ALL imported Excel lists are the same? Some Lists have 100 columns, some have 12. If the customer had two different addresses, how would that be handled? You may have video already, I have not found it yet. Thanks!!!!!!
Hi Sam O,
If you're trying to append a table, meaning combine Excel data with a current Access table, all columns and field names will have to be exactly the same or it will not work. If the Excel data has 100 columns, you'll need to make sure that all 100 columns are included in the Access table in order to append. If you're trying to send the Excel data to a new table, you can do it by creating a new table within your Access database. If the customers have 2 different addresses, you'll need to create a column for both addresses, rather than having them in separate rows. I hope this helps you out.
Hello professor I have a question how can I prevent a customers list from creating duplicate entries? BTW I want to thank you and let you know I have enjoyed watching and learning from your videos
Hi Enrique,
It depends which program you'd like to remove the duplicates from. If you want to remove them in Excel, go to the Data tab, Data Tools group, then select the row you'd like to remove the duplicates from, and click Remove Duplicates. If you want to remove them in Access, make the duplicate customer field the Primary Key. If you do that, Access won't allow duplicates to be made in that field. I hope this is helpful for you. Thank you so much for watching my videos, I am glad you're enjoying them. Have a great weekend!
Professor Adam Morgan I am using access 2010
I do follow all these steps, but Wizard message tells me to close the file or use a right format, but my Excel file is closed, and the format is *.xlsx. I can't get it why? I tried to store file on C drive, then in Users folder... nothing works to import Excel file data into Access table :-(((
Update:
I did remove original fields/columns names in the Excel file and retyped the names into the cells above each column (they headers were copied directly from database). Now MS Access could understand and accept Excel file, and all go the same as in Video.
Hi EB,
Yeah the column names are especially important if you're appending data. Thanks for the update, perhaps it'll help other viewers who have the same problem. Thank you for watching!
Hi, I hope you can read this. I am trying to import an XLS file but the characters are in Korean. And when I import it to the database, the data is in "????". How do I get MS Access to be able to read Korean characters? I have already tried installing Korean language under MS ACCESS options. Thank you very much
Hi Cedric,
I'd recommend translating the information into English before you export the file. I'm not entirely sure that the import will work in other languages. I know Word & Excel are supported in many languages, but I'm not sure how an import would effect that. I hope this helps.
How to change acess 2013 to 2016 plz tell in reply not in next video
Thank you so much professor this has been really helpful you saved me a lot of time.
I am doing an append and getting a script out of range is that due to the headers?
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.
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.
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!
I Am unable to import full data of a cell that is more than 100 characters. and Access only imports less than that characters per cell even for Long text formated cell. what should i do?
Hi Bruk,
Go into the Design View on your Access table and change the character limit. Most 'Short Text' fields only allow 64 characters. Change the Data Type to long text and you should be good. Here is a video if you need extra help:
ruclips.net/video/NWMPVmhlgQI/видео.html
I have just done the same as Kanei I have clicked on the export from excel and not the import I have lost my excel data and can't find it anywhere on my computer guess I have lost it?
Hi Joan,
Go to Excel, and click File, then Open. You should see a list of your recent files along the right side. It should be there, as the file won't auto delete even if you export it. I hope this helps.
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!
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
Brother how can we transfer data from ms access form to ms excel in a new sheet. Means when we fill the form in ms access and click on submit button and the data should be save in ms excel new sheet. Please guide me
Hi Ashish,
Sure, here is a video I made on transferring Access tables to Excel, I hope it helps you out:
ruclips.net/video/qXU-nzVPvMs/видео.html
Thanks
No problem!
As someone who is frantically watching your videos during my timed integrated word/excel/access project, thank you oh Microsoft gods 🙏🏻🙏🏻🙏🏻
I am using 365 access to create a database using a Excel sheet it comes up with an error string sold and I cant do a thing I am using a new table
Hi Linda,
I am not too sure about this one. Are you trying to convert the Excel File to Access? I am confused as to where the problem is occurring.
Hi bro,Is it works with template MS access Inventory and Billing ?
Hi 888boss88b,
I'm not really sure what you mean by template. Please let me know with a little more detail what you're trying to do and I'll do my best to help you out.
Hello Professor I am using Microsoft Access (MA) through a software called VMware because I do not have have MA in my personal computer. How do I import an Excel file from my personal computer to MA?
Hi Eric,
I'd recommend exporting your Access database to a .txt (text only file) then importing the .txt file to your personal computer. I hope this helps!
thank you, your tutorials are very helpful. But I have a problem that I haven't been able to solve, I am a beginner in access. I have an excel file that in one column sometimes have an "enter tab in the same line" so, when I export the excel into access, access removes the enter and leave everything in that field in our single line. Ex.
excel has in one field, for example A5:
1234
agbc
in access it will show
1234agbc
It looks like access remove the enter from the fields
Hi Nicolas,
I am glad you're finding my tutorials helpful. Access reads your Excel data as text. When you have an 'enter' or create a new line in Excel, it will not recognize that change since it's a formatting change. The only way to get it to show in a new line in Access is by having it in its own cell in Excel. I hope this helps, thanks for watching.
Hello, I have a question regarding updating files /adding new data into an existing excel file when there is a connection with pivot tables. Whenever I try to refresh a sheet (when I add new data into the datasheet), my file doesn't want to refresh and says: error, cannot open the source file called "xyz". Is there any possibility to update a file and that the pivot table updates automatically without this error. I don't want manually do every month new dashboards. Thank you in advance!
Hi Ivana,
I'm actually not too sure on this one. I would recommend converting the pivot table to a range and seeing if that helps. I'm sorry, but I typically try to make the Excel formatting as basic text as possible before I convert to Access. I hope this helps you, thank you for watching!
Thanks for posting, although for whatever reason it didn't help my project. The error I keep getting when trying to import my data is, "database unrecognizable". I was able to call someone and get help, but you should do a video showing how to use the "Database Tools" button to fix a file. Also, you should do a video on how to change the Filled map in Excel if it's not showing the map you want. For example, I was trying to create a filled map of ONLY California and my excel data was of several different CA counties but it kept giving me a map of the whole US.
Hi Cayla,
Thanks for the suggestion. I will look into this and post up a video. Have a great day and thanks for watching.
I am trying to create a query within a query but not having success can you do a video on this
Can you run each of the queries separately to ensure they work b4 attempting the query within a query? Can you provide the query you are trying to execute?
Hello prof! is it possible to import data from excel file which have the same PK with the Access table? so we are like updating the already existing data in Access instead of adding a new records
Hi Abdullah,
You're probably getting an error because of the following:
1. The primary key you specified has duplicates, and therefore cannot be a primary key.
2. The primary key you're using is in use in another table in the database.
It's a little hard for me to answer this question without seeing your database, but I hope this helps anyway. Have a great day.
Hi, I'm having an issue importing my excel spreadsheet into access. The problem I'm having is my spreadsheet file is a single worksheet a lot of duplicated data and I need to import it into access using 3 tables. However, when I import it and select to index (with NO duplicates) it still wont let me and imports the whole spreadsheet, or imports the specified fields but with the duplicated data. Could you do a video on importing into many tables?
Hi imogen harrison,
So the problem that you might be having is that the field that you're choosing as the Primary Key has duplicates within it. I recommend creating a new Primary Key field with individual unique numbers. You may also have empty cells within your spreadsheet that are causing errors within Access. Here is a video I made on Primary Keys that can probably help you:
ruclips.net/video/7lJS5tklOrE/видео.html
i want to see how it is changing "indexed" & "data type" can you make a video on it please?
i have problem whith it because i can't speake english (i cant understand very well) if you can use this exel again...?
Hi Shpend,
I am a little behind on my video production right now, but I can make a video discussing how data types effect imports. Thank you for the suggestion and for watching this video!
Helo, man! When we transfer data from one Listview1 to another (Listview2, in a different form, as checked by checkbox), how to send Listview2 data to a DB Access table? Many thanks for the excellent tips given.
Hello Prof. Joaquim,
Typically errors in transfer occur when too much formatting happens in Excel. For Excel, just think text-only formatting. Any charts, formulas, things like that sometimes cause transfer errors. Perhaps adjusting the Excel sheet would improve the transfer. I hope this helps.
Thank you for your attention
No problem, have a nice day.
What I'm trying to do is, we have Genesis for dialling and receiving calls. We have a Genesys dashboard to view how many calls are in the queue. There's no notification at that dashboard. Dashboard doesn't have a URL. It has an Ip like 192.168.11.22:8080/gax/plugins/pulse/#/
Ip last two are wrong. I gave you this as an example. This dashboard requires a login password, So what I am trying to do. I want to import this at excel. and want to set a module to refresh it every 5 seconds. And wants to use if function and beep now module. that if calls queue becomes 0 to 1. I should get a beep.
Have you understood my need? So how to import data from this kinda site which requires a login and how to make it refresh every 5 seconds
Hi Seep,
I’m sorry, but I wouldn’t know where to begin on something like this. You may want to look for some answers online, because I cannot help with this, sorry.
Hi Professor Adam, How do I print my forms in MS Access 2016. Mine is printing more than one record per form and I just want one record per form. Please help! Thank you!
Hi Dana,
Unfortunately, forms don't have a lot of great custom print options. I recommend converting the form to a report. Once the report is created, open your Report in design view.
Go to View-Grouping and Sorting
On the dialog form that opens Select the field you want to sort and also select the sorting order.
On the group header select Yes
On the group footer select Yes
Group on - Each Value
Keep Together Yes
Close the form and view the changes in your report design.
right Click on the group footer and go to properties.
On the Format Tab go to Force New Page . Select (After Section) from the drop down.
Save your changes and you are done.
I hope this helps you out.
Thank you
No problem!
Can you please make a tutorial on my case where i have excel and word standard blank forms which i wanted to import in access. I have the table of informations too from excel so that it will automatically fill in the blank forms as i print it. Hope you will help me. Thank you
Hi Franciose,
I'm sorry but I do not know of a way to do that. Since the form style on Word or Excel isn't standard, Access would have a hard time formatting your information into a table. My recommendation is to convert the forms to a table in access and create forms and reports from that table that you can print out. I hope this helps, thanks for watching!
Hi..please help me!i tried to import my excel sheet work to access,but now my original excel sheet is gone and when i tried to open the excel sheet by exporting it,all the work i had done in it is gone!!!i'm new to access...pls help
Hi Shilpa,
So the original file shouldn't have gone missing when you tried to import. My recommendation is to open up Excel, and view the recent files on the left. It should be listed there. If not, press the Windows key on your keyboard and immediately start typing the Excel file name. If it shows up, you can click on the Excel file to open it. If not, you may have deleted it, in which case, take a look in the recycling bin. I hope this helps you out.
@@ProfessorAdamMorgan thanku soo mch for the quick response,but i already tried all those ways yesterday n i couldn't find it!😥anyways i'm doing my work all over again,i guess it's gone forever!😪
No problem Shilpa, best of luck with redoing your work, sorry their isn't an easier solution here.
You saved many lives today my brother!!! Da wordt ne traktatie op foubert eh moatt 👍
kom mij trakteren a broeder
The Excel file I am trying to link to Access has many sheet tabs. How do I specify a tab in the Excel to link Access to?
Hi Bryan,
When you click Excel in the Import and Link group, choose the Excel file you want to import. From there, click Next and it'll take you to a new screen where you can choose the specific worksheet tab that you want to import. It's not shown in this tutorial since I only had one sheet, but in instances of multiple sheets, the step will appear.
How come it doesn't give me the option to append? All it says is "export data" and "open file destination."
Hi Gina,
Make sure that you have a current table that you can append to. It needs to be already created in your database with the column heading already input.
Hello ! I have a newbie question but is it possible to change the text alignment in access like it is possible in excel ? I really need this for an employee attendance list and honestly I have no idea how to do it in a user friendly way in access ...
Hi Mike,
Yes, let's assume you're working in a table. You have horizontal alignment options in the Home Tab, Text Formatting group. You can use left, middle, or right alignment options. So all you have to do is highlight the column you're interested in, then choose the option you want. Let me know if this works for you.
Hai, I work for a manpower company & we have lots of data. I want to create a website to ease my work, pls advise best useful front-end language & back-end database.
Hi Admin Soundlines Services,
I haven't done too much in Web Design, so I don't have a good answer for you. You'd have to ask a developer about that sort of thing.
My import and link section has no excel! It only has new data source and saved imports.. pls help
Hi Rosette,
What version of Access are you using? Let me know and I can try to help.
Supper explanation and very helpful
I'm glad this helped you out, The Best Technical 360. Thank you for watching!
Hi.. is it possible to edit/replace the existing data inside the database.. by importing thru excel? For example.. if i want to edit the course name for 20 students (which data is already exist in the database), can i reupload the excel again?
Hi Jules,
Unfortunately, you'd need to add the data in as a new table. If you're planning on using the headings/data types over again, you may want to set up a table as a template so you can reuse it.
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!
Thanks for the toturial , i am trying to import an excel file but it has merged cells so what should I do to complete the task successfully and import it to access
Hi Dyala,
Thank you for watching. You will need to unmerge the cells and remove any unnecessary cells from the spreadsheet. I recommend only having column headings and data if possible. I hope this helps.
I'm getting this error message when trying to import an Excel xls. file into DB 2016:
Import Error: The wizard is unable to access information in the file ‘’. Please check that the file exists and is in the correct format.
I'm not sure how to resolve this issue when the file is already in the correct format. The funny thing is, this file was exported from a table that I started in DB. I exported it, added a little data and tried import it but it won't take its own file...I didn't change anything in the file other than adding a couple of records.
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.
I want to export to excel one record. use formulas from excel to manipulate data. Then export back to access
Hi Kenneth,
That would be a lot of work to change individual records. What formulas are you using? If the math is simple enough, you can create a calculated field within Access.
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!
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
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.
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?
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.
I want the excel external source to be able to append the Access database record. i can get it to append the linked but not the access record
portion
Hi tvprofile,
Double check that all of your column headings from Excel are spelled correctly. Also, double check that the 'type' matches with the Access field type. I hope this helps.
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.
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.
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.
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
Hello Professor. I am following step by step and for some reason I am not able to append one Table with another. Is there anyway we can communicate via email or directly ? Thx.
nice explanation... but how many cars do you have???
No problem Sreejon, thanks for watching!
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?
Hi the problem i have is the append is not in the export- excel spreadsheet.... when going in external data.
Hi Alfredo,
The icon will only appear if you create a table in Access that has the same header names. For most cases, you just need the 1st bullet to import the data into a new table.
Thank you Adam. What if my backend has a unique numeric identifier choose automatically by access. How to generate this number
I appreciate the efforts you do to make these concise and apt videos. More to come from you.......Great work
Thanks
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?
Thanks Professor, I am wondering if you have a tutorial on how to create and run queries.
Hi Hermann,
Thank you for watching. Yes I do, here is the video:
ruclips.net/video/YWjQcra9dOc/видео.html
I have really enjoyed the tutorial.send me some videos hw to create dat on-line system for a small school.katanekwa Sambula in Zambia
Thanks for the info. I have a question: what happens when the existing database has an autonumber column? Do I have to make sure the excel sheet has no over-lapping numbers? Basically, I'm trying to copy a bunch of records, change an aspect of them and reimport them to Access, but I'm worried about the autonumber column. The reimported data should not have the same id numbers. Do I have to adjust them in Excel? Anyway, thanks!
Hi Warwick,
The autonumber that is placed on the new Access table is the table's primary key. This means that no number in this column will have a duplicate value. I made a video on primary key's and what they mean and what they do. You shouldn't worry about it too much, it's just to reduce errors in databases. You won't have to change any of your Excel data before the import. Here is a video about primary keys:
ruclips.net/video/7lJS5tklOrE/видео.html
Hi, have you noticed that it the file size of excel is 20 MB, then Access after importing this excel file, shows its size as 60 MB ? I am wondering if that is a problem specific to my case ?
Hi CPatwal,
No, if the file is very large and you import the entire file then it is not abnormal.
Thanks Adam !
No problem, happy to help.
Help! I need to import excel files but the import and link does not show the Excel icon. It only has new data source and saved import .
Thank you
Hi Pamela,
Are you taking an Excel file and putting it into Access? If so, what version of Excel are you using? Make sure that you're not using Excel Online, as I am not sure if it has this feature. Let me know what you're trying to do and I'll try to help.
Professor Adam Morgan, Thank you for your quick response. No I am not using the on line Excel. I downloaded the file from an on line teaching course called Blackboard.
I figured it out:
I created a new database and I had to import Excel files from Blackboard. First I downloaded the files and saved them, then I went to external data, clicked on New Data Source-from file-Excel and browsed the computer.
Thank you again for your quick response 😊
Oh I see, you were downloading it from your course page on Blackboard. Sure, I'm glad you were able to get the files downloaded and find the New Data Source button. Thanks for the comment, let me know if you have any other questions.
Professor Adam Morgan, Thank you for your AWSOME videos.
Hey good to know you enjoy them Pamela. Good luck with your class!
When I import my list from Excel to Access it sorts my list by the key and I don't want that. I need it to stay in the same order as it was in Excel... How do I prevent Access from sorting my list by the key field? Ex. of my key field: 8DA9213
Hi Rijena,
You can choose your own primary key when you're exporting the Excel Worksheet. I would recommend creating a new column (in Excel) and numbering the values in order that you wish them to stay. Then set that field as the primary key. That way when you work in access, you can always sort from smallest to largest to return to the order that you like. I hope this makes sense, let me know if it helps.
When importing from Excel in Access 2016, it works perfectly as you described it. However, if in a weeks time there is a new excel spreadsheet with more data in it than the first and I import it, it creates duplicates of the records already there. Is the a way for a differential import, so only the added records are imported? Thanks
Hi Statsman,
You could remove/filter out the old records from Excel and append the remaining (new) records into the table. It gets a little more complicated when records are being added in two places, but I hope this helps you out. Thanks for watching the video and for the comment. Have a nice day.
Thanks
Is there a way to import excel files where the header of the first column is different from content of that column? Do I need to separate that header to its own column? I know, probably a stupid question. I hate to have to reformat multiple spreadsheets
Hi Marlon,
I would probably separate that data into it's own column. Or you can choose not to import that column in the import steps. Ultimately, it just depends on how you want your table to look. I hope this helps.
Professor Adam Morgan Thanks. I think we’re looking at getting it separated.
Hi Marlon,
Yes, in that case I would separate the data while still in Excel so it looks exactly how you need it to in Excel.
Professor Adam Morgan Thank you!
No problem Marlon!
Thank you for you easy to follow Access 2016 videos. Im new to Access 2016 & I need help importing an Excel 2016 spreadsheet whose columns names have changed but the data is mostly same the already existing already existing Access 2016 database except for 1 new field in the excel spreadsheet. There is an access form with a macro button that imports the excel data into once a 2nd macro is click that confirm the data shown after the import button is clicked is correct.
Is there a way to mport the excel file into the Access Db then create a macro or update query to change the excel field hearings to match the current AccessDB then just add the one new column? Thanks!
Hi Juanita,
Thank you for watching my videos, I appreciate it. You could set up a macro that makes the import steps, but unfortunately, I don't think you can link it to the Access file. I'm not too sure how to solve this problem, and I don't want to direct you to an answer that doesn't work. You could run an update query, and link it to your Excel data I suppose. As long as the file locations do not change, but I haven't tried this myself before.
@@ProfessorAdamMorgan Thank you for the update Professor Adam...Thankfully I was able to do a work around by manually formatting the filenames b4 Iimported them then only copying the data fields not the headers when I import the data.
I do have another Access 2016 question...I want to turn on the checkboxes in the Database Tools/Relationships section so that all my tables that have a primary to foreign key /1 to many relationships have the 2 relationship properties checked: Cascade updates & cascade deletes. But both if these options are greyed out on all of my tables with relationship lines. Do you happen to know what I can do to enable both?
thanks
Juanita
Hi Juanita,
I am glad you were able to figure it out. You need to create a one to many relationship to enable both of the cascade options. I made a video on this a while back, have a look at it at the link below. Hopefully it'll help you solve this problem:
ruclips.net/video/NtRAyS0LLlk/видео.html
This was good for just importing a standard excel file but I need to import with the formats I have in excel file including bold, italicizing and underlining. File was originally formatted in Access, downloaded into excel so others could review and make changes and re-import to access. Lost all formatting. Any suggestions?
Hi James,
Unfortunately, you're going to lose a lot of formatting on most Access databases. Access is not typically formatted in the way that an Excel file can be. The problem is that Access will import the Excel file and consider rows, columns, and text, but it doesn't usually consider formatting beyond that. Once you get the file in Access, you will need to reformat it using Access formatting options. I hope this explains it, have a nice day.
@@ProfessorAdamMorgan BUMMER...thanks for replying.
No problem James, have a nice day.
How to import or export a file with jpg photos
Hi Wroom,
You would want to add in pictures manually. Access is mostly used for text data, so you would need to add in the pictures manually. I hope this helps.
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 😅