Page 1 of 1

LOAD DATA - linefeeds within fields

Posted: Sun Apr 06, 2003 11:30 pm
by McGruff
I'm stumped with a LOAD DATA problem with linefeeds inside fields.

When I export a tab file from a FileMakerPro database where some fields have new lines (addresses etc - I know but that's the way my client has it set up) the linefeeds seem to get lost after a LOAD DATA.

I've tried every configuration of LOAD DATA I can think of (terminated by, etc) and every output variation from FileMakerPro (Windows, Dos, Mac character sets, csv rather than tab) but always when I look at the mysql db in phpMyadmin after a LOAD DATA it's got funny little squares which can't be nl2br()'d.

If copy paste direct from the FileMakerPro database into phpMyadmin the linefeeds are maintained and nl2br() works fine. That's not a practical way to deal with hundreds of records though.

If I open the tab or csv file in Word the linefeeds are there (you know, the backwards arrow symbol).

If I open the tab file in edit plus there are funny little squares again. I can find replace these with <br /> before LOAD'ing DATA (which works) but I'd rather not have to do that (a client will be maintaining the site and I want to keep the update routine as simple as possible).

ord("the square symbol") returns 11

Any help gratefully appreciated.

Posted: Mon Apr 07, 2003 2:09 pm
by McGruff
bump

Posted: Mon Apr 07, 2003 3:00 pm
by daven
I do LoadData a lot, but never from FileMakerPro.

Do you know what character the square represents, or is it merely the "program has no bloody idea what this character is" symbol?

Posted: Mon Apr 07, 2003 3:57 pm
by McGruff
If I copy the square into a php script and ord(square) it returns 11.

Not sure if that's meaningful - suspect like you say it just ain't got a clue what it is.

FileMaker shouldn't really be the problem, I don't think: when I open the tab file in Word, the linefeeds within a data field seem to be there (the left facing arrow symbol) and paragraph returns to delimit the next record are there too.

The file does LOAD into mysql OK, just with funny little squares instead of linefeeds.

Posted: Tue Apr 08, 2003 11:51 am
by bznutz
Here is a resource that no programmer should be without: http://www.asciitable.com/

The ascii code 11 is a VT or vertical tab. Typically, any ascii code that a program tries to interpret that is not within it's domain of known characters ends up looking like that annoying-ass box character.

I would try executing addslashes() on your data before putting it in the database. This adds the escape char to any chars that MySQL has a problem understanding. Just make sure you run removeslashes() on your data when you pull it out of MySQL.

Also, you can convert all ascii 11 chars to something else if it makes it easier for you. Just do a str_replace on the data before inserting it into the database. Look for chr(11) and replace with another char, like a regular tab (chr(9)) or perhaps a string of spaces.