?
Solved

MySQL question

Posted on 2011-09-29
14
Medium Priority
?
329 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:lvollmer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 18

Expert Comment

by:Garry Glendown
ID: 36817517
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
 

Author Comment

by:lvollmer
ID: 36817527
can you tell me what  the query would be if the fieldname was specID assuming the dir was '/tmp/data.csv' ?

Thanks
0
 
LVL 18

Expert Comment

by:Garry Glendown
ID: 36817548
Query to do what?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lvollmer
ID: 36817557
import the data from the CSV into the table of the appropriate field? am i misunderstanding?
0
 
LVL 18

Expert Comment

by:Garry Glendown
ID: 36817581
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
 

Author Comment

by:lvollmer
ID: 36817612
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
 

Author Comment

by:lvollmer
ID: 36817617
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
 
LVL 18

Expert Comment

by:Garry Glendown
ID: 36817620
Missing the "table" between "into" and "coupons" ...
0
 
LVL 18

Expert Comment

by:Garry Glendown
ID: 36817623
(sorry, missed typing that myself above, after trying it correctly on my server ;) )
0
 

Author Comment

by:lvollmer
ID: 36817651
no prob

now i get : #13 - Can't get stat of '/Users/vl/Desktop/Code Sets/HY55.csv' (Errcode: 13)
0
 
LVL 18

Expert Comment

by:Garry Glendown
ID: 36817664
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
 

Author Comment

by:lvollmer
ID: 36817737
do you know where i could find the db-directory? I'm on a mac and I don't think there is /tmp
0
 
LVL 18

Accepted Solution

by:
Garry Glendown earned 2000 total points
ID: 36817754
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
 

Author Closing Comment

by:lvollmer
ID: 36817831
bingo found it, and it worked. thanks!!
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question