The COPY command and csv files
От | E. Matthew Finnin |
---|---|
Тема | The COPY command and csv files |
Дата | |
Msg-id | c939097c0604121152i7975b59et23b9a0b9406fde53@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: The COPY command and csv files
|
Список | pgsql-novice |
**I apologize in advance if this is a familiar question, but I couldn't find an answer** I am attempting to copy a csv file into a table, but no matter what I can think of I get one of two errors. I am using Postgresql 8.1.2. 1.) Here is the first COPY command I gave: COPY pwt61_oecd FROM stdin WITH DELIMITER AS ',' CSV QUOTE AS ' " ' NULL AS 'na' ; And here is the error: ERROR: missing data for column "isocode" CONTEXT: COPY pwt61_oecd, line 1 '/home/.../data/pwt61_oecd.csv" The file I am attempting to copy is very large, but there are no empty spaces that I can find, and the column "isocode" certainly has no empty spaces or NULL values. Here are the first two lines of the pwt61_oecd.csv file: "country","isocode","year","POP","XRAT","PPP","cgdp","cc","ci","cg","p","pc","pg","pi","openc","cgnp","csave","y","rgdpl","rgdpch","rgdpeqa","rgdpwok","rgdptt","openk","kc","kg","ki","grgdpch" "Australia","AUS","1950","8434.0650803","0.8931999704","0.515977685","1836.3303946","60.365553134","27.690222887","7.9941116957","57.767319935","53.853430186","73.898773273","61.642604011","47.850011457","na","31.64033517","95.021059091","9113.8072046","9173.8190347","10576.40502","22078.709149","9988.2503259","19.331539711","65.924922543","13.104642709","26.200404435","na" (I attempted the same copy command after changing all "na" to "NULL" and I also tried the same command after removing column headers (1st line) from the csv file, but I received the same error message each time.) 2.) Curious if I would get the same error, I attemtped to use the \copy command in psql, but this time I removed the column headers from the csv file: \copy pwt61_oecd FROM '/home/.../data/pwt61_oecd2.csv' WITH DELIMITER AS ',' CSV QUOTE AS ' " ' NULL AS 'na' And the error report: ERROR: invalid input syntax for type numeric: "na" CONTEXT: COPY pwt61_oecd, line 1, column cgnp: "na" Obviously I haven't used the COPY or \copy functions properly. Or I am using the wrong data type. Could someone please point out my mistake(s)? And on a unrelated note, in a seperate table I would like to import there are exponential numbers (e.g. 3.77E-13). Do I need to convert these to regular numbers if I wish to use the numeric data type? Thank you. -Eric
В списке pgsql-novice по дате отправления: