Solved

xls to mySQL

Posted on 2004-09-02
7
2,788 Views
Last Modified: 2008-07-07
Hi,

I have two .xls documents that i want to import into a table in my mySQL database.  What would be the quickest way to do this?  I'm using phpMyAdmin 2.5.4

Thanks

Trevor
0
Comment
Question by:trevorhartman
[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 Comments
 
LVL 32

Expert Comment

by:ldbkutty
ID: 11967363
There are many available tools for importing. EMS Mysql Quick Import - http://ems-hitech.com/quickimport is one of the good and easiest way. You have a fully functional trial version of 30 days.
0
 
LVL 7

Assisted Solution

by:madwax
madwax earned 100 total points
ID: 11968619
another way, which I most often use is to write a function in a neighbouring cell that gives me the complete SQL statement with the help of excels function CONCATENATE. That is, something similar to:

=CONCATENATE("INSERT INTO table(column) VALUES('";A1;"');")

this will give you a resulting query which you can later cut and paste into phAdmin...

Good Luck,
//madwax
0
 
LVL 7

Accepted Solution

by:
petoskey-001 earned 200 total points
ID: 11969885
I prefer to transfer the whole spreadsheet page in at once.  

First in excel, exprt your data as text.  Either comma delimited or tab delimited.
Then you should have two files, "sheet1.txt" and "sheet2.txt".

In MySql using phpMyAdmin make sure you have a table that can accept your spreadsheet.  Make sure it has the correct number of fields and the correct data types to hold your spreadsheet data.

Then click on that table in phpMyAdmin, scroll to the bottom and click "Insert data from a textfile into table".  Click browse and load a file, you have to do each file seperately.  Set your options for field delimiters, etc to whatever you used when you exported from Excel and then click submit.

All your data will be imported at once, or error message will tell you that your tables were not structured right to hold the data.  
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Assisted Solution

by:draconius22
draconius22 earned 200 total points
ID: 11971296
My way to do this:
1. Save your .XLS file as .CSV
2. Import this with phpMyAdmin's "Insert data from a textfile into table" option into the prepared table with appropriate structure.

0
 
LVL 10

Expert Comment

by:frugle
ID: 11978094
Nodding draconius's way, I find it more reliable to save the excel data as Tab Delimited Text and import in the same manner.

Discuss?

Mike
0
 
LVL 7

Expert Comment

by:petoskey-001
ID: 11978432
I agree.  In fact that's just what I said, but a bit shorter.  :o)
0
 
LVL 8

Author Comment

by:trevorhartman
ID: 11978910
I ended up using the cvs and phpMyAdmin insert data from textfile.  worked great

thanks - Trevor
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

739 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