Create Table on postgresql using information from excel sheet

Question

Need to extract values from excel to a table in postgresql. Example- excel table format data Table 1

Time       Flow
12:01 AM   30
12:02 AM   60

... PostgreSQL table 2

Time       Price  Flow
12:01 AM    $1     ?(take from the excel sheet)
12:02 AM    $2     ?

Created a table 2 on postgresql and converted into dataframe. Trying to read values from the excel sheet table 1 to fit data into table 2

PostgreSQL table 2

Time       Price  Flow
12:01 AM    $1     30(taken from the excel sheet table 1 flow column)
12:02 AM    $2     60

Answer 1

Save the Excel spreadsheet as CSV file. Delimitor, quote character etc. must match, Doing that with LibreOffice, standard options just work ..

Then COPY or \copy to a temp table and UPDATE table2 from there:

CREATE TEMP TABLE tmp_x ("Time" time, flow int);

-- in psql!
\copy tmp_x FROM '/path/to/file.csv' WITH (FORMAT csv, HEADER, NULL '\N', DELIMITER E'\t');

UPDATE table2 t
SET    flow = x.flow
FROM   tmp_x x
WHERE  t."Time" = x."Time";

"Time" must be unique, of course.

Related, with more details:

• How to update selected rows with values from a CSV file in Postgres?

About COPY / \copy

• In PostgreSQL, how to insert data with COPY command?