[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

using : LOAD DATA INFILE in MySQL

Posted on 2009-07-04
3
Medium Priority
?
639 Views
Last Modified: 2012-05-07
Hello,
I want to load data from a csv file to a MySQL table,
I want to perform this operation within a Stored Procedure.
I tried :
LOAD DATA INFILE,
but i have an error saying that i cannot use LOAD DATA from a Stored procedure.

Do I have any alternative method that i can use in a Stored procedure?
thanks
0
Comment
Question by:Nyana22
[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 33

Expert Comment

by:snoyes_jw
ID: 24781236
You can use the LOAD_FILE() function to read the entire file into a variable, and then use a loop to parse each line out of the variable:

SET @file := LOAD_FILE('path/to/file.csv');
while LOCATE('\n', @file) do...

It will be much slower than LOAD DATA INFILE would be.
0
 

Author Comment

by:Nyana22
ID: 24790022
Hello,
well thanks for your suggestion,
but what do you mean by variable?
is it a temporary table?

can you please give more detail about the loop,
thanks
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 2000 total points
ID: 24793509
The variable is not a temporary table; it's just a string that holds the entire file contents.

The loop would have to hunt for line separators in that string, and then within that loop hunt for field separators, and thereby break the string up into the individual fields.

I really think it would be much simpler to drop the requirement for a stored procedure, and just call LOAD DATA INFILE directly from your application.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

650 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