sharky_24us
asked on
MYSQL Load Data File Local Column issues
I am having issues that I can't explain. My load data file code and the text files are being used on another site of mine and work perfectly. On another site of mine they are not correctly mapping to the correct columns of the database on import.
Here is my load data file code:
LOAD DATA LOCAL INFILE './Multi-Family w_4+ Units.txt' INTO TABLE tilistings FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(id, price, unit, complex_name, city, county, street_number, directional, address, zipcode, location, primary_area, secondary_area, bedrooms, baths, half_baths, furnished, sq_ft, amenities, listing_office, listing_agent, year_built, other_encl_sq_ft, property_type, num_rooms, type_style, flood_zone, federal_flood, cobra zone, city_taxes, county_taxes, hoa_dues, hoa_period, construction, roof, floors, foundation, heating_system, cooling_system, water_sewer, parking, appliances, interior_features, exterior_features, included_hoa_dues, owner_expenses, remarks_1, virtual_tour, sq_ft, display, display_address, reo, short_sale);
I have attached a sample of the text file to this message.
There are many columns from the text file being placed wrong.
I can't seem to figure out why this worked on the windows server, but not on this linux server I am now on.
Please someone help me!
Thanks
Chris
Here is my load data file code:
LOAD DATA LOCAL INFILE './Multi-Family w_4+ Units.txt' INTO TABLE tilistings FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(id, price, unit, complex_name, city, county, street_number, directional, address, zipcode, location, primary_area, secondary_area, bedrooms, baths, half_baths, furnished, sq_ft, amenities, listing_office, listing_agent, year_built, other_encl_sq_ft, property_type, num_rooms, type_style, flood_zone, federal_flood, cobra zone, city_taxes, county_taxes, hoa_dues, hoa_period, construction, roof, floors, foundation, heating_system, cooling_system, water_sewer, parking, appliances, interior_features, exterior_features, included_hoa_dues, owner_expenses, remarks_1, virtual_tour, sq_ft, display, display_address, reo, short_sale);
I have attached a sample of the text file to this message.
There are many columns from the text file being placed wrong.
I can't seem to figure out why this worked on the windows server, but not on this linux server I am now on.
Please someone help me!
Thanks
Chris
MLS # List Price Unit # Complex Name City County Street # Direction Address Zip Code Location Primary Area Secondary Area Bedrooms Full Baths 1/2 Baths Furnished Apx Heated SqFt Range Amenities Listing Office Listing Agent Year Built Other Enclosed SqFt Property Type Rooms Type & Style Flood Zone Federal Flood Cobra Zone City Taxes County Taxes OA $ OA Paid Construction Roof Floors Foundation Heating System Cooling System Water/Sewer Parking Appliances Interior Features Exterior Features Included HOA Dues Owner Expenses Remarks Virtual Tour Apx Heated SqFt Display on Internet Display Address REO Short Sale
28390 245000 61 BERMUDA LANDING NORTH TOPSAIL BEACH Onslow 61 BERMUDA LANDING 28460 North Topsail Beach,Onslow County Island Interior Island Interior 2 2 0 False 800-1000 Swimming Pool,Pier/Dock 19 1 1987 112 M 4 Townhouse True True False 304.76 1172 266 QTR Other-See Remarks Composition Shingle Carpet,Vinyl Concrete Slab,Pilings Heat Pump Heat Pump Public Water,Public Sewer Single Garage Range/Oven,Vent/Hood,Dishwasher,Refrigerator,W/D Connection Ceiling Fans,Window Treatments,Great Room,Dining Area,Cable TV Drive-Paved,Deck(s) Lawn Care,Exterior Maintenance,Common Area Maintenance Cable TV/Satellite,Electric,Trash Removal,Sewer,Water,Telephone,Building Insurance APPLIANCES AND CARPETING NEW. VIEW OF OCEAN FROM 2ND. FLOOR. SOUND VIEW FROM LIVING AREA. 832 True True False False
is this line one of those who get columns misplaced ? what version of mysql are running on windows, what version are you running on linux ?
I suspect it caused by FIELDS TERMINATED BY '\t' and you might have \t in your field values!!?? May be you can have your text fields quoted!! and use OPTIONALLY ENCLOSED BY...
ASKER
on the linux host where it is not working, I am running MYSQL 5.1.48 and on the windows host where it is working I am running MYSQL 5.0.22.
The code snippet shows BERMU under Complex Name (this is the text file) yet in the database, that goes into the directional column in the database (the 4th column).
It seems as though it skips the column list I provided in the updatedb.sql file for the Multi Family txt file.
The txt file is tab delimtted and I didn't notice any tabs in the text file values themselves.
The code snippet shows BERMU under Complex Name (this is the text file) yet in the database, that goes into the directional column in the database (the 4th column).
It seems as though it skips the column list I provided in the updatedb.sql file for the Multi Family txt file.
The txt file is tab delimtted and I didn't notice any tabs in the text file values themselves.
One of the things that has bitten me before is that when the file is moved between Unix and Windows, then end of line characters get translated in the FTP transfer. If you are moving the file between the two using FTP make sure you set the transfer type to Binary.
ASKER
yes the transfer is binary via a php script.
The strange thing is that the load data file statements work also through phpmyadmin but don't work correctly when called from the php script
The code for the php script that calls the .sql is attached.
Any other ideas?
The strange thing is that the load data file statements work also through phpmyadmin but don't work correctly when called from the php script
The code for the php script that calls the .sql is attached.
Any other ideas?
//Doing the SQL work now
$url="updatedb.sql";
$nowhost="localhost";
$nowdatabase="xxxxxxx";
$nowuser="xxxxxxxxx";
$nowpass="xxxxxxxxx";
function parse_mysql_dump($url,$nowhost,$nowdatabase,$nowuser,$nowpass){
$link = mysql_connect($nowhost, $nowuser, $nowpass);
if (!$link) {
die('Not connected : ' . mysql_error());
}
// connect to the current db
$db_selected = mysql_select_db($nowdatabase, $link);
if (!$db_selected) {
die ('Can\'t use DB connection : ' . mysql_error());
}
$file_content = file($url);
foreach($file_content as $sql_line){
if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
echo $sql_line . '<br>';
mysql_query($sql_line);
}
}
}
parse_mysql_dump($url,$nowhost,$nowdatabase,$nowuser,$nowpass);
ASKER
it is fixed.
I used this code instead and it read the .sql file more accurately.
I used this code instead and it read the .sql file more accurately.
<?php
// Name of the file
$filename = '*****.sql';
// MySQL host
$mysql_host = '*****';
// MySQL username
$mysql_username = '*****';
// MySQL password
$mysql_password = '*****';
// Database name
$mysql_database = '*****';
//////////////////////////////////////////////////////////////////////////////////////////////
// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());
// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line_num => $line) {
// Only continue if it's not a comment
if (substr($line, 0, 2) != '--' && $line != '') {
// Add this line to the current segment
$templine .= $line;
// If it has a semicolon at the end, it's the end of the query
if (substr(trim($line), -1, 1) == ';') {
// Perform the query
mysql_query($templine) or print('Error performing query \'<b>' . $templine . '</b>\': ' . mysql_error() . '<br /><br />');
// Reset temp variable to empty
$templine = '';
}
}
}
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.