Solved

Load Data infile SQL for MySQL

Posted on 2016-08-24
3
21 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
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://…

685 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