importing a csv into the db - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: importing a csv into the db (/showthread.php?tid=12888) |
importing a csv into the db - El Forum - 11-04-2008 [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. importing a csv into the db - El Forum - 11-04-2008 [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 importing a csv into the db - El Forum - 11-04-2008 [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 importing a csv into the db - El Forum - 11-04-2008 [eluser]alberto24[/eluser] ok - here's the code i tried.... Code: <?php I get the following error: Quote:A Database Error Occurred 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 importing a csv into the db - El Forum - 11-04-2008 [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). importing a csv into the db - El Forum - 11-04-2008 [eluser]alberto24[/eluser] ok - when i add the copy code back, here's what i get: Quote:A PHP Error was encountered 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! importing a csv into the db - El Forum - 11-04-2008 [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) importing a csv into the db - El Forum - 11-04-2008 [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' importing a csv into the db - El Forum - 11-04-2008 [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 importing a csv into the db - El Forum - 11-04-2008 [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. |