| Author |
 Topic  |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
 Posted - 30 April 2008 : 06:33:00
|
I have a spreadsheet in Excel that I need to import into a table in a MySQL database but no idea how to go about it beyond converting the *.xls to *.csv. I have access to both MySQL Admin and phpMyAdmin. Really need to figure this one out 'cause we are not inputting 1500+ records with 10 fields each manually! 
< |
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 30 April 2008 : 06:43:13
|
Can you access the database from your computer? do you have Excel on said computer?
I ask only because I ran into a similar problem a while back and wrote a little VBScript to read the Excel sheet and add the data to the database... but it only helps if you can connect to the DB from a computer that has Excel on it.< |
 |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 30 April 2008 : 06:48:37
|
quote:
Originally posted by AnonJr
Can you access the database from your computer? do you have Excel on said computer?
I have MySQL Admin on the server but no Excel. On my local machine, though, I have Excel installed as well as the MySQL drivers which I can use to connect to the DB through ASP. If I'm following your thinking, you're suggesting that I should be able to open the *.xls with ASP and treat essentially like another database?
< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 April 2008 : 07:51:10
|
I would simply use SQLYog's Enterprise version and import the data from Excel into MySQL. It's a paid version, but if you use MySQL you will wonder how the heck you've been without it for so long.
If you don't want to buy it and are willing to give me access to the data, I may as well import it for you and give you a MySQL dump from the import.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 30 April 2008 : 10:14:35
|
| |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 30 April 2008 : 13:27:26
|
If you have DTS (with one of your SQL Server subscriptions), you can use that - it's a core competency ;-)
Create a JET connection to the xls, create an ODBC connection to MySQL, map the fields, add any funky transforms you need, do a test run, hit go, voila. Expect to hit problems with nulls, illegal characters, etc if the spreadsheet isn't validated first!
If it's a regular job, save the DTS package, and next time it's a 5-minute job.< |
Edited by - pdrg on 30 April 2008 13:29:49 |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 30 April 2008 : 13:59:04
|
pdrg's idea sounds better too. 
Must be nice to have access to proper tools....  < |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 01 May 2008 : 02:38:00
|
Get your excel data to a .csv file. Upload the csv file to your mysql server. Use the LOAD DATA LOCAL INFILE to load into mysql. I don't know if this works through phpmyadmin though.
If it were me, I'd grab mysql and install it locally, then get the data imported properly on my local workstation. Once imported locally, then you can easily export from your local mysql server to your remote one. I use the mysql administrator (from mysql) to make a db backup locally, then connect to the remote mysql and restore the backup sql I just made.
< |
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 22 May 2008 : 12:18:57
|
Thanks for the help on this one guys. Finally got the spreadsheet through and, after battling for a good half an hour with permissions issues trying to create an Excel object locally using Mark's method, I gave Paddy's solution a whirl and, following 2 quick Google's to check for anything special I need to do to the connection string and how, exactly, to read the spreadsheets contents into a recordset, all ran perfectly first time 
For those interested, here's the basics of the final script:<%
server.scripttimeout=900
const adCmdText=&H0001:const adExecuteNoRecords=&H00000080:const adGetRowsRest=-1:const adOpenForwardOnly=0:const adLockReadOnly=1
dim arrRecords,intRecords,objConn,objRs
set objConn=server.createobject("ADODB.Connection")
objConn.open "provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source="&server.mappath("excel.xls")
set objRs=server.createobject("ADODB.Recordset")
objRs.open "SELECT * FROM FirstField:LastField",objConn,adOpenForwardOnly,adLockReadOnly,adCmdText
intRecords=-1
if not objRs.eof then
arrRecords=objRs.getrows(adGetRowsRest)
intRecords=ubound(arrRecords,2)
end if
objRs.close:set objRs=nothing
objConn.close
set objConn=server.createobject("ADODB.Connection")
objConn.open "driver={MySQL ODBC 3.51 Driver};server=IP;uid=user;pwd=pass;database=db"
if intRecords>-1 then
for x=0 to intRecords
' some string manipulation, combining fields, etc.
objConn.execute("INSERT INTO TABLE (FIELDS) VALUES (VALUES)"),,adCmdText+adExecuteNoRecord
response.write "INSERT INTO TABLE (FIELDS) VALUES (VALUES)<br />"
next
end if
objConn.close:set objConn=nothing
%>< |
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 22 May 2008 : 14:23:39
|
Cool. 
Glad you got everything done. I'm surprised you had the permission issues... I figured if it could be done with the limited permissions they give me at work it would be a cinch anywhere else.  < |
|
|
| |
Topic |
|
Importing from Excel to MySQL
