Solved

MySQL question

Posted on 2011-09-29
14
328 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
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?
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

724 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