Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL question

Posted on 2011-09-29
14
Medium Priority
?
330 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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