LOAD DATA INFILE only imports 1 row???

[Justummar]

I am using the following
This is on a server with mysql version : 3.23.56 and PHP version: 4.3.10

$sql = "LOAD DATA LOCAL INFILE 'xfile.txt' INTO TABLE `lettings` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES;";
mysql_query($sql) or die('Error loading data file.<br>' . mysql_error());

But it only imports 1 row at best. If I take out the IGNORE 1 LINES option then it obviously inserts the header but for some reason it only ever imports 1 row. I change the LINES TERMINATED BY '\\n' to LINES TERMINATED BY '\\r\\n', Nothing get imported!! which is bizarre.

Any Ideas? is the order of the parameters important? I dont get any errors or anything. But I have been tearing my hair out over this for days. So please anyone.

[Justummar]

I am using the following
This is on a server with mysql version : 3.23.56 and PHP version: 4.3.10

$sql = "LOAD DATA LOCAL INFILE 'xfile.txt' INTO TABLE `lettings` FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES;";
mysql_query($sql) or die('Error loading data file.<br>' . mysql_error());

But it only imports 1 row at best. If I take out the IGNORE 1 LINES option then it obviously inserts the header but for some reason it only ever imports 1 row. I change the LINES TERMINATED BY '\\n' to LINES TERMINATED BY '\\r\\n', Nothing get imported!! which is bizarre.

Any Ideas? is the order of the parameters important? I dont get any errors or anything. But I have been tearing my hair out over this for days. So please anyone.

Oh and this is what xfile.txt looks like

"BranchID", "PropertyID", "PropertyName", "Street", "DisplayStreet", "Postcode", "PricePrefix", "Price", "Bedrooms", "Receptions", "Bathrooms", "ParkingSpaces", "Numeric5", "Numeric6", "Numeric7", "Numeric8", "Numeric9", "AREA", "TYPE", "FURNISHED", "CHILDREN", "SMOKING", "PETS", "GARDEN", "DSS", "PARKING", "cFacility1", "cFacility2", "cFacility3", "cFacility4", "cFacility5", "cFacility6", "cFacility7", "cFacility8", "cFacility9", "cFacility10", "Tenure", "ShortDescription", "MainDescription", "AvailabilityCode", "AvailabilityDate", "FullAddress", "PricePrefixPos"
"BRANCH", "P1001", "Apt 1, Merchants Court", "East Parade", "Y", "BD1", "PCM", "450", "0", "0", "0", "0", "0", "0", "0", "0", "5", "LITTLE GERMANY", "APARTMENT", "YES", "ELECTRIC", "NO", "YES", "NO", "NO", "NONE", "", "", "", "", "", "", "", "", "", "", "", "", "", "MANAGED", "21/04/2008", "Apt 1, Merchants Court East Parade Bradford", "2",

[Atli]

Have you tried '\n' instead of '\\n'?
Looks to me like the one you are currently using would have MySQL look for the '\' and 'n' chars rather than the new-line char.

[Justummar]

Thank you for you reply, most appreciated.
No I tried all different combination of \\n\\r and \n and so on
But it turns out, the reason it was importing only 1 row was that the first field or should i say the primary key field constraint was preventing it from adding more. I removed the primary key of that field as it was a single table with no relationships to any other tables. Its working now with just "\n" also.
Thank you again for your reply.