Solved

How do I import data from Excel Spreadsheet into Mysql?

Posted on 2008-10-13
7
379 Views
Last Modified: 2012-05-05
How do I import data from Excel Spreadsheet into Mysql?
I have a speardshee that I am trying to import into Mysql. How do I do that?

thanks.
0
Comment
Question by:martyje
  • 4
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
mredfelix earned 250 total points
ID: 22703003
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22703088
I often do this by creating a new formula column in excel that creates INSERT statements for each line, then copying that formula down to each row.

A             B                C              D
Steve      Johnson     3323        ='INSERT INTO Employee(Firstname,Lastname,EmployeeNumber) VALUES('"&A2&"','"&B2&"','"&C2&"')'
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22703097
So, then I take the results of the formula in that column, and paste it into Query Analyzer or whatever, and execute it.
0
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.

 

Author Comment

by:martyje
ID: 22703236
I have no clue what u guys are talking about. Sorry. I am using phpMyadmin, should have stated that but anyway, is there any simple way to import excel file inot mysql through phpmyadmin?
0
 

Author Comment

by:martyje
ID: 22703289
mredfelix:
I am getting an error "Can't find file 'test.csv' "
test.csv file is the file that I am trying to import. Myslq is residing on a different sever is that a problem?
0
 

Author Closing Comment

by:martyje
ID: 31505581
Thanks much, I just had to give the 'exact' path of the file, it worked perfectly.
0
 

Author Comment

by:martyje
ID: 22704637
Just an FYI, this is how the exact code that worked...

load data local infile 'c:\\wamp\\www\\test.csv' into table keywords
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(keyword, date_searched)
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

790 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