Thread: command line v GUI and COPY

Greetings,
I have looked through the documentation provided on postgresql.org for
help with a few items, since I don't know much about DBs or PostgreSQL.
It seems that most of the documentation on the site is focused toward on
the command line. Is there another useful resource that focuses on GUI help?

At the moment I have several CSVs from the US Forest Service's FIA
progam and would like to set up a new DB with the CSVs as tables. I set
up a non-admin user and when I try to import a CSV to a table, I get a
couple of error messages. The first is that there is extra data after
the last column. I read somewhere that error arose because I was
importing to an existing table, even though the table was something I
just made in GUI by right-clicking and using the menu and the table as
far as I knew was empty. I deleted the table and am trying to find
another way to create a table by clicking on something and copying
directly/importing from the CSV.

I tried psql. I wrote "create table ok_boundary;
copy ok_boundary from 'c:\[...]ok_boundary.csv' delimiter ',' csv header;"
(all with caps as shown in documentation or on stackoverflow.com) and it
wouldn't let me create the table to begin with. Trying to pick up the
command line stuff is slower than the GUI for me, so it would be great
if I could find something to get through the initial steps that is less
focused on the command line. In the bigger picture, I am trying to get
PostgreSQL and PostGIS to work with QGIS for a couple of GIS projects
and so in the process, I am trying to get my head wrapped around those
two former programs and their connections.

John Polo

On 10/14/2014 12:11 PM, jared wrote:

for GUI, do you have pgadmin installed?

Yes. I created the DB "FIA" with it and in the tree under "Tables", right-clicking has an option for New Table, but I don't know which of the tabs, if any, let me import existing data at the time of creating the new table in the pgadmin menu.

For the 'extra data after the last column'

The error should have given you a row number.  Did you open up the file and that row number and make sure there wasn't?

This is how the data originally appears in the file. The error says it was in row 2. It looked like there was an extra pair of quotes and then an odd quote at the very end.
"CN","PLT_CN","INVYR","STATECD","UNITCD","COUNTYCD","PLOT","SUBP","SUBPTYP","BNDCHG","CONTRAST","AZMLEFT","AZMCORN","DISTCORN","AZMRIGHT","CYCLE","SUBCYCLE","CREATED_BY","CREATED_DATE","CREATED_IN_INSTANCE","MODIFIED_BY","MODIFIED_DATE","MODIFIED_IN_INSTANCE"
"202492243010854","157287634010478",2008,40,1,5,131,2,1,,2,276,0,,70,7,1,"",2011-05-02,"10854","",,""

I used Notepad++ to open the file and get rid of the quotes and just leave the trailing commas for the three empty fields. Still the same error message. I tried again, deleting the commas trailing after "10854" to see if that would get rid of the problem. Just in case, I opened in LibreOffice Calc, I can't see any other contents in the cells. I couldn't find anything in the fields, tried to delete them anyway. Searched the web and came across the comment about adding to an existing table. I still don't know what's causing the problem.

John

Jared,
The error came up when I was trying to use pgadmin (GUI) and mouse and menus that opened up with right-clicks. I opened psql and typed it all as you did and it worked. Thank you. I have _many_ more CSVs and it would easier to do this through a GUI or some other way that won't involve as much typing. And that's just the FIA data I downloaded. I have a bunch of GIS data that I want to start building another DB with. But this is a start.

John

On Tue, Oct 14, 2014 at 2:40 PM, John Polo <polo@okstate.edu> wrote:

 

This is how the data originally appears in the file. The error says it was in row 2. It looked like there was an extra pair of quotes and then an odd quote at the very end.
"CN","PLT_CN","INVYR","STATECD","UNITCD","COUNTYCD","PLOT","SUBP","SUBPTYP","BNDCHG","CONTRAST","AZMLEFT","AZMCORN","DISTCORN","AZMRIGHT","CYCLE","SUBCYCLE","CREATED_BY","CREATED_DATE","CREATED_IN_INSTANCE","MODIFIED_BY","MODIFIED_DATE","MODIFIED_IN_INSTANCE"
"202492243010854","157287634010478",2008,40,1,5,131,2,1,,2,276,0,,70,7,1,"",2011-05-02,"10854","",,""


John

Hey John,

Maybe I am misunderstanding, but that copied into a table fine, did you paste the correct row into the email?

I placed the text you provided than executed:

create table test

(

CN  character varying,

PLT_CN  character varying,

INVYR  character varying,

STATECD  character varying,

UNITCD  character varying,

COUNTYCD  character varying,

PLOT  character varying,

SUBP  character varying,

SUBPTYP  character varying,

BNDCHG  character varying,

CONTRAST  character varying,

AZMLEFT  character varying,

AZMCORN  character varying,

DISTCORN  character varying,

AZMRIGHT  character varying,

CYCLE  character varying,

SUBCYCLE  character varying,

CREATED_BY  character varying,

CREATED_DATE  date,

CREATED_IN_INSTANCE  character varying,

MODIFIED_BY  character varying,

MODIFIED_DATE  character varying,

MODIFIED_IN_INSTANCE  character varying

);

copy test

from '/for_import/test_data.txt'

with delimiter as ','

csv header

null as '';

And everything came through without errors.