Excel to Access Using Python - Migrate Data Using Pandas, SQLalchemy python, and sqlalchemy-access

i'm your host sean mckenzie thank you for joining me once again on my channel on data analytics and data engineering today we're going to talk a little bit about migrating records uh in small systems as opposed to big database systems and one of the examples i want to use is how to migrate data from excel or csv into a microsoft access database using python and one of the reasons i want to do this is because when you install pandas which is a very nice library has all kinds of cool things like data frames and that it has a very nice method that allows you to very easily insert bulk insert many many records into big databases like microsoft sql server or oracle or or whatever but it doesn't have it natively built in to insert into microsoft access and that's what we want to do today and we're going to do it by using a new dialect that we're going to install and then use from sql alchemy which is part of this sort of pandas installation so without further ado let's get to it okay so today we're going to use an xlsx file that i retrieved on august 4th then used for another video today we're not going to analyze this data we're just going to use it for the import process just to just so that we can have a large enough data set to make this worthwhile as you can see there are thousands of records in this worksheet here and if i go all the way to the end it looks like there's about 35 000 and this is going to make a a good data set for our import and i'll just close and save this i must have adjusted a column size or something there so that's our uh our covid spreadsheet that we're going to import and here's a a blank table that we're going to use for our second example we're going to look at how to export that data or migrate that data into a new table and we're also going to look at how to put the data into an existing table so you can see that i have all of this uh data here and actually i think the the column name was 2019 for the population data so i'll change that and uh and then i'll close our access database and get ready to go here so if you remember uh from my previous videos we installed the pandas library and some other stuff and uh pandas is great but today we're going to need to get our sql alchemy access library because the two underscore sql method in pandas does not support access right out of the box you've got to get the dialect from microsoft access and so that's what we've done here and then once that's completed we're also going to need to get the library that allows us to read the excel sheet and we can get that one by getting the xlrd using our pip to install that and that's going to get our our excel get us going with excel then i'll go ahead and start my idle shell and i'll create a new file uh in our default kind of default installation of python which we added our pandas to in previous episodes from there we can go ahead and import our pandas into into our little project here and we'll also we're going to get the url lib library that's going to help us to get our engine going from sql alchemy and so we'll do an import statement for the engine as well and we'll give a little feedback to the user here so we'll just so we can see our progress as the program executes so we'll say print open excel and then we'll set our excel xlsx path to our directory or our path where we've got our kovid 19 xlsx file and then once we're there we can go ahead and make a data frame and we're going to set our data frame so that we can read excel and then we'll pass in our xlsx path and that's gonna read that file out of the directory and then once we've got that into a data frame in in pandas there we can just check it and we'll say that you print the head 10 rows as our program runs just so that we know that we're getting something and then from there we can give a little bit more feedback and say that we're actually going to go ahead and open microsoft access as well and so we'll give a little bit of feedback that we're we're starting that process and then the next thing we need is a connection string so we'll we'll go ahead and i'm going to paste in a connection string from our previous project since we already used this access database now many of the examples of creating an engine use a dsn but i'm a proponent of not using a dsn for my connections my odbc connections i prefer to use the connection string just because it has it sort of really releases your project from having a dependency on other files and resources and things so in order to continue here we need to make our url connection that will be passed into the for the engine and you can sort of follow the same pattern as i'm using here to create your url it'll pretty much be the same every time just make sure that you put your connection string variable in in there and it will create your your your url for you and then we can go ahead and create our our engine so we'll say acc underscore engine equals create engine then we'll pass in our url to that so that we will have something to connect with and then we can give a little bit more feedback we can say uh um writing to ms access and using a print command and uh and that's gonna give some feedback so we know that we've gotten that far in the procedure and and then we can go ahead and tell our data frame we can use the two underscore sql method and that's gonna allow us to write to a table and so we'll we'll write to a table and the first argument is going to be uh covad underscore import because that's what we're gonna name our new table and then we can say our access engine is the second argument there and with no other arguments passed into it that's just gonna create a new a new table called covet underscore import and uh i'll go up here and i'll actually say a new table in here so that we know or what we're doing in our example here and then i can give our final feedback uh which will be uh right complete so that we know our procedure is done and uh and then that that way we'll know we're done and we can go ahead and check our data in the database to see if about what we what we wanted out of there and i'll just do a quick pass over the code here make sure i didn't make any mistakes i'll change those to single quotes since that will be more along the lines of what we've got in the rest of the uh of the procedure and it also looks like i missed uh an equal sign in my connection url there so i'm going to go ahead and go up there and and put that in and now that that's in there i'll take a final look i think we're all good to go here so we can go ahead and click f5 on our keyboard and see what we get in our output and as we can see there's our python script starting we're opening excel and it is reading the uh i think 35 000 lines into excel and uh there we can see that we've got our table information and it has started writing to access and now this took a lot longer than it seems here i actually sped this up for the sake of time but we can see that the write is complete and we can go ahead and open our access database and take a look at what we've got in here so there's our covid import table and you can see that the sql alchemy automatically created an index field that it popped some integers into uh so that you've got kind of a key and uh it's got the all the data from the other um from the excel sheet uh 30 34 104 rows and they've all been put into access in a new table which is exactly what we wanted okay so what if we wanted to put it into an existing table so i've got a table here that i created with an index on it with the same index name field there and let's go ahead and change our example to um to write to an existing table and i'll show you how to do that now so i changed my up my feedback to say existing so we know that that's what's happening as it's running and we're gonna use the same name as the table that's there the covid table and we'll use the argument if underscore exists and we'll say append and that's going to append our records onto that table instead of creating a new table and so what we can do from there is we'll save that and hit f5 and that's going to kick off our procedure and as you can see we've got the same thing happening it's opening excel and it's taking some time to import those records and once those are in we can examine our header again we can see that we've got the same same records in our header and it has started writing to access into an existing table just as we wanted it to and that'll take some time and as i mentioned before i sped this up for expediency but you can see that the right is complete and then we can go and look at our database and see what we got and as you can see here uh the covet table has been populated just as it was for a new table and so there we have it that's our covid uh table that was received the appended records and we can go ahead and close that and we can see that the data is the same as the table that was created in the first example that we had today and that is how you can migrate data from excel to access using pandas and sql alchemy well i hope you enjoyed our discussion today on how to bulk insert rows into microsoft access from microsoft excel and csv and things like that and if you like what you saw today please give the video a thumbs up and please subscribe to the channel if you haven't subscribed yet and make sure to click the bell when you see the bell so that you'll be notified of any new content that i put up on the channel and if you have any questions or comments please make sure to leave those in the comment section below and if you need help with any if you're trying to do this exercise and you run into a problem just uh leave your comment in the section below and uh have a safe day have a great day thanks for watching and i'll catch you next time [Music] [Applause] [Music] [Music] you

Channel: Sean MacKenzie Data Engineering

Views: 1,346

Rating: 5 out of 5

Keywords: excel to access, insert excel data into access tables using python, copy excel data into access using python, migrate from excel to access, migrate data, pandas sqlalchemy access, pandas to_sql access, dataframe to accdb, Migrate Data Using Panda, sqlalchemy python access, dataframe to ms access, sqlalchemy python, sqlalchemy access relationship, sqlalchemy access, data analysis, sean mackenzie

Id: 7H4MVZBWFto

Length: 14min 19sec (859 seconds)

Published: Fri Aug 28 2020