Solved

MySQL question

Posted on 2011-09-29
14
327 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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

730 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