Import Excel spreadsheets into your database using PostgreSQL

welcome back to another video today  we're going to go over how to import   Excel files into postgres SQL this will be the  best way to just get large amounts of data in   here so you can practice your SQL easier and  more complex syntax to you know return the   data that you want last video we went over some  basic uh create table statements insert into   um and selecting various ways and so we're going  to get more data instead of manually inserting all   that data into our database we're going to import  it so from the first screen here when you open PG   admin you'll be in servers click databases I'm  going to go down to tables we dropped the last   table here so from the last video so we're going  to need to create a new table so let's open up a   query tool just so we can write SQL but before we  get started so the way we're going to import data   into the database is by creating a table and then  you can right click on the table and click import   so we're going to need an Excel  file and if you need a place to find   um data sets I did post a video on how to  use kaggle like where to find data sets   and different ones this is real estate data that  I've used in a lot of my past videos for different   um you know data analysis tips  or just removing duplicates and   there's different ways to you know manipulate  the data in this case this is just a   normal Excel file we're going to need to save it  as a CSV file so in this case we can save a copy   and in this folder we can just click save as  type instead of excel workbook we'll go down to   the first CSP file click save and it changed the  type and now it is a CSV file but in Excel format   so to create a table and use the same create  table statement as before put real estate data you can't put spaces so make sure  it's either one word or use underscores   inside the parentheses what we  can do is actually just copy   these values and then paste them in here so  after each one we'll put a comma and hit enter and you can't use States state so we'll  use the word States if it's purple that   means it is used for different syntax and you  can't use it okay so now these are all of our   column names so now we just need to put the  data types so we know status will be a varying   character we'll make it 15 long because it's all  for sale in that first column the next column   will be integer and the one after that will  also be integer so we can just put here int and then also here and and for  acreage we know that will be numeric   because it has decimals and since it has  decimals we can just make it a numeric value   the next two for City and states will be  varchar a little longer so we'll do varchar   we'll do 40. that should certainly be enough  so varchar 40 zip code we know will be an INT   also size is that a numeric value it  looks like that will also be an INT and   previous sold price so let's just go  down to the first value that shows up so that looks like a date Okay so  we can just put a date value in here and then price we will do ins we put semicolon   hit execute it returned and then if we  go up to the database postgres is the   default one the name you just refresh if we  click down on tables this table will show up   if you click columns you'll be able to see  the different types of columns that are here um and so let's close this file don't want to save that we already saved  it as is and then if we click on the table   right click on the table we can go down  to import export we will be importing   so we're going to want to choose our file which  this will be the comma separated values file   and that's already the default format click ok and  there is a error and the error will be the very   first column so yeah that is not an integer bet so  what we're going to want to do is reopen that file um and so after we reopen this file  we're going to just delete the first   row so after we delete the  first row and then just save it close out of there and let's re-import okay we can just delete this by clicking here   let's right click go to import we'll just  re-click on the same files before click ok in the process has completed so now we can  just refresh once again just to make sure   it went through and then if we right click  on this table go down to view edit all rows   this will select every single row which  is over a hundred thousand rows of data   you can see that it is all in here and so  it's a rose one thousand of a hundred thousand   so you can go through here there are null values  which are just the blanks or null in databases   so you can keep scrolling it will take us down to  2000 out of the hundred thousand and in the next   video we'll get into different ways to use the  select statement to sum values to group them and   order them but I just wanted to show you how to  get your data in here so you can start writing a   little more complex things and really work with  more data instead of manually inserting it all   um and that's all I have for you please like  subscribe and comment and thanks for watching

Channel: Excel Helps

Views: 10,607

Keywords: Learning SQL, best way to learn SQL, PostgreSQL tutorial, how to use PostgreSQL, Learning SQL for data analysis, learn data analytics, Learn to use excel, data analysis, Structured Query Language, Learn to query your data, how to create your own database, make money with data, analyze your data, importing excel data into SQL database, Import excel files, excel data to database, tracking data with a database, SELECT statement, query data, manipulate excel data, find insights

Id: JLX8lCSAD08

Length: 7min 32sec (452 seconds)

Published: Sun Jun 18 2023