Transfer csv files into a mysql database

[Kei]

Hey everybody,

My company decided to improve the management of some data contained in csv files by loading their content in a mysql database.

I heard that this is part of the process called ETL. Is that right?
If that's so, which tool can I use in order to do what I describe above.

thanks.

[gtd]

Hi kei,

As a matter of fact you have two ways to do it depending on your technical background. You can either write a script yourself or use an ETL tool that will write the most part of the script for you.

Are you more of a technical user or more of a business user ?

[Kei]

I would say that I am more on the "business side" than on the technical one.

[peter_budo]

MySQL documentation - LOAD DATA INFILE Syntax. Search page for references of csv for faster location

[gtd]

Indeed, if it is a one-shot project, the fastest way to do it is indeed with :

LOAD DATA INFILE 'path/file.csv' INTO TABLE your_table;

However, if you plan on doing it on a regular basis (if you receive, these very csv files regularly) you may want to use, indeed, an ETL tool.

[Kei]

Thanks gtd and peter.

As you said gtd, I would like to do it 3 or 4 times a week.

I will do with "load data infile" now and I will wait for your other suggestion concerning the etl tool.

Why would it be better to use an etl tool if I plan to do it "on a regular basis" ?

[gtd]

It would not be better but simpler, because you can automate the task with an etl tool, thus allowing you to run the task on a user-defined basis.

[peter_budo]

This may help you with choosing ETL tool

[Kei]

Thanks for the link peter, it is very interesting.

but the full study need to be purchased...

Anyway, this survey seems a bit old (more than 2 years ago) and since I can't afford to dedicate a budget to the operations mentioned above, I have to choose an open source etl tool which pretty much narrows down the choice.

I will think about it and give my feedback afterwards.

[Kei]

I have been using Talend Open Studio for a few days now, and it IS very user-friendly.

I also manage to export an access database to a postgre database very simply.

Thanks for this tool, very very useful and easy to use.

[marck_don]

Ok, as you think. I understand that having an heavy code base is a major blocker to change it.
But I ,personally, as a DBA and professional SQL developer think you go the wrong way, trying to handle your date processing in php to send it to mysql.
You have an highly optimized and low level engine that is all ready to handle them for you, and I think it makes perfect sense to use it.
It can ease your computations a lot by using built-in functions.
PHP simply isn't adapted to that task when you care about performances.

And yes, I did 11 years (since PHP-FI/PHP 2.0) of PHP development as senior developer at the same time I achieved my DBA formation.
I don't say that because I don't know PHP.
I know it suffisantly to know when I should stop using it.

I simply found sad that today developers don't try to use the right tool for the right task, and try to do everything in the same place, regardless of performances and resources conservatism.

That being said, it will just let you do the php date() calls to send to the db the integer you need.
I don't see what your question is about....