Solved

Load Data infile SQL for MySQL

Posted on 2016-08-24
3
22 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql db 3 95
Using Modal's in to Retrieve Data from MySql and Populate Forms 13 129
Survey branching tutorial 11 73
MySql Qry performance  (Ver 5.5.17) 1 13
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

736 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