We help IT Professionals succeed at work.
Get Started

Perl Script - Import Multiple CSV Files MySQL with Update Query In Between

Joel Buhr
Joel Buhr asked
on
588 Views
Last Modified: 2017-07-21
I have never messed with PERL but understand it is probably what I want to be using to import due to the file sizes I am dealing with.

Environment : Linux MariaDB server.

I have 7 data files of roughly 24 million rows each.

Normally I am importing these via SSH mysql command after I move the ".csv" files over to the server running the following command for each file.

LOAD DATA LOCAL INFILE '/home/APR17/20161003_FULL FILE_01.csv' INTO TABLE FD_MASTER FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Open in new window


Then I run this query to code the batch that got loaded.

UPDATE FD_MASTER
SET LoadInputFile = '01'
WHERE LoadInputFile IS NULL

Open in new window


So my question is how do I create a PERL script that will load each of the 7 files one at a time, and in between each load run the Update Query.

It would be nice if it at the end provided a simple report of what it did including how long each segment took to load.

After this there are a series of queries that I also run on that Master file which I would like to also add to this process, but those can wait.
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 9 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE