Solved

xls to mySQL

Posted on 2004-09-02
7
2,798 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

630 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