Solved

using : LOAD DATA INFILE in MySQL

Posted on 2009-07-04
3
635 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
  • 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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

809 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