Load Data infile SQL for MySQL

I can not get the following SQL code to work to load data properly from a txt file created in Windows Notepad. I have included the create statements for the database schema and table.

create database shr2;
use shr2;
CREATE TABLE `shr2` (
  `shrcode` char(3) COLLATE latin1_general_cs NOT NULL,
  `shrfirm` varchar(20) COLLATE latin1_general_cs DEFAULT NULL,
  `shrprice` decimal(6,2) DEFAULT NULL,
  `shrqty` decimal(8,0) DEFAULT NULL,
  `shrdiv` decimal(5,2) DEFAULT NULL,
  `shrpe` decimal(2,0) DEFAULT NULL,
  PRIMARY KEY (`shrcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
drop database shr2;

LOAD DATA LOCAL INFILE
'/MySQL/shhr.txt'   INTO TABLE shr2
FIELDS TERMINATED BY ','
ENCLOSED BY '''
LINES TERMINATED BY '\r\n';    /*this is where I keep getting the error*/
chonabrahamAsked:
Who is Participating?
 
chonabrahamAuthor Commented:
Here is how the data are stored in the text file.

FC,'Freedonia Copper',27.5,10529,1.84,16
PT,'Patagonian Tea',55.25,12635,2.5,10
AR,'Abyssinian Ruby',31.82,22010,1.32,13
SLG,'Sri Lankan Gold',50.37,32868,2.68,16
ILZ,'Indian Lead & Zinc',37.75,6390,3,12
BE,'Burmese Elephant',0.07,154713,0.01,3
BS,'Bolivian Sheep',12.75,231678,1.78,11
NG,'Nigerian Geese',35,12323,1.68,10
CS,'Canadian Sugar',52.78,4716,2.5,15
ROF,'Royal Ostrich Farms',33.75,1234923,3,6
0
 
Dave BaldwinFixer of ProblemsCommented:
The text file does have to be formatted as a standard CSV file for that to work.  Can you post a few lines of 'shhr.txt'?
0
 
chonabrahamAuthor Commented:
It got me thinking about the right structure.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.