CODEWITHSUNDEEP
postgresqlpgadminpostgresql-13
Alice
Alice

Reputation: 21

Can't import a CSV file into PostgreSQL

Summary

I failed to import CSV files into a table on PostgreSQL. Even though it says that the import was successfully completed, there's no rows created. How did this happen, and how can I fix this? Thank you.

Details

1. The CSV file I (failed to) imported, is like this 1. CSV file imported

| number | ticket     | category  | question                 | answer                | url            | note     |
|--------|------------|-----------|--------------------------|-----------------------|----------------|----------|
| 1      | #0000000   | Temp>123  | *confirming*             | Would you...?         | https:///....a | -        |
| 2      | #1234567   | AAA / BBB | "a" vs "b"               | If A, "a". If B, "b". | https:///....b | #0000000 |
| 3      | #1234567-2 | AAA>abc   | Can we do sth using "a"? | Yes, blah blah blah.  | https:///....b | -        |

And this is the table on PostgreSQL

2.\ The message after the import Even though it says that the import was "successfully completed" When I hit “More details” of the import pop up (3. Message - Completed) --command " "\\copy public.test (\"number\", ticket, category, question, answer, url, note) FROM '/Users/alice/Desktop/test.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '\"' ESCAPE '''';""

3. The message when I made sure that the file was actually imported

SELECT * FROM (table name)

For references

Screenshot

4. No row created / 1. CSV file imported / 2. Import Preference / 3. Message - Completed / 5. postgres_log

Upvotes: 0

Views: 6406

Answers (3)

Mohammad
Mohammad

Reputation: 1

This is a file permission issue. Open a shell terminal, go to the directory where the data file is stored, and run chmod +rx * and retry loading the data file into your DB.

Upvotes: 0

Alice
Alice

Reputation: 21

After changing the name of a column from "number" to "consecutive", the error message showed up in Query Tool (not in Import/Export)

  1. Tried Query Tool instead of Import/Emport --> the situation didn’t change

  2. Changed the first column name from “number” to “constructive” in both csv and psql table --> the situation didn’t change

  3. Tried Query Tool copy public.test (consecutive, ticket, category, question, answer, url, note) FROM '/Users/alice/Desktop/test5.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '"' ESCAPE '''';"" --> the situation didn’t change

  4. Tried Query Tool copy public.test (consecutive, ticket, category, question, answer, url, note) FROM '/Users/alice/Desktop/test5.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' —>got error message ERROR: could not open file "/Users/alice/Desktop/test5.csv" for reading: Permission denied HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. SQL state: 42501

Upvotes: 1

Alan Millirud
Alan Millirud

Reputation: 1192

check columns settings in tab at 2. Import Preference image

right from options tab

there you should set columns order as in your file

also check more details at 3. Message - Cpmpleted

Upvotes: 0

Related Questions