CODEWITHSUNDEEP
phpmysqlcsv
JMohasin
JMohasin

Reputation: 533

Efficient method for uploading csv file

I am uploading csv file contents into mysql database. I am not sure which approach is best & efficient & nice.

1] Using the LOAD DATA INFILE SQL statement

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);

2] Using a script to parse and import the file for e.g. fgetcsv() function in php

Upvotes: 1

Views: 103

Answers (4)

JKirchartz
JKirchartz

Reputation: 18022

I personally prefer LOAD DATA INFILE because loading script-wise usually uses multiple insert statements. If you need to modify the CSV it'll be much easier from a script, but in my experience LOAD DATA INFILE is faster.

Upvotes: 1

Iznogood
Iznogood

Reputation: 12843

Only reason to not use LOAD DATA INFILE SQL is if you where to process some of the data. For instance if had to convert or join data before insert. But if its a straight import this is by far the fastest.

Upvotes: 1

Ed Heal
Ed Heal

Reputation: 60007

If you can be sure of the content of the cvs, use method 1.

But if you are unsure you may wish the script to do some sanity checking of the contents of the file. In this case use method 2.

Upvotes: 3

Surreal Dreams
Surreal Dreams

Reputation: 26380

Loading directly into the database will likely be more efficient than using a script - you don't have the overhead of running the script, reading/parsing, putting together queries, etc. You just move the data over.

If you need to do this manually 100 times a day, then you're going to want something more automated, like a script. Then you need to consider your personal efficiency and level of effort.

Upvotes: 2

Related Questions