MySQL LOAD LOCAL DATA HELP

[jbwebdesign]

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)

 

[jbwebdesign]

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

[khanahk]

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

[jbwebdesign]

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.

 

[khanahk]

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

[jbwebdesign]

no problem