Solved

xls to mySQL

Posted on 2004-09-02
7
2,781 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
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
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.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

839 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