Insert comma separated values into MySQL table field
I've a text file that contains many megabytes of comma separated values (about 10 Mb): I must insert these values in a mysql db. Any value must be stored in a different row of a specific field of a table. Any suggestion is appreciated (I can eventually use PHP if needed).
For this tasks I use tools such us Talend. You can do all kind of imports from CSV, files excel, etc, up to 450 connectors. it's open source and there is also a paid version with more features. It's java but you don't need to code in java unless you want to do something the application is not capable of doing. You may need to invest 3/4 hours doing the tutorias but it's worth the hassle.
Just use search bar, there is a lot of samples at stackoveflow. Main phases:
You can write a php script in which you can:
- Use explode method to get all the entries in an array format
- Then run a for/foreach loop over that array to add elements to database
I think you should create a table with same numbers of column CSV file have. Than you can run this query into mysql to load the CSV file data into created table
LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;