I am looking for the rough equivalent of:

mysqlimport -L -h <host> -r --fields-terminated-by=',' -u<user> -p<pass>
--fields-enclosed-by='"' genekeydb *csv >report

In particular, I have '"' enclosed fields.

Thanks,
Sean


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

At 9:13 AM -0400 9/17/04, Sean Davis wrote:
As far as I know there's nothing to determine enclosed fields with
postgresql and you'll have to trim these '"' off after the import.

For the import, what you're looking for is COPY TO/FROM ...

Here's the documentation on COPY:
<http://www.postgresql.org/docs/7.4/interactive/sql-copy.html>

If this is a one time operation that you don't need to program into
your solution you can easily do it with Eduphant from any Win or Mac
client: <http://aliacta.com/download>
In Eduphant, click the "stdin" button to select the file you want to
import, then type the appropriate COPY command and execute it.

General caveat on imports: make sure you don't mix up text encodings!

Cheers,

Marc

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Marc,

Thanks. That is what I had thought. Another detail--I have fields
(including some dates) that include "" (empty fields). What is the
best way to deal with those? Should I just convert them to NULL
(assuming that was the actual meaning) and then use that as the NULL
string in the copy command?

Sean

On Sep 17, 2004, at 9:28 AM, M. Bastin wrote:


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

At 9:37 AM -0400 9/17/04, Sean Davis wrote:
Sean,

I think you could try that. You may leave them empty as well (after
stripping '"') and use that for NULL with the COPY command.

Marc

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Marc,

That seemed to get most of the way (setting to NULL). However, it
seems that does not work for integer columns? I'm still getting an
error:

ERROR: invalid input syntax for integer: "NULL"
CONTEXT: COPY cgap_lib,line 1, column clones: "NULL"

Here is the schema:
CREATE TABLE "cgap_lib" (
"cgap_lib_id" character varying(16) NOT NULL,
"organism" character varying(64) NOT NULL,
"libid" character varying(16) NOT NULL,
"unilib_id" character varying(16) NOT NULL,
"descr" character varying(3500),
"keywords" character varying(256),
"clones" smallint,
"strain" character varying(128),
"tissue" character varying(256),
"unique_tissue" character varying(128),
"edit_date" date NOT NULL,
"get_date" date NOT NULL
);

Here is the first line:
cg2_000000027681 Mus musculus 241 78 Mouse liver library liver, normal,
bulk, EST NULL NULL NULL liver 2004-8-17 2004-8-23

Any more insights?

Thanks again,
Sean


On Sep 17, 2004, at 12:15 PM, M. Bastin wrote:


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

At 12:31 PM -0400 9/17/04, Sean Davis wrote:
Your text columns will probably have imported the text "NULL."

From http://www.postgresql.org/docs/7.4/i.../sql-copy.html

"null string

The string that represents a null value. The default is \N
(backslash-N). You might prefer an empty string, for example."

You can choose your own null string with:
[ NULL [ AS ] 'null string' ] ]

Marc

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Marc,

I hadn't noticed that for that table I left out the with NULL AS
'NULL'. That did fix the problem. I appreciate the help. I now have
a completed database from mysql!

Sean



On Sep 17, 2004, at 12:37 PM, M. Bastin wrote:


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)