Search code examples
postgresqlpgadminpostgresql-13

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

  • numberr : numeric
  • ticketr : char
  • category : char[]
  • question : char
  • answer : char
  • url : char
  • note : char

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

  • When I click "Count Rows", it says "Table rows counted: 0"
  • I tried the following script in Query Tool of the table, and it shows no rows created
SELECT * FROM (table name)

For references

Screenshot

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

Solution

  • 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