Creating table from csv using sql query

Question

I was creating table name "tripsdata" in SQL query tool, which is done successfully. Now I have to insert values into the table and I used the below syntax :

LOAD data INFILE 'tripsdata.csv' 
INTO TABLE tripsdata
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

It keeps giving me error - "ERROR: syntax error at or near "data" LINE 2: LOAD data INFILE 'tripsdata.csv' "

Can you share the error and rectification?

tried what I mentioned above. I want to create this table using SQL query:

Here's the link to the manual, with all the different statements. And LOAD is not one one them: postgresql.org/docs/current/sql-commands.html — Frank Heikens
– Frank Heikens, Commented Sep 22, 2023 at 12:10
COPY doesn't have a SKIP or IGNORE N ROWS option. Is the actual problem how to ignore the header row? In that case you need to use HEADER ON. If you want to match header and column names, HEADER MATCH — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 22, 2023 at 12:13

Panagiotis Kanavos · Accepted Answer · 2023-09-22 13:34:32Z

1

LOAD DATA is a MySQL command. PostgresSQL uses COPY. I suspect what you're trying to do is import a CSV file while skipping the header row. You can do that with the FORMAT CSV and HEADER ON options:

COPY country 
FROM '/path/to/tripsdata.csv' 
(FORMAT CSV,HEADER ON);

answered Sep 22, 2023 at 13:34

Panagiotis Kanavos

134k16 gold badges211 silver badges272 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nnaemeka Daniel John · Accepted Answer · 2023-09-22 20:15:17Z

0

You can as well try this query:

COPY tripsdata FROM 'tripsdata.csv' WITH CSV HEADER;

answered Sep 22, 2023 at 20:15

Nnaemeka Daniel John

4823 silver badges9 bronze badges

Comments

Hanisha Dua · Accepted Answer · 2023-09-29 13:05:23Z

0

Thank you!

I tried this and the error pertains - "ERROR: could not open file "/path/to/tripsdata.csv" for reading: No such file or directory HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy."

answered Sep 29, 2023 at 13:05

Hanisha Dua

12 bronze badges

Collectives™ on Stack Overflow

Creating table from csv using sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related