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
Solved

CSV into MySQL

Posted on 2014-01-06
6
479 Views
Last Modified: 2014-01-08
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.
0
Comment
Question by:Razzmataz73
  • 3
  • 3
6 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39759675
MySQL has LOAD DATA INFILE statement that could help.
REFMAN: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

MySQL also has the mysqlimport program.
REFMAN: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

Kedar explains here:
http://www.experts-exchange.com/Database/MySQL/A_2250-Load-Delimited-Data-into-MySQL-Server.html

You can handle the scheduling (automation) of executing either import method via cron job, or other tools on the Linux server.  In addition, newer versions of MySQL have scheduled events.
REFMAN: http://dev.mysql.com/doc/refman/5.1/en/create-event.html
0
 

Author Comment

by:Razzmataz73
ID: 39765431
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?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39765498
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).
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:Razzmataz73
ID: 39765555
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
0
 

Author Comment

by:Razzmataz73
ID: 39765626
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.
:)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39765715
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.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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