using : LOAD DATA INFILE in MySQL

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
Nyana22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

snoyes_jwCommented:
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
Nyana22Author Commented:
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
snoyes_jwCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.