Solved

MYSQL Load Data File Local Column issues

Posted on 2010-08-16
8
494 Views
Last Modified: 2012-06-27
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
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

Open in new window

0
Comment
Question by:sharky_24us
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 4

Expert Comment

by:maysara
ID: 33451195
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 ?
0
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 33452321
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...
0
 

Author Comment

by:sharky_24us
ID: 33454030
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.
0
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
LVL 3

Expert Comment

by:glenthorne
ID: 33460105
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.
0
 

Author Comment

by:sharky_24us
ID: 33460606
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?
//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);

Open in new window

0
 

Author Comment

by:sharky_24us
ID: 33460833
it is fixed.

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 = '';
}
}
}

?>

Open in new window

0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 37265857
Question PAQ'd and stored in the solution database.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question