I am trying to use the INFILE MySQL function to upload a large CSV file (2GB) into a MySQL database.
INFILE is faster than looping through each row, so I want to try get INFILE to work. The bottom script works in Qlikview - so why not in Qlik Sense?
So far...
First hurdle was that the "MySQL Enterprise Edition" Data connection can only read from our MySQL Community Edition. Using drop / truncate / create commands results in the error: (Most command actually executed if you ignore errors)
"QVX_UNEXPECTED_END_OF_DATA: Unable to get column information for the fields that are used in the query: Object reference not set to an instance of an object."
The errors were fixed by using the ODBC connection setup for Qlik Sense:
"Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 1148, ErrorMsg: [MySQL][ODBC 8.0(w) Driver][mysqld-5.5.49-log]The used command is not allowed with this MySQL version"
I also tried using OLE DB with the ODBC connection, but I get an error.
I tried different path references, but without luck.
"D:/QlikData/QSData/Temp/Test.csv"
"D:\\QlikData\\QSData\\Temp\\Test.csv"
referencing the csv file path on the qlikview server
Qlikview Script:
ODBC CONNECT TO MySQL;
SQL
LOAD DATA LOCAL INFILE "//QS_Server/QSData/Temp/Test.csv"
INTO TABLE Test_DB.Test_Table
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
