Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2823
  • Last Modified:

xls to mySQL

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
trevorhartman
Asked:
trevorhartman
3 Solutions
 
ldbkuttyCommented:
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
 
madwaxCommented:
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
 
petoskey-001Commented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
draconius22Commented:
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
 
frugleCommented:
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
 
petoskey-001Commented:
I agree.  In fact that's just what I said, but a bit shorter.  :o)
0
 
trevorhartmanAuthor Commented:
I ended up using the cvs and phpMyAdmin insert data from textfile.  worked great

thanks - Trevor
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now