Solved

Load Data infile SQL for MySQL

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

10 Experts available now in Live!

Get 1:1 Help Now