Automatically convert CSV file to MySQL

Question

I was finding automatic .csv file converter to web based to put the .csv file report as web based. but i can't find one. instead, i found one system which is taken MySQL database and show the report.

Now what i need is a tool which automatically convert a .csv file to MySQL and the other tool will take the MySQL file and show as report.

What opensource web tool you recommend?

Thanks,

Answer 1

There are few ways to import CSV into MySQL DB:

1. Manually using phpMyAdmin -- select DB, table and choose "Import".

2. MySQL can even use CSV as database storage engine (work with CSV file directly) -- but this is not recommended to use it extensively as performance is not that great -- but enough for reading data from it occasionally. The CSV file has to use specific row/column delimiters.

Official documentation: http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html

3. Executing this sort of SQL command (you just need to do a little bit of coding to implement this as a webpage/script):

LOAD DATA LOCAL INFILE "xxx.cvs"
INTO TABLE xxx
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'

Official documentation: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

4. Code your own script that will read data line by line from CSV file and convert them into SQL commands (INSERT INTO ...).

---

You are asking for already existing open source solution -- phpMyAdmin is a perfect example. It is more that just an import script -- it is widely used by manage MySQL databases, but it does what you need -- import data from CSV file into MySQL DB.

Unfortunately I do not know any Open Source reporting web-based tool -- if I need a report I will code it myself.

---

If you search for CSV 2 MySQL a bit, you will see quite a lot PHP-based scripts available. Some of them are free and some cost some money; some will be able to handle any CSV format (row delimiter, column delimiter, non-ascii characters etc) while other may only handle a limited variation.