Problem scenario
You want to extract data from a .csv file. In other words you want to transform data from a flat file and import it into a database. Is there an example of how to create a table, format a .csv file, and run a command to import the .csv file into the Postgresql database?
Solution
1. Here is how to create a table to target for the import or ETL (Extract, Transform, Load) process:
CREATE TABLE continualtable (id int CONSTRAINT firstkey PRIMARY KEY, streetAddress varchar(200), city varchar(100), state char(2), zipcode int, phonenumber varchar(20));
2. Here is the content of a .csv file that will be imported into the database table created with the above command:
id streetAddress city state zipcode phonenumber
1,123 Broadway,New York,NY,10001,212-555-5555
2,50 Sunset Blvd,Los Angeles,CA, 90210,310-555-5555
3,400 West Jackson Blvd,Chicago,IL,60611,312-555-5555
3. From a command terminal of the Linux server with Postgresql, run these commands:
sudo -i -u postgres
psql
COPY continualTable FROM '/tmp/file.csv' WITH CSV HEADER;
For the COPY command example above remember that "continualTable" is the table name of the target table for this import process, "/tmp/" is the path to your file and your file is named file.csv.