CODEWITHSUNDEEP
sqlpostgresqlcsvimportcopy
screechOwl
screechOwl

Reputation: 28149

Postgresql - can't load csv file into table

I'm trying to load a few csv files into several postgresql tables but none of them are working and generating a variety of errors.

The files are public and located here:

http://www.kaggle.com/c/march-machine-learning-mania-2015/data

I'm using pgAdmin version 1.20.0 with psql 9.4.0 on a Windows 8 machine.

Here are the things I've tried so far:

I'm using pgAdmin and can create the schema and table.

I then right click on the table, open the import tool, choose the file, set the format to csv, click the header check box, set the delimiter to ',' and click 'Import', and get an error that there is extra data after the last expected column.

Next, I tried using the command line with this:

copy marchmad.regular_season_compact_results FROM 'C:/data/regular_season_compact_results.csv' CSV HEADER delimiter ',' null '\N';

and get this error message:

ERROR: could not open file "C:/data/regular_season_compact_results.csv" for reading: Permission denied
SQL state: 42501

Next, I try putting a '\' in front like this:

\copy marchmad.regular_season_compact_results FROM 'C:/data/regular_season_compact_results.csv' CSV HEADER delimiter ',' null '\N';

and get this error message:

ERROR: syntax error at or near "\"
SQL state: 42601
Character: 1

Any suggestions for how to make this work?

Upvotes: 2

Views: 5368

Answers (3)

amirhossein
amirhossein

Reputation: 63

the file that you want to import needs permission, for that go to security section from proprieties and click add in the white text part write 'Everyone' be sure that the read and execute check box checked!

Upvotes: 0

khampson
khampson

Reputation: 15306

In pgAdmin, you can only use copy, which is a server-side SQL command, so permissions for the file are from a server perspective, even if the server is on localhost.

The error you're getting from that command suggests that the Postgres server doesn't have permission to access the CSV file. It may be that you're running the server as another user, say, postgres, and the CSV can't be accessed by it. If you adjust the permissions such that the user your server is running as can access the CSV file and the directory that it's in, I would expect it to work.

The \copy command doesn't work because that's a special psql command usable in the psql terminal. Since it operates on the client side, it will generally be running under your user. Therefore, it's likely to be the same user as the one that downloaded the CSV, and thus will have access.

You can try using the psql terminal if you want to try it that way. Generally I prefer using psql for dealing with copying files, since that way it's always locally focused in terms of file/permissions, and therefore I can easily copy files to even remote servers without needing to be concerned about special access on the server's file system.

Upvotes: 4

Reenactor Rob
Reenactor Rob

Reputation: 1526

At the end of the file regular_season_compact_results.csv, there is a blank line. That might be the problem giving you the error.

Upvotes: 2

Related Questions