1,105,312 Community Members

LOAD DATA INFILE query not working

Member Avatar
ebutt13
Newbie Poster
20 posts since Dec 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,

I'm trying to insert the contents of this this file (assets.txt) into my database:

B02W0Y2^EUROSTAR LTD^35.09^^
B02V64S^NORTHERN ROCK^21.97^^
B06N3Q2^MOTOR OIL SAPA^7.24^^
B05WF50^COLT TELECOM GROUP^20.29^^
B03R2SA^XEROX CAPITAL^35.65^^
B09G3SD^COREALCREDIT^12.10^^
B04F5GD^ULSTER BK LTD^16.46^^
B02DH5H^EUROTUNNEL FINANCE^53.24^^
B05D3WK^BRADFORD & BINGLEY^18.07^^
B05DGEF^SKIPTON LTD^4.50^^
B07DHX4^EBAY INC^46.55^^
B08VEW1^FURSTENBG CAP^86.36^^
B06HS34^LBG CAPITAL^54.22^^

I'm using this code:

mysql_connect($mysql_hostname,$mysql_user,$mysql_password);
$sql = "LOAD DATA INFILE 'E:\EasyPHP-12.1\www\logintest\ASSETS.txt' INTO TABLE stock_names
        FIELDS TERMINATED BY '^'  LINES TERMINATED BY '^^\r\n'";

mysql_query($sql);      

However, nothing is being inserted and no error messages are appearing. Does anybody know what's going wrong?
Perhaps I'm doing it all wrong, in which case can somebody suggest an appropriate alternative?

Many Thanks,

Ed

Member Avatar
cereal
Posting Virtuoso
1,710 posts since Aug 2007
Reputation Points: 461 [?]
Q&As Helped to Solve: 345 [?]
Skill Endorsements: 44 [?]
 
0
 

can you post the output of explain stock_names?

Member Avatar
ebutt13
Newbie Poster
20 posts since Dec 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

@cereal

I'm not quite sure what you mean by "explain stock_names".
There is not output. Nothing inserts into the database.

Member Avatar
drjohn
Posting Pro
526 posts since Mar 2010
Reputation Points: 50 [?]
Q&As Helped to Solve: 106 [?]
Skill Endorsements: 4 [?]
 
0
 

Try using a tab delimited file instead.
I update 64,000 rows every week using a tab delimited file and it works every time.
Inventing your on type of csv file is never worth the effort when there are tried and tested methods.

Member Avatar
cereal
Posting Virtuoso
1,710 posts since Aug 2007
Reputation Points: 461 [?]
Q&As Helped to Solve: 345 [?]
Skill Endorsements: 44 [?]
 
0
 

In addition from manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Windows path names are specified using forward slashes rather than backslashes. If you do use backslashes, you must double them.

The explain command instead, it is used to display the table structure: http://dev.mysql.com/doc/refman/5.0/en/explain.html

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article