Excel to MySql load

[nickedw]

Is there something similar to the Access to sql Function for Excel worksheets? I am referring to the MySQL_ExportAccessTableToMySQL function from the MYSQL Functions file found in the Technical database.
Thanks

[Deana]

The basic ADO code remains much the same. You will simply need to modify the SQL strings to use Excel SQL commands.

Reference: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+Tutorials/OLE~TUTORIAL+ADO.txt

[nickedw]

I want to import Excel 2007 worksheet data into MySQL. I thought I'd load the Excel data into an WB as follows:

strQuery = "SELECT * FROM [":wrksht:"$A1:L":sFinalRow:"]"

objRecordSet.Open(strQuery, objConnection, adOpenForwardOnly, adLockReadOnly)
; get recordset
Result = objrecordset.GetRows( )

I can see the data in Result[] , but How do I insert the Data into MySQL?

Thanks for any help.

[Deana]

First it appears that you should export the Excel data to a CSV FILE: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Save~XLS~file~as~a~CSV~file.txt

Then... the code to load the resulting CSV file into MySQL might look something like this:

Code (winbatch) Select Expand

; This code is based of the following code in the tech database:
; http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/ADO~DAO+MySQL~UDFs.txt
;
; REQUIREMENTS to use this code:
; Download and install MyODBC-3.51.11-1-win.msi -- this is so ADO can connect via the ODBC driver.
; You can get this from the MySQL download page.

; Create a System DSN via Control Panel > Data Sources and use the MySQL ODBC driver. Be sure to test it.

; Make sure the account/userid you specify has the correct privileges in MySQL

#defineFunction MySQL_LoadDataFile(DataFileName, TableName, FieldDelimiter, TextQualifier)
; This builds the import SQL for you to import a file, typically a CSV or TAB delimited. I've only
; included a few options, so if you need to work with different options, you'll have to edit
; the code to your needs.
sqlstr = strcat(`LOAD DATA INFILE '`, MySQL_FormatFilePath(DataFileName, "/"),`' INTO TABLE `, TableName)
if FieldDelimiter <> "" then sqlstr = strcat(sqlstr, " FIELDS TERMINATED BY '", FieldDelimiter, "'")
if TextQualifier  <> "" then sqlstr = strcat(sqlstr, " ENCLOSED BY '", TextQualifier, "'")
return(sqlstr)
#endFunction

; First setup the connection string options...
DSN   = "MySQLTest"     ;!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!
SVR   = "localhost"     ;!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!
DBASE = "test"          ;!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!
USER  = "FredFlintstone";!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!
PWD   = "****"          ;!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!

; set the ADO Connection string...
cConn  = strcat("ODBC;DSN=", DSN ,";DRIVER={MySQL ODBC 3.51 Driver};SERVER=", SVR, ";DATABASE=", DBASE)
cConn  = strcat(cConn, ";UID=", USER,";PWD=", PWD,";OPTION=3")
DB     = ObjectCreate("ADODB.Connection")
DB.Open(cConn)

; InsertCSVFile
; this will take a CSV file and input it into a MySQL table. Since import files can have many types of options,
; you need to specify the field delimiter and the text qualifiers (if any) so that MySQL can handle the import.

; actual: `LOAD DATA INFILE 'C:/Data/Access/Employees.txt' INTO TABLE test.employees FIELDS TERMINATED BY ',' ENCLOSED BY '"'`

DataFileName   = "C:\Data\Employees.csv"                 ;!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!
TableName      = "test.employees"                        ;!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!
FieldDelimiter = ","                                     ;!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!
TextQualifier  = `"`  ; data is inside double-quotes...  ;!!!!!!MODIFY TO FIT YOUR NEEDS !!!!!!!!
sqlstr = MySQL_LoadDataFile(DataFileName, TableName, FieldDelimiter, TextQualifier)
DB.Execute(sqlstr)
Exit

[nickedw]

Thanks, but is there any way to bypass the csv step. The data needs to stay secure and i would prefer not saving it to an intermediary csv file. Your help is appreciated.

[stanl]

Thanks, but is there any way to bypass the csv step. The data needs to stay secure and i would prefer not saving it to an intermediary csv file. Your help is appreciated.

You could user either the Jet 4.0/Ace Provider (depending on your version of Excel) to collect the data into a recordset then INSERT INTO the MySQL Table. That assumes the column names match the field names and the data types are similar [or use Aliases and SQL Formatting]. Very difficult to give a code example without knowing more about your data. If Jim is trolling this thread he will have insights as he is experienced with MySQL.

You might also want to search for tutorials like below. Although written for VBA, VBA code is easily transferable to Winbatch. The link below uses a row by row insert:

http://www.heritage-tech.net/908/inserting-data-into-mysql-from-excel-using-vba/

[nickedw]

Thanks, That worked exactly. The capture to recordset, then Insert with SQL may be slower than the CSV to File Load but this gives me the flexibility i was looking for.

[JTaylor]

Happy to help if I can but sounds like you have saved the day once again Stan  8)

Jim