How to load data from Harvest to Postgres destination

Begin by logging into your Harvest account. Navigate to the "Reports" section and select the type of report you wish to export (e.g., time, expenses, invoices). Utilize Harvest’s built-in export functionality to download the data in CSV format. Ensure you download all necessary data files required for your PostgreSQL database.

Open the exported CSV files with a spreadsheet application or a text editor. Review the data for consistency and completeness, ensuring there are no missing fields or corrupted entries. Make necessary corrections or adjustments to align with your database schema.

Before importing the data, define the schema in PostgreSQL. This involves mapping the CSV columns to PostgreSQL table columns. Use SQL commands to create tables that match the structure and data types of your Harvest data. For example:
```sql
CREATE TABLE harvest_data (
id SERIAL PRIMARY KEY,
user_id INTEGER,
project_id INTEGER,
hours NUMERIC,
date DATE,
notes TEXT
);
```

Ensure you have the PostgreSQL client tools installed on your machine. These tools typically include `psql`, which is a command-line interface for interacting with PostgreSQL databases. Installation can be done via package managers like `apt` for Ubuntu or `brew` for macOS.

Use the `COPY` command within the `psql` interface to import the CSV data into your PostgreSQL database. This command reads from the CSV file and inserts the data into the specified table. Execute the following command, replacing placeholders with your actual file path and table details:
```sql
COPY harvest_data(user_id, project_id, hours, date, notes)
FROM '/path/to/your/file.csv'
DELIMITER ','
CSV HEADER;
```

After importing the data, run SQL queries to verify the integrity and accuracy of the data within PostgreSQL. For instance, check row counts, data types, and sample entries to ensure the import was successful:
```sql
SELECT COUNT(*) FROM harvest_data;
```
Confirm that the count matches the number of records in your CSV file.

To streamline future data transfers, consider writing a script (e.g., in Python or Bash) that automates the entire process. The script can handle downloading the latest data from Harvest, preparing the CSV, and running the import commands. This will save time and reduce manual effort for regular updates.

By following these steps, you can efficiently transfer data from Harvest to a PostgreSQL database without the need for third-party connectors or integrations.