Microsoft.AspNetCore.Mvc.Localization.LocalizedHtmlStringImporting from Excel to MySQL

Author : Shaggy
Subject : Importing from Excel to MySQL
Posted : 30 April 2008, 06:33
Message :

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!

<

Reply Author: AnonJr
Replied on: 30 April 2008, 06:43
Message:

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.<

Reply Author: Shaggy
Replied on: 30 April 2008, 06:48
Message:

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?
<

Reply Author: ruirib
Replied on: 30 April 2008, 07:51
Message:

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.<

Reply Author: AnonJr
Replied on: 30 April 2008, 10:14
Message:

Reply Author: pdrg
Replied on: 30 April 2008, 13:27
Message:

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.<

Reply Author: AnonJr
Replied on: 30 April 2008, 13:59
Message:

pdrg's idea sounds better too.

Must be nice to have access to proper tools....

<

Reply Author: Doug G
Replied on: 01 May 2008, 02:38
Message:

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. <

Reply Author: Shaggy
Replied on: 22 May 2008, 12:18
Message:

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:

Code:

<%
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
%>

<

Reply Author: AnonJr
Replied on: 22 May 2008, 14:23
Message:

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.

<

Importing from Excel to MySQL

Topic

Replies ...