Let us understand how we can load the data into databases using utilities provided.
Most of the databases provide data loading utilities.
One of the most common way of getting data into database tables is by using data loading utilities provided by the underlying datatabase technology.
We can load delimited files into database using these utilities.
Here are the steps we can follow to load the delimited data into the table.
Make sure files are available on the server from which we are trying to load.
Ensure the database and table are created for the data to be loaded.
Run relevant command to load the data into the table.
Make sure to validate by running queries.
Let us see a demo by loading a sample file into the table in Postgres database.
We can use COPY Command using
psql to copy the data into the table.
Make sure database is created along with the user with right permissions. Also the user who want to use
COPY command need to have pg_read_server_files role assigned.
Create the file with sample data. In this case data is added to users.csv under /data/sms_db
user_first_name,user_last_name,user_email_id,user_role,created_dt Gordan,Bradock,email@example.com,A,2020-01-10 Tobe,Lyness,firstname.lastname@example.org,U,2020-02-10 Addie,Mesias,email@example.com,U,2020-03-05 Corene,Kohrsen,firstname.lastname@example.org,U,2020-04-15 Darill,Halsall,email@example.com,U,2020-10-10
Connect to Database.
CREATE TABLE users ( user_id SERIAL PRIMARY KEY, user_first_name VARCHAR(30) NOT NULL, user_last_name VARCHAR(30) NOT NULL, user_email_id VARCHAR(50) NOT NULL, user_email_validated BOOLEAN DEFAULT FALSE, user_password VARCHAR(200), user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A is_active BOOLEAN DEFAULT FALSE, created_dt DATE DEFAULT CURRENT_DATE );
Use copy command to load the data
Validate by running queries
SELECT * FROM users;