MySQL question

i have a csv file that I need to add to an existing database table.

The table has 9 columns, the CSV only has 4. the values of the remaining columns can be null once the data is imported.

How do I import this file into the table? I'm not sure how phpmyadmin will  the right columns to insert data into.
lvollmerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Garry GlendownConnect With a Mentor Consulting and Network/Security SpecialistCommented:
Unix path usually is /var/lib/mysql/DBNAME ... check /etc/my.cnf, it has the DB path if it's not at the default ... or do "find / -name mysql" to look for it ... anyway, Mac OS X should have a /tmp ...
0
 
Garry GlendownConsulting and Network/Security SpecialistCommented:
If the data fields not available in the CSV are set to allow NULL values, you should be able to load it without any special options ... e.g. "load data infile '/tmp/data.csv' into table mytable" ... (tested in MySQL 5.1)
0
 
lvollmerAuthor Commented:
can you tell me what  the query would be if the fieldname was specID assuming the dir was '/tmp/data.csv' ?

Thanks
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Garry GlendownConsulting and Network/Security SpecialistCommented:
Query to do what?
0
 
lvollmerAuthor Commented:
import the data from the CSV into the table of the appropriate field? am i misunderstanding?
0
 
Garry GlendownConsulting and Network/Security SpecialistCommented:
Above syntax should already be sufficient if the first four columns are the ones you want to fill. Otherwise, just list the columns you wish to fill, like:

load data infile "/tmp/data.csv" into mytable (col1, col2, col3, col4)
0
 
lvollmerAuthor Commented:
for now i have decided to just update one column called cpn inside a table called cpns

load data infile "/Users/vl/Desktop/Code Sets/HY55.csv" into coupons (coupon)

when I enter that into phpmyadmin query analyzer i get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'coupons (coupon)' at line 1

0
 
lvollmerAuthor Commented:
CORRECTION:

for now i have decided to just update one column called cpn inside a table called cpns

load data infile "/Users/vl/Desktop/Code Sets/HY55.csv" into cpns (cpn)

when I enter that into phpmyadmin query analyzer i get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cpns (cpn)' at line 1
0
 
Garry GlendownConsulting and Network/Security SpecialistCommented:
Missing the "table" between "into" and "coupons" ...
0
 
Garry GlendownConsulting and Network/Security SpecialistCommented:
(sorry, missed typing that myself above, after trying it correctly on my server ;) )
0
 
lvollmerAuthor Commented:
no prob

now i get : #13 - Can't get stat of '/Users/vl/Desktop/Code Sets/HY55.csv' (Errcode: 13)
0
 
Garry GlendownConsulting and Network/Security SpecialistCommented:
MySQL server process probably doesn't have access to that path - move the file to /tmp or the db-directory and it should work
0
 
lvollmerAuthor Commented:
do you know where i could find the db-directory? I'm on a mac and I don't think there is /tmp
0
 
lvollmerAuthor Commented:
bingo found it, and it worked. thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.