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,

Solution

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.

Comments

[ripper234]

Isn't this a duplicate? webmasters.stackexchange.com/questions/14514/…

[TikaL13]

@LazyOne... that is a great resource. I use that everyday.