jbwebdesign Posted August 24, 2012 Report Share Posted August 24, 2012 hello, i am trying to load a large CSV file in the database and it's not working can someone please tell me what i'm doing wrong?? here is my query for the SQL LOAD DATA LOCAL INFILE '/tmp/2012-8-StandardAreaCodeDatabase.csv' INTO TABLE targets FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (State,NPANXX,LATA,ZipCode,ZipCodeCount,ZipCodeFreq,NPA,NXX); but i get the following error: #2 - File 'tmp/2012-8-StandardAreaCodeDatabase.csv' not found (Errcode: 2) Quote Link to comment Share on other sites More sharing options...
jbwebdesign Posted August 24, 2012 Author Report Share Posted August 24, 2012 (edited) For anyone who is trying to do something similar to what i have to do which is to LOAD a large database onto phpmyadmin..... here is what i did to solve my issue..... 1.) Login to cPanel and white list your ip address. ( go to the mysql remote access and enter your ip address) 2.) Download a program called MySQL Work Bench 3.) Connect to MySQL Workbench using the ip address that shows on on the left side of your cPanel (in my case it's a shared ip address because i am on a shared hosting account) 4.) After you have connected, create a table with the same fields that appear in your Large database file. (in my case it is a .csv file) 5.) use a similar query like the one below (i am on my mac, the path should be your own computer's path): in the example below, i am using the table name "targets" and the fields in my table are State,NPANXX,LATA,ZipCode,ZipCodeCount,ZipCodeFreq,NPA,NXX LOAD DATA LOCAL INFILE '/Users/mycomputername/Downloads/2012-8-StandardAreaCodeDatabase-csv/2012-8-StandardAreaCodeDatabase.csv' INTO TABLE targets FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (State,NPANXX,LATA,ZipCode,ZipCodeCount,ZipCodeFreq,NPA,NXX); I hope i was able to help anyone else who had the same problem as me Edited August 24, 2012 by jbwebdesign Quote Link to comment Share on other sites More sharing options...
khanahk Posted August 25, 2012 Report Share Posted August 25, 2012 If you use phpMyAdmin there is an 'Import' button where you can import a CSV, XLS, etc. that phpMyAdmin will convert to an SQL table for you Quote Link to comment Share on other sites More sharing options...
jbwebdesign Posted August 25, 2012 Author Report Share Posted August 25, 2012 yes there is khanahk but sometimes when you have a Large database to import, that will not work because of php's limitations. so the best solution for that is to run a LOAD DATA query on the SQL. Quote Link to comment Share on other sites More sharing options...
khanahk Posted August 25, 2012 Report Share Posted August 25, 2012 I see. I have run into the 'filesize too large' error when importing some CSV's and will keep this in mind if and when I come to it again. Thanks for your post Quote Link to comment Share on other sites More sharing options...
jbwebdesign Posted August 26, 2012 Author Report Share Posted August 26, 2012 no problem Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.