CSV into MySQL

Hello,
At this point I am just gathering requirements and brainstorming about how to handle this project. Is it something that should be done in code. Is there an application out there that already does what I want that someone thinks I should use, etc...

So I would like to pick your brains.

We have a manual process were someone goes through 2 CSV files and manually manipulates data. I want to put that data into a MySQL database and automate some of that manipulation for her.

The CSV files are uploaded to our server on a weekly basis by a 3rd party.
We have a Redhat Enterprise Linux 6 server with MySQL version 5.1.71, Webmin 1.660.

What would be the best process to create the MySQL database table and then create an automated process to import the data from the 2 CSV files (that have the same structure) into the database. And then repeat the import once a week (roughly 6am on Mondays).

Does this make sense?

Is this best handled in code?
If so can you point me at some examples for best practices?

Or is there some sort of GUI that I can put on my server to schedule this for me?

Thanks in advance for your expertise.

MySQL ServerMicrosoft ExcelLinux

Last Comment

Kevin Cross

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Kevin Cross

1/6/2014

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Razzmataz73

1/8/2014

ASKER

Thank you Kevin.

If I have questions on this should I post it here?
Or after I make a decision on which way to go would it be better to start a new thread?

Kevin Cross

1/8/2014

What is the question? If it helps you pick the solution, I say post it here as it may help others decide between the options as well. If once you pick a solution, you have other questions a new thread is helpful as it allows us to focus on that issue independently as well as allows other Experts to chime in (as it will gain more attention as a new question).

Razzmataz73

1/8/2014

ASKER

Great.

I have my MySQL Dabases created on my server. And our files are being uploaded on a weekly basis to the same server into a web folder location:
sailing-vacations.mobi/tuimarine

In particular it overwrites the files:
sailing-vacations.mobi/tuimarine/BROKERUSD.CSV
&
sailing-vacations.mobi/tuimarine/BROKERUSDSS.CSV

I am starting to go through your docs now, but if you could help me with getting the automation to work that would be perfect.

The MySQL Database is called
bareboats
and the table is called
rates

And the fields are:

rate_id
rate_create_date
rate_modified_date
rate_basecode Matches up to column A of the CSV
rate_yacht Matches up to column B of the CSV
rate_crewed Matches up to column C of the CSV
rate_date Matches up to column D of the CSV
rate_charter_date Matches up to column E of the CSV
rate_dollars Matches up to column F of the CSV
rate_cents Matches up to column G of the CSV
rate_currency Matches up to column H of the CSV

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Razzmataz73

1/8/2014

ASKER

OK,
I got this to work:

LOAD DATA INFILE '/var/www/sailing-vacations.mobi/tuimarine/BROKERUSDSS.CSV' INTO TABLE rates FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;

LOAD DATA INFILE '/var/www/sailing-vacations.mobi/tuimarine/BROKERUSD.CSV' INTO TABLE rates FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;

Now I just need to figure out how to automate that.
:)

Kevin Cross

1/8/2014

I would do that via scheduled task (cron tab) of server. I am not a Linux person, so a new question may be helpful for that piece.