Import XSLX/CSV File To PostgreSQL database

hi this is Ankush in today's video we are going to see how to load Excel data to the PostgreSQL database so basically we have the requirement where Excel contain a number of Records so in this example I am using the Excel which is having the 50,000 record and I am in a situation where I need to put this data to the who's this equal database table so we are using the command-line interface by using the putti chain sorry viruses in the PJ admin and we are will be looking after how we can do this thing in a practical way okay so let us see so if I go to this location I can see there is one excel file is available so I am just opening this file so that I can show you what are all the takeoff records are available inside this excel file so you can see here there are number of columns which are available which is active under school QMS - SP and there are around more than 43,000 of course I can see over here so here it's average so there are many records are available if you just go below you can see there are 59 thousands records are available and we are going to insert these records to the Bosley sequel database table so in order to do this thing there are few things that we need to take care the first thing is we need to convert this excel file file to the CSV file and after doing this we can use a copy command by using this copy command we can load the data from the excel file to the Posca sequel database and then we can just cross check it by using the Select command okay so before doing the copy there is one more thing which we need to take care that we need to create a table with the same data structure so I will I will be demonstrating you what is mean by data structure so if you just check to this excel file you can see over here there are number of columns are available and when we are creating the new table in that case that column name and the number of columns should be a should be the same so this is what the main requirement is so here you can see there are number of columns are available the first one is summary underscore date legacy is there and many columns are there so when we are creating the table in the post a sequel it should contain the same column or same number of columns so that it can detect that yeah this data should be available this data should be go to this particular column only so how to do that let me show you in a practical way before that I will just open the PG admin which is mainly useful to connect to the Postgres equal database server so now it has connected to the Fuji sequel database server it is not asking me the password because I have already saved the password so let me go to the database the database name is possibly sequel so here we have a schema which is having the name public and here only we are going to create a script okay so as I said earlier first of all we need to create a table so I am going to create a table and this is the excel file before that we need to just convert this excel file to that CSV file how to do that go to the file save as browse and save as a type so we are using the type as a CSV so just go here this is a comma delimited and I am keeping the name sampled it CSV okay and I am saving it to the desktop just click on save so already one CSV file is available available with the same name so I am just replacing it okay so if I'm just going and checking the name was sample so this is the CSV file I am trying to open this file with the notepad plus plus you can see you over here this is the CSV file and we have converted this XM XML file to this CSV file so the first step is done now the second step is we need to create a table I have already stepped handy with me which is creating a table let me show you before that I am just deleting the table if the same table is available so this is a table create table table name and summary underscore date which is having the date column and from where this column is coming so I will just show you if you just go to this XLS file you can see over here this is the first column which is I am putting here legacy end is two company this is the second column third so the same number of column which is available in the XML file I am putting it to the sequel file so basically we are creating a table over here and putting the same column which are available inside the XML file okay so let me drop the table because I am already having this department table inside the public schema so I will just fire this query now we have dropped the table if I'm just trying to find out by using the command select star form it is showing that the table does not exist because just now we have a drop that Dept table now we are going to create a new table with the name Department under the public schema so I will just fire this query here now it has created a table let us try to select the data see here here now we can see there is no data available inside the department table because still we have not added or loaded the data from the CSV file or XLS file now this is the command which will be helpful to load the data from the excel m5 now the one thing which I have already told that we need to convert the XML file XLS file to the CSV file so we have already converted that the file to the CSV file and this is the path that we need to say currently the csv file is available on the desktop so if you just go here right-click properties you can get the path from here itself so till desktop it is the path and at last you need to provide the name of the file okay so let's copy it and fire it on the sequel prompt okay now here we got the error message saying permission denied so what we need to do just go here right-click properties and then go to the Security tab click on the edit and at last you need to click on the Add button type it as everyone so that everyone will have the access to read and write this file make sure you are providing all the access just by selecting the check box over here and click on apply okay okay now if you try let's see whether we are getting any error message so I am selecting this copy command again and just executing now it has started executing the command and hopefully it will load all the data which are available inside the CSV file to the department table in order to cross-check this we can use the Select command once again let us see whether we are getting the same amount of data so here you can get you are getting the message saying copy 59,000 record let us see by putting the command as select star from the table name see yeah so whatever the data which are available inside the excel file or CSV file that is getting loaded to the table Department so this is how we can load the data from the excel file to the Postgres sequel database so let me try to repeat the consequence again whenever you want to load the data from the excel file to the post a sequel database first of all you need to convert that excel file to the CSV file as a comma separated limiter then you need to create a table which is having the same data structure as like XML file access file except sorry excel file and then at last you need to use the command copy and in order to cross-check you can use the select star from table name thank you so much see you again with the next video please do like subscribe bye bye

Channel: ANKUSH THAVALI

Views: 48,382

Rating: 4.7846789 out of 5

Keywords: CREATE TABLE and import CSV Using PostgreSQL, How to import CSV file data into PostgreSQL table on Windows, How To Use Excel PostgreSQL Import, Export & Convert Software, How to import database postgreSQL, Export PostgreSQL table to CSV file using pgAdmin, PostgreSQL - dump postgresql with command line on windows, import excel to Postgresql Database, Postgresql, import csv

Id: KTcEg35Xd38

Length: 11min 29sec (689 seconds)

Published: Thu Aug 02 2018