Just did a little testing with mariaDB for you and all the ones driving into this issue and had success. This is for mariaDB, but mySQL should just work the same way.
Just like the Lucee Admin does it as seen here at github, you can try it the following way:
- Lets assume you have a csv file named addresses.csv like this placed into your webroot:
id, name, email, phone, address
"1","Maria Del Mar","marymar@somehost.com","323-3232","Parque Bonito 1"
"2","Pedro Antonio","pedro@somehost.com","323-3233","Playa Blanca 2"
"3","Jhon Jairo","jhonny@somehost.com","323-3234","Lago del Bosque 3"
"4","Luz Mary","luz@somehost.com","323-3235","Valle Encantado 4"
-
You have a mariaDB database created with:
dbname: mydb
dbusername: root
dbpassword: myDbPassword
-
You have a table named addresses created like this:
CREATE TABLE addresses (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(35) NOT NULL,
email VARCHAR(100),
phone VARCHAR(100),
address VARCHAR(100)
);
-
Go to the Lucee Server Admin and create a datasource manually with the ‘mariaDB’ extension from @Julian_Halliwell (you might need to install the mariadb extension if not already installed) and the following criterias:
dsn-name: mydsn
db: mydb
user: root
password: myDbPassword
-
Create a cfm file named updateDSN.cfm with the following code:
<!--- Read the Datasource "mydsn" already created from the Lucee Server Administrator --->
<cfadmin
action="getDatasource"
type="server"
password="myLuceeServerPassword"
name="mydsn"
returnVariable="existingDSN"
>
<!--- Show the settings --->
<cfdump var="#existingDSN#">
<!--- Set allowLoadLocalInfile if not already exists --->
<cfset myDsn=listToArray( existingDSN[ "dsnTranslated" ], "?" )>
<cfif !mydsn[2].findnocase( "allowLoadLocalInfile=true" )>
<cfset structInsert( existingDSN.custom, "allowLoadLocalInfile", true )>
</cfif>
<!--- Update the Datasource --->
<cfadmin
action="updateDatasource"
type="server"
password="myLuceeServerPassword"
name="mydsn"
newName = "mydsn"
host = "#existingDSN.host#"
className= "#existingDSN.className#"
dsn = "#existingDSN.dsn#"
port ="#existingDSN.port#"
custom = "#existingDSN.custom#"
dbusername = "#existingDSN.username#"
dbpassword = "#existingDSN.password#"
>
<!--- Reread the updated Datasource --->
<cfadmin
action="getDatasource"
type="server"
password="myLuceeServerPassword"
name="mydsn"
returnVariable="newDNS"
>
<cfdump var="#newDNS#">
And run it through the browser.
- Create a cfm file named
importCSV.cfm with the following code:
<cfquery name="test" datasource="mydsn">
LOAD DATA LOCAL INFILE '#expandPath("./").replace("\","//","ALL")#addresses.csv'
INTO TABLE mydb.addresses
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;
</cfquery>
DONE!
And run it through the browser.