0

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).

Improve this question
2
  • If you have access to office, open the CSV in Excel, Save in Open Document Format, and import it into phpmyadmin directly, It will even make the table for you Commented Mar 10, 2014 at 10:57
  • The table is in a remote db and has many different fields (I can't substitute their content). Moreover, I red there can be issues with loading so much rows at a time. Commented Mar 10, 2014 at 10:59

4 Answers 4

Reset to default
1

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.

Good luck

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

That's overkill, he can do his task in 30 lines of code
@message: the file is more than 6000 pages in OpenOffice. I though I could split the pages, then use PHP, but I was wondering if there's a less "primitive" way to do it
talend it's more flexible, once you learn it you can do this in 3 minutes with basically all databases and most commonly used file formats
0

Just use search bar, there is a lot of samples at stackoveflow. Main phases:

  1. Parse CSV file
  2. Initiate database connection through PDO
  3. Run for/foreach loop to populate database
Improve this answer

1 Comment

This is exacly what I wanted to do, but I thought there was a better way.
0

You can write a php script in which you can:

  1. Use explode method to get all the entries in an array format
  2. Then run a for/foreach loop over that array to add elements to database
Improve this answer

1 Comment

This is what I wanted to do when I decided to ask if there was any better way than having PHP open a 10 Mb text file, split values by commas and query the db...
0

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;
Improve this answer

2 Comments

The csv file only stores data for ONE column: I must insert this data in an existing table
Once imported to a temp table you can then insert to your existing table using queries on this temp table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.