Import Data into My SQL

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

ADO DAO

	ADSI LDAP CDO
	Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Import Data into MySQL

 Keywords:  Import Export Data MySQL ODBC 3.51 Driver DSN CSV ADODB ADO ODBC

Question:

I have an Excel spreedsheet that I would like to import into MySQL. How can this be accomplished?

Answer:

First it appears that you must export the Excel data to a CSV FILE: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.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:

; This code is based of the following code in the tech database:
; http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.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

Article ID:   W18040

Filename:   Import Data into My SQL.txt

File Created: 2014:07:18:09:51:38

Last Updated: 2014:07:18:09:51:38