Re: Trying to load MySQL data

garrettmoore(at)gmail(dot)com wrote:
> Hello,
>
> I am working on a project where we are converting from MySQL to
> Postgres. I figured the easiest way would be to export the MySQL data
> as CSV.
>
> I'm having a problem importing some of the data. What I have done is
> exported the MySQL data and then modified it so that all single quotes
> (a ' quote) are doubled, and null values are replaced with an empty
> value in the CSV.
>
> Our data, for example, looks like this:
>
> 2628,'Poster,5,'255.255.18.138',,'[quote=gz]I''m curious, why not allow
> users, then to freely edit topics?[/quote]
> We could either go with "it''s a minor annoyance that is one measure
> that helps keep DB load lower," or "we do it just to piss you off."
> You choose.',0,1124498148,,,376,0,0,,
>
> This row causes an error. The error is that:
>
> The value "why not allow users" is not valid for column 'x'.
>
> Column x is the first column after the long section of text, with a
> value of '0'.
>
> It appears that the quote in "I'm", which has been doubled quoted to
> '', is not being properly skipped over, and COPY thinks that the next
> comma (after "curious") is a new column, and tries to start inserting
> data there. At least that's what I have come up with.
>
> Why is this happening? I've used this method before and I didn't have
> any trouble, when loading a bunch of Wikipedia test data (which has all
> manner of quotes, commas, and apostrophes in it).
>
> This is line 39150 in the file; all previous lines import fine, but
> this kills the COPY process and all of the previous inserts are rolled
> back. I need to get this data loaded intact.
>
> My copy command is
> COPY posts FROM '/tmp/posts.txt' CSV QUOTE $$'$$;
>

It appears that you're missing a quote after the word 'Poster':

> 2628,'Poster,5,'255.255.18.138',

brian