Solved

Load Data infile SQL for MySQL

Posted on 2016-08-24
3
16 Views
Last Modified: 2016-09-08
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*/
0
Comment
Question by:chonabraham
  • 2
3 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41769315
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
 

Accepted Solution

by:
chonabraham earned 0 total points
ID: 41783048
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
 

Author Closing Comment

by:chonabraham
ID: 41789224
It got me thinking about the right structure.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now