Solved

using : LOAD DATA INFILE in MySQL

Posted on 2009-07-04
3
636 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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert row field data graphically 4 34
sort in mysql based off of query param 4 30
Wifi connection to laptop without router windows 10 11 50
MySQL Persistent Connections 10 36
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…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

697 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