Excel 2016 - Import to Access - How to Export from Microsoft MS Data to Database - Transfer Tutorial

hi and welcome everyone today we have a video request from je r je r says I'm having trouble importing Excel files from my computer it just says error I don't know if it's my computer or I don't know if I'm doing it right can you make a video on it please alright J here so I'm gonna make that video right now I'm not sure if you're importing the files to your Access database to a current table or to a brand-new table so I'm going to show you guys how to do this both ways alright so right here we see an Access database all I have is one table in it so I want to get my excel file into this Access database so this is the excel file that I created it's pretty standard whenever you take an excel file and put it in to access it doesn't really need any formatting on it right because access is a database program so you could basically just write in whatever data you need with all of the excel data for default formatting and it should be ready to go right from there so I'm gonna take this file here and I'm gonna put it into an access table so I'm gonna close out of this right now you need to make sure that your excel file is currently closed and then what I'm going to do is I'm gonna go into my access area and I'm gonna go to the external data tab so you click on external data and to move Excel into there you go to import and Link right because import means it's coming into the Access database and so then I'm gonna choose this icon right here for excel so I click on Excel and then it's going to open up this dialog box called get external data and I need to specify the source of the definite or of the definition of the objects I click on browse and then I find the area where that is located and I find it right here on my desktop and so you'll find whatever file you need and then you can choose one of these three options I'm going to import the source data into a new table in this case so I go right here and I click OK and then we see the data is all showing up here correctly now keep in mind the first row these are column headings so I need to make sure that this checkbox is too right so first row is column headings I click Next I go right here this is where I could specify the different data types for the headings so if I wanted to change this to say a currency I could do that right here and so I could change all those if I want for the sake of this video I'm not going to change anything and then I'm gonna click Next this is where I could choose my primary key if you don't know what the primary key is I made a video on that just earlier today so check that one out but I'll choose my primary key as the item ID field alright and then I'll click Next and then this is where I can call call it a name I'll just call the inventory and then I'll click finish all right so then it'll ask me do I want to save the import steps I am NOT going to save them in this case and I'll click on close and then we'll see right over here inventory has now appeared alright good so let's say I open this up I see all 14 rows of data that right there is how you import to Microsoft Excel now or Microsoft Access rather now I mentioned earlier that I err I'm not sure if you're trying to import to current table so I created this one e inventory table here and so I created this keeping in mind that all of the information should be the exact same on all of the columns right so this is my original source in Excel here and I see it says item ID item category campus so on and so forth all of these things need to be perfectly spelled if you're importing to a current table so you can see all the column names need to be correctly spelled as well as the records below should have the same type of formatting so what I'm going to do here is I'm going to import all of these records here onto these records here so we should have 3 more than we had before so I closed out of Excel and now I'm gonna go again to the external data tab I'm gonna go to import and Link and I'm gonna click Excel just like last time I'm gonna find the file in the same spot as last time I'll double click it and I'll go right here to upend this time and this time I'm gonna move it to the 1e inventory table the one I just showed you it's right here and it's open typically you do want to close out at the table so I'm gonna just close that but anyway I'm gonna move it straight to that table so again I go to append 1e inventory I click OK it recognizes everything I click next and this time I'll call it the same thing obviously and I'll click finish and close and now if I open this up you should see 17 records here right the three we originally had as well as the 14 that we imported in so the biggest problem or the biggest issue that students have when they try to do this is that the students don't spell the names of the columns correctly on Excel and access that's the main error that you'll get so joy here make sure that you're doing that correctly for everyone else that's watching make sure that you again type the field names and the column names perfectly the same no extra spaces nothing like that otherwise the programs won't be able to communicate those errors so if you enjoyed this video give it a thumbs up please subscribe to the channel if you haven't already and hopefully this helps you out I have a lot of videos on access Excel Word and PowerPoint as well as how to get certified in those so if you ever request yourself put a comment in the comment section and I'll be happy to make a video for you as well thank you so much and I hope to see you again soon

Channel: Professor Adam Morgan

Views: 378,204

Rating: 4.919075 out of 5

Keywords: professor adam morgan, import excel to access, excel to access, how to import excel to access, how to import data from excel to access, access, microsoft access, access excel, access database, excel access, microsoft access database, access 2016, import data from excel to access, excel to access data transfer, excel database, access import data from excel, access tutorial, how to use access, ms access, how to use access database, import spreadsheet wizard access, ms excel, ms

Id: RelLvfilOnQ

Length: 5min 54sec (354 seconds)

Published: Sat Apr 22 2017