importing a csv into the db

[eluser]Rick Jolly[/eluser]
Load data infile is the wrong solution if users will be editing the csv. In that case you won't be able to validate and display errors and the import will crash and burn.

[eluser]Randy Casburn[/eluser]
Yea - that's where I was going with the first post. Then I re-read what the OP said. 'Just has a bunch of files sitting on the shelf that need to get pushed into the DB.

If that is really the usecase, that's why I changed my tune. Otherwise, (file structure changes likely), we're back to a script based solution.

Randy

[eluser]obiron2[/eluser]
May I suggest that you don't load them directly into your live system.

Either make a duplicate of the table or test the load on a development server first.

If you are using phpmyadmin on your MySQL server, you can quickly export the existing data in an import SQL statement for re-loading should it all go horribly wrong.

Unless they are your files and you are happy the data is correct, I would strongly suggest using a programatical method for loading the data.

Obiron

[eluser]alberto24[/eluser]
ok - here's the code i tried....

Code:

<?php
include_once( 'base.php' );
class Csvimport extends Base {

function Csvimport ()
    {
        parent::Base();
        $this->load->database();        
        
    }


function index ()
    {  
        $query = $this->db->query("LOAD DATA INFILE '/web/content/csv/test1.csv' REPLACE INTO TABLE csvtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (f_name, l_name, country, @dummy, phone) SET customer_id = '14'");
        
        if ($query) {
            $data['message'] = "All items imported successfully.";
        } else {
            $data['message'] = "Import failed.";
        }

    }

}
?>

I get the following error:

A Database Error Occurred
Error Number: 1045

Access denied for user '369706_cloud1'@'%' (using password: YES)

LOAD DATA INFILE '/www.cloudcontacts.com/web/content/csv/test1.csv' REPLACE INTO TABLE csvtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (f_name, l_name, country, @dummy, phone) SET customer_id = '14'

All my other controllers work fine and can read/write to the db so I am not sure why it says access denied. Any ideas?

thanks

[eluser]ray73864[/eluser]
yeah, that's because mysql can't read from that directory, so using the very first reply i posted (that had code), do the 'copy' thing first to /tmp/ and it will all work fine.

also with regards to the file structure thing, when i did mine using 'load data infile' i was 100% guaranteed that the file would be the same everytime since it was a data export from MSAccess (all columns from the table).

[eluser]alberto24[/eluser]
ok - when i add the copy code back, here's what i get:

A PHP Error was encountered
Severity: Warning

Message: copy(/www.site.com/web/content/csv/test1.csv) [function.copy]: failed to open stream: No such file or directory

Filename: controllers/csvimport.php

Line Number: 15

the path i am using is copied from the path in the ftp client. then the same error i listed above is shown.

sorry to be a pain!

[eluser]ray73864[/eluser]
hrmm, that looks to be missing something at the beginning of the path, also you need to enclose the path in double quotes.

if you have access to SSH you could just do 'pwd' to get the full path details, but i would think you are missing a /home or something at the beginning (not sure though)

[eluser]alberto24[/eluser]
ok spoke with the host - got the full path - now the error about locating the file is gone

but the other error is still there:

A Database Error Occurred
Error Number: 1045

Access denied for user '369706_cloud1'@'%' (using password: YES)

LOAD DATA INFILE "/mnt/target03/351357/www.host.com/web/content/csv/test1.csv" REPLACE INTO TABLE csvtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (f_name, l_name, country, @dummy, phone) SET customer_id = '14'

[eluser]alberto24[/eluser]
got a response from my host - You wouldn't be able to pull the data from the storage target (ftp location). So it wouldn't work in our system to do this.

well there goes that

[eluser]ray73864[/eluser]
as i said before, you need to copy the file to a temporary directory, php can copy to /tmp/ so create an upload script that uploads the file to /tmp/ then use that in the load data infile and when the import is complete have php 'unlink' the file.