Hi -

I have two questions that have arisen as a result of using the copy command to
create csv files.

1. Apparently, the table from which I am trying to create the csv file has
linefeeds/carriage returns in it that don't belong there. UGH! Is there a way to
restrict the users from inserting linefeeds into the field? They are connecting
via odbc from some windows app (probably access) so I assume it's a windows
linefeed, and although I can't see them, I know they are there because the
output has them, and I get the following warning:

WARNING: CSV fields with embedded linefeed or carriage return characters might
not be able to be reimported


2. I'm not sure I understand the copy sytax. If I want text to be in single
quotes instead of double quotes, the way I read the doc, the following should
work:

copy table1 to 'output.txt' with csv quote as ''';

which doesn't seem to work. Am I misreading the doc?

Thanks in advance for any help!

Mija

Search Discussions

  • Oliver Elphick at Sep 19, 2007 at 6:50 am

    On Tue, 2007-09-18 at 16:49 -0700, Mija Lee wrote:
    Hi -

    I have two questions that have arisen as a result of using the copy command to
    create csv files.

    1. Apparently, the table from which I am trying to create the csv file has
    linefeeds/carriage returns in it that don't belong there. UGH! Is there a way to
    restrict the users from inserting linefeeds into the field? They are connecting
    via odbc from some windows app (probably access) so I assume it's a windows
    linefeed, and although I can't see them, I know they are there because the
    output has them, and I get the following warning:

    WARNING: CSV fields with embedded linefeed or carriage return characters might
    not be able to be reimported
    You can either reject bad data or clean it up.

    To reject it, put a constraint on the column:

    alter mytable add check (mycolumne !~ E'[\r\n]');

    To clean it up, create a trigger to be fired on insert or update:

    CREATE FUNCTION clean_data()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    BEGIN
    NEW.mycolumn = translate(NEW.mycolumn, E'\r\n', ' ');
    RETURN NEW;
    END;
    $$;

    CREATE TRIGGER clean_data BEFORE INSERT OR UPDATE
    ON mytable FOR EACH ROW
    EXECUTE PROCEDURE clean_data();

    2. I'm not sure I understand the copy sytax. If I want text to be in single
    quotes instead of double quotes, the way I read the doc, the following should
    work:

    copy table1 to 'output.txt' with csv quote as ''';

    which doesn't seem to work. Am I misreading the doc?
    Single quotes in a quoted string need to be doubled: ''''
    Alternatively, you can use the escape string format: E'\'' or E'\047'.
    (47 is the octal value of the single-quote character.)

    In either case, what you get may not be what you want: every
    single-quote character in the data will be doubled:

    copy mytable to stdin with csv quote as E'\047';
    198,Registered
    200,'Fred''s Bar'
    201,'Tom, Dick and Harry'


    --
    Oliver Elphick olly@lfix.co.uk
    Isle of Wight http://www.lfix.co.uk/oliver
    GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
    ========================================
    Do you want to know God? http://www.lfix.co.uk/knowing_god.html


    --
    This message has been scanned for viruses and
    dangerous content by MailScanner, and is
    believed to be clean.
  • Mija Lee at Sep 19, 2007 at 6:20 pm
    Oliver:

    thanks so much for the help. Just for folks who might be looking on the list,
    Oliver's suggestion works with 8.1 and above:

    alter table mytable add check (mycolumn !~ E'[\n\r]');

    whereas this works on 8.0:

    alter table mytable add check (mycolumn !~ '\\r\\n');

    Maybe everyone already knows this...

    Mija
  • Oliver Elphick at Sep 19, 2007 at 9:12 pm

    On Wed, 2007-09-19 at 11:19 -0700, Mija Lee wrote:
    Oliver:

    thanks so much for the help. Just for folks who might be looking on the list,
    Oliver's suggestion works with 8.1 and above:

    alter table mytable add check (mycolumn !~ E'[\n\r]');

    whereas this works on 8.0:

    alter table mytable add check (mycolumn !~ '\\r\\n');
    But don't forget the square brackets: '[\\r\\n]'
    otherwise you would match only a return followed by a linefeed rather
    than either by itself.
    Maybe everyone already knows this...

    Mija
    --
    Oliver Elphick olly@lfix.co.uk
    Isle of Wight http://www.lfix.co.uk/oliver
    GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
    ========================================
    Do you want to know God? http://www.lfix.co.uk/knowing_god.html


    --
    This message has been scanned for viruses and
    dangerous content by MailScanner, and is
    believed to be clean.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedSep 18, '07 at 11:49p
activeSep 19, '07 at 9:12p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Mija Lee: 2 posts Oliver Elphick: 2 posts

People

Translate

site design / logo © 2022 Grokbase