1. Have a docker container running on your server (example EC2).
2. Locate your CSV file you want to import.
3. Check to make sure your schema model for the table you are importing your data match.

For example, if your table CSV rows were:

Time,Date,Elapsed,Orig,Dest,Detour enroute,Driver,Distance

And your schema is:

model Trip {
  id       Int    @id @default(autoincrement())
  time     String
  date     String
  elapsed  String
  orig     String
  dest     String
  detour   String
  driver   String
  distance String
}

1. Once you have migrated your PostgreSQL schema with npx prisma migrate and got a success message, log into your PostgreSQL interactive terminal. One way is to do this:

   • Run docker ps -a to see your databases. Look for the one with status "Up hours."
   • Modify the /upper-division-cs/web-24wi/scripts/docker_connect.sh script and replace database1 with your database running when you run docker ps.
   • Run chmod a+x docker_connect.sh.
   • Run it with source docker_connect.sh or ./docker_connect.sh.
   • You should see postgres=#.
   • Run \l to see your list of databases.

2. Connect to your database with your final project schema. For example, if your database is called mydb, type \c mydb.

3. Next type \dt to list your tables. After that, you can run SELECT * FROM "Trip", replacing Trip with what you called your table. Your table may be empty.

4. Once your table is ready to take data, you need to copy it to your Docker container:

   • Open another terminal and change into the directory with your CSV file you are importing.
   • Next, run the Docker copy command:
     • Step 1: Get the container id from docker ps and copy it to your clipboard.
     • Step 2: Prepare the command: docker cp /tmp/data.csv <container_id>:/data.csv, replacing /tmp/data.csv with your filepath for your data and <container_id> with your container id. It will look like 31ba4cf01d5d.
     • Step 3: Run the copy command from step 2.
     • Step 4: If it succeeds, check your PostgreSQL prompt with \! cat data.csv. You should get a printout of your CSV data in your terminal. This means that data was copied successfully. Now you need to import it into your table.

Back on the mydb=# line, run a command that is tailored for your data. Sure, here's how you can format it for a wiki:

COPY Trip(time, date, elapsed, orig, dest, detour, driver, distance) FROM 'file_path' DELIMITER ',' CSV HEADER;

This SQL command imports data from a CSV file into the Trip table. Make sure to replace 'file_path' with the actual file path to your CSV data. The DELIMITER ',' option specifies that the CSV file uses commas as delimiters, and CSV HEADER indicates that the first row of the CSV contains column headers. Adjust the column names in the COPY command to match your schema, as shown above. For example, if your command works, then you will get a message from SQL saying a number of records.

That's it. You can take your table name and run SELECT * FROM "table" to see all your data. Remember to replace table with your actual table name.

Repeat this process for each dataset you have and each table you have. Done!