0

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

Alice
  • 21
  • 1
  • 6
  • Please replace the links to images with text versions of the information. We also need me more information 1) The CSV file structure as text file, not shown in Excel. 2) The schema of the table you importing into. – Adrian Klaver May 02 '21 at 15:44
  • Welcome to the SO community. Please take a few minuets to take the [Tour](https://stackoverflow.com/tour) and review the help section [ask]. For useful information concerning images see images, [Why not Upload Images](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question/285557#285557) – Belayer May 02 '21 at 19:35
  • Hi @AdrianKlaver, thank you. I added the structure of the CSV file and table. If you need any other info, please let me know. Thank you! – Alice May 03 '21 at 09:29
  • Shouldn't `SELECT * FROM (file name)` be `SELECT * FROM table_name`? Are you sure you are looking at correct table? Are there data rows in `/Users/hsaito/Desktop/test1.csv`? – Adrian Klaver May 03 '21 at 14:44
  • Hi Adiran, Apologies for the mistake. (Both of the table name and the file name are the same this time, so there should not have an impact here.) Yes, I chose the csv file in the file selector, so the file path should be fine. – Alice May 03 '21 at 14:54
  • It is not about the file being there, it is whether there is data in the file? You should also take a look a look at the Postgres log after doing the copy to see what it shows. – Adrian Klaver May 03 '21 at 17:38
  • Hi Adrian. Oh, understood. Yes, the file has contains data, as the screenshot (1. CSV file imported) shows! – Alice May 04 '21 at 13:16
  • Also, created a log table following 18.8.4 of this page, but didn't the table is the same as the table in question - no row was created.... Reinstalled PostgreSQL and pgadmin, but nothing has changed... – Alice May 04 '21 at 13:16

3 Answers3

1

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

Alice
  • 21
  • 1
  • 6
  • This was the actual issue actually. I copied the file in docker's mapped volume using root privileges and it changed the file permissions. When I changed the file folder / file permissions, it worked fine. – Imran Faruqi Oct 19 '21 at 15:39
0

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

Alan Millirud
  • 1,049
  • 7
  • 14
  • Hi Alan, thank you for your comment. I confirmed that the CSV file has columns that the PostgreSQL table has. And they're set in the same order. Thank you! – Alice May 03 '21 at 09:31
0

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.

helvete
  • 2,455
  • 13
  • 33
  • 37
Mohammad
  • 1