Solved

MySQL question

Posted on 2011-09-29
14
325 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 17

Expert Comment

by:Garry-G
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 17

Expert Comment

by:Garry-G
ID: 36817548
Query to do what?
0
 

Author Comment

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

Expert Comment

by:Garry-G
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
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.

 
LVL 17

Expert Comment

by:Garry-G
ID: 36817620
Missing the "table" between "into" and "coupons" ...
0
 
LVL 17

Expert Comment

by:Garry-G
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 17

Expert Comment

by:Garry-G
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 17

Accepted Solution

by:
Garry-G 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP Healthcheck 2 84
get domain with php 7 19
Make custom query_posts look show the excerpt only 9 25
Website Interactive tool that will do calculations 3 26
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
This article discusses how to create an extensible mechanism for linked drop downs.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now