Re: Load Data Infile and newlines
Mark Nienberg wrote: I'm trying to use LOAD DATA INFILE to read a csv formatted file into a mysql 5.0.22 table. Some of the fields contain text that has newline characters in it. After reading the manual to learn how special characters are treated, I altered the csv file so newlines are represented by '\\n', that is two backslashes followed by a lowercase n. After loading the file, I find that the text fields do indeed contain '\n' as I hoped (one of the backslashes was stripped during import). But for some reason these newlines aren't treated as such by mysql or php. For example, the php function nl2br does not recognize and substitute them. I have other similar tables in the same database that I successfully constructed using the same method, but that was with a previous version of mysql (4.something). Something is different now. The command I used was: mysql load data infile '/share/store/library.csv' into table library fields terminated by ',' enclosed by '' lines terminated by '\r\n'; Any help appreciated. Mark You need to quote the actual linefeed character, not a backslash n. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data Infile and newlines
Gerald L. Clark wrote: Mark Nienberg wrote: I'm trying to use LOAD DATA INFILE to read a csv formatted file into a mysql 5.0.22 table. Some of the fields contain text that has newline characters in it. After reading the manual to learn how special characters are treated, I altered the csv file so newlines are represented by '\\n', that is two backslashes followed by a lowercase n. After loading the file, I find that the text fields do indeed contain '\n' as I hoped (one of the backslashes was stripped during import). But for some reason these newlines aren't treated as such by mysql or php. For example, the php function nl2br does not recognize and substitute them. I have other similar tables in the same database that I successfully constructed using the same method, but that was with a previous version of mysql (4.something). Something is different now. The command I used was: mysql load data infile '/share/store/library.csv' into table library fields terminated by ',' enclosed by '' lines terminated by '\r\n'; You need to quote the actual linefeed character, not a backslash n. OK, I wrote a little perl script to replace \n with a real newline character and now it works, even without adding a backslash in front of it. I don't know why it used to work without this. Maybe the older version of phpmyadmin I was using did some kind of automatic conversion before inserting. Thanks for your help. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load Data Infile and newlines
I'm trying to use LOAD DATA INFILE to read a csv formatted file into a mysql 5.0.22 table. Some of the fields contain text that has newline characters in it. After reading the manual to learn how special characters are treated, I altered the csv file so newlines are represented by '\\n', that is two backslashes followed by a lowercase n. After loading the file, I find that the text fields do indeed contain '\n' as I hoped (one of the backslashes was stripped during import). But for some reason these newlines aren't treated as such by mysql or php. For example, the php function nl2br does not recognize and substitute them. I have other similar tables in the same database that I successfully constructed using the same method, but that was with a previous version of mysql (4.something). Something is different now. The command I used was: mysql load data infile '/share/store/library.csv' into table library fields terminated by ',' enclosed by '' lines terminated by '\r\n'; Any help appreciated. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
