CodeIgniter Forums
importing a csv into the db - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: importing a csv into the db (/thread-12888.html)

Pages: 1 2 3


importing a csv into the db - El Forum - 11-03-2008

[eluser]alberto24[/eluser]
Hi - I have a variety of csv files that I need to import into my database. Is there a codeigniter function for this? I assume I need to somehow matchup the field names from the csv to match the field names in the database? Also the data needs to also add a specific customer id if possible.

I've done a bit of searching but have come up empty.

Thanks!


importing a csv into the db - El Forum - 11-03-2008

[eluser]Randy Casburn[/eluser]
This is not related to CI, but here is an answer to your question. You can easily integrate this with CI.

http://www.codewalkers.com/c/a/Database-Code/PHP-CSV-Importer-20/


Randy


importing a csv into the db - El Forum - 11-04-2008

[eluser]ray73864[/eluser]
I suggest not using these php based libraries for importing a csv file into a db (especially a mysql db).

I had to do csv import for a client and the csv file could hold anywhere up to 5000 products, no matter how streamlined i made it, i still had to do a 'select' query to see if it existed first and if so just update otherwise insert.

if you are using mysql then might i suggest the native 'load data infile' that mysql has, it allows you to import a csv file directly into a database table and even with 5000 products (like i had) takes a couple of seconds compared to a couple of minutes.

Example code from what i was using:
Code:
if ( !copy("/home/http/capelite/test2.csv", "/tmp/capelite_item.csv") )
            $data['message'] = "Could not copy CSV file to temporary directory ready for importing.";
        
        $query = $this->db->query("LOAD DATA INFILE ? REPLACE INTO TABLE item FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (id, product_id, @dummy, @dummy, item_code, @dummy, @retail_price, @dummy, item_status, @dummy) SET retail_price = substring(@retail_price, 2, 100)",array('/tmp/capelite_item.csv'));
        
        if ($query) {
            $data['message'] = "All items imported successfully.";
        } else {
            $data['message'] = "Import failed.";
        }
        
        unlink ("/tmp/capelite_item.csv");

basically the file may or may not have to be copied to /tmp first (i had to with mine because the mysql server couldn't read from the web directory where the csv file was being stored.

next i tell it that i want to 'replace' if the record already exists.
then, i tell it how the fields are terminated and that the fields could optionally contain double quotes (for strings), and i tell it what the rows are terminated by.

Then you name each DB column with the columns in the csv file using @dummy for any column in the csv file that won't go into your DB table, you can use 'SET' for doing fancy things with the column, such as stripping the $ sign from a currency field and then you have to tell it where the csv file is.

the mysql website has a lot more information but that is the basic gist of it all.

Oh, you also don't have to put every column from the CSV in, if you have a CSV file with 20 columns and a DB table with 5 columns and those 5 columns are the first 10 in your csv file, then ignore the other 10.

I should point out that when i tested it, that csv file had over 20 thousand rows in it, i was forever hitting the php memory limit at about 6000 rows and even using usleep() it was still going to take several hours to import them all, LOAD DATA INFILE did the whole thing in under 30secs.


importing a csv into the db - El Forum - 11-04-2008

[eluser]alberto24[/eluser]
thanks for the replies! The issue is that the csv I have doesn't match field for field so I am not sure this solution ray suggests will work.


importing a csv into the db - El Forum - 11-04-2008

[eluser]johnwbaxter[/eluser]
I'm with ray73864 on this one, load data infile is great if you're not doing any checking of any description on what is going into the db.


importing a csv into the db - El Forum - 11-04-2008

[eluser]ray73864[/eluser]
the fields don't have to match field for field, the 'id' field for instance could be field #1 in the DB but field #10 in the CSV file.

what you are doing when naming them is the 'load data infile' goes through and looks for the column in the csv file that you have named with the same name as the field in the DB.

eg:

csv file: 1,"Ray","Australia","blah",123456789
db table: id, name, phone, country

where "blah" is a dummy field that isn't in your database.

in the 'load data infile' you would do:

Code:
LOAD DATA INFILE my.csv REPLACE INTO TABLE contact FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (id, name, country, @dummy, phone)

This would pull out every row in the csv file and stick the correct information into the correct columns.


importing a csv into the db - El Forum - 11-04-2008

[eluser]alberto24[/eluser]
hmm ok - i will try this - thank you - im pretty new at this stuff...

so if the csv has fields 1,2,3,4,5,6
but the database has fields in this order
2,4,5,1 and 3 and 6 are not in the database

then i would do:
1,2,@dummy,4,5,@dummy

?


importing a csv into the db - El Forum - 11-04-2008

[eluser]ray73864[/eluser]
basically what you want to do is tell the 'load data infile' what each field in the csv file matches up with in the database table.

your example using numbers makes this rather complicated to visualise, but it should work, what you do is try it out and then fine tune it to make sure your information ends up where it should do in the database table.

my previous example above should show how to do it properly.


importing a csv into the db - El Forum - 11-04-2008

[eluser]alberto24[/eluser]
thank you ray


importing a csv into the db - El Forum - 11-04-2008

[eluser]Randy Casburn[/eluser]
Yes - I agree too - as an after thought these guys are correct. load data infile is a much better solution.

Randy