Thread: Looking for postgres equivalent of mysqlimport
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
At 9:13 AM -0400 9/17/04, Sean Davis wrote: >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. 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
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: > At 9:13 AM -0400 9/17/04, Sean Davis wrote: >> 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. > > 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
At 9:37 AM -0400 9/17/04, Sean Davis wrote: >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, 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
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: > At 9:37 AM -0400 9/17/04, Sean Davis wrote: >> 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, > > 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
At 12:31 PM -0400 9/17/04, Sean Davis wrote: >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" Your text columns will probably have imported the text "NULL." From http://www.postgresql.org/docs/7.4/interactive/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
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: > At 12:31 PM -0400 9/17/04, Sean Davis wrote: >> 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" > > Your text columns will probably have imported the text "NULL." > > From http://www.postgresql.org/docs/7.4/interactive/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