Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

xls to mySQL

Posted on 2004-09-02
7
Medium Priority
?
2,809 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 400 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 800 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Assisted Solution

by:draconius22
draconius22 earned 800 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…

715 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