From the course: PostgreSQL Essential Training

Import data from a CSV - PostgreSQL Tutorial

From the course: PostgreSQL Essential Training

Start my 1-month free trial Buy for my team

Import data from a CSV

“

- The entire point of saving data in a database is so that you can organize and retrieve information back out of the database for later analysis or review. The process of retrieving data is called querying the database, and postgres uses the same SQL query language that's standard across all relational database platforms. Learning how to query a database is an important skill in the world of data science, and it's consistently one of the most requested skills in job listings. Now, in order to practice querying our database here in postgres, we need to fill it in with some data. We could write out an insert into command to populate a data table with lots of rows. But a simpler way is to use the PG admin interface to import data saved in a CSV file. CSV is a Comma Separated Values file, and it's a common file format that can be exported and read by any spreadsheet application. I want to add a bunch of rows to the KinetEco products table. Let's take a look at its current status by going into our manufacturing schema, where we'll find the Tables folder, and finally the products table. I'll select it and they click on the View Data button here on the toolbar. That'll run this select statement and show me the data that's below. Now, we created this table in the last chapter. So in order for the import to be successful, you'll need to have created a products table with the same column names and the data types that I've used. Now, there's one thing that I need to do before importing the data, and that's to fix the category ID that we changed previously when demonstrating the on cascade update functionality of the foreign key relationship. There, I changed a category ID from one to 100, and that change cascaded over here to our products table. So I need to change that back so that it matches the data that I'm about to import. Let's select the Categories table, and then click on the View Data button. Then I'll find the Category ID for the batteries, and I'll double-click on it and change it back to one. Press the Save Data Changes button here on the toolbar, that'll push those changes back up to the database, and I can close this tab. That'll take me back to the tab for our products data, where I'll re-execute the query, and I'll verify that that change cascaded over here and changed our lithium ion solar battery back to a category ID 1. Okay, now we're ready to import the data. Right click on the Products table and choose import/export data. Then opens up the dialogue where we can switch over to the Import section. Then we'll go into and browse our file system to find the exercise files folder, which I've placed on my desktop. Inside of the Chapter 4 folder, you'll find a couple of CSVs, and I want the KinetEco-NewProducts.csv. Select it and click Open, and then make sure that the format is CSV, and our encoding is UTF-8. That's the text encoding method that I've used for this particular file. Next, let's take a look at the Options tab. This particular file does have a header row, so it has the column names in the first row of the file, so I'll turn that option on. It also uses a comma as a delimiter between values, so I'll make sure that that says as a comma there. Once you've made those changes, press the OK button to import all of the data into our data table. After a moment, you should get this message at the process completed, so we can go ahead and close these messages here and then re-execute the query in this top menu here to see all of the data. So now that that's done, we have a much larger pool of data that we can explore using queries.

Contents