Cleansing table data for import

[Forbes]

Hi guys!

I've been tasked with importing table data into a new web application. However, the data is chock full of "bad" characters that are generating tons of errors when I try to import.

There's lots of "Ian\\\\\\\\\\\\\\\\\\\\\\'s home page" and: "That's not the idea; certainly not my idea!" which are wreaking havoc.

There's also instances of double apostrophes and even some HTML thrown in for good measure.

As you can imagine, picking through over 13,000 entries by hand really isn't going to happen. I've written a small PHP script to stripslashes(), but it's not getting rid of all of them, just some.

Does anyone have any tips for cleansing table data?

[r937]

i can see the problem with the backslashes, but what's wrong with that other example?

your only hope is to do lots of searches... for example,

WHERE copy LIKE '%\\%'

followed by UPDATE statements

make sure your boss/client approves the expense of data cleansing (it will be rather substantial)

[Forbes]

The ";" character is interpreted as a line termination, which brings the whole import process to a halt.

[r937]

what format is the imported data in? csv?

[Forbes]

No, it's in SQL with insert statements.

[r937]

that's interesting, i would expect that the file would run, i.e. would load the bad data, seeing as how the SQL statements were likely created from a mysqldump

can you give an example of an INSERT statement that fails?

[Forbes]

I don't have the data to hand. For obvious reasons, I had to abandon the last attempt.

I'm using phpMyAdmin for exporting and importing the data. The inserts all seem perfectly fine, other than the data inside them.

[r937]

okay, now we're getting someplace

if the data has been loaded already, then see post #2, you will have to do lots of searches, followed by individual UPDATE statements which fix the data

[Forbes]

So it's going to be a manual process?

[r937]

not completely, no

[Forbes]

OK, thanks for the help!