Solved

using : LOAD DATA INFILE in MySQL

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

13 Experts available now in Live!

Get 1:1 Help Now