MySQL INFILE CSV

Anonymous — Thu, 23 Dec 2021 14:17:17 GMT

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:

https://withdave.com/2017/04/connecting-to-mysql-community-edition-with-qlik-sense-system-dsn-with-mysql-odbc-driver/

But INLINE still gives an error. This time:

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

"Connector reply error: ErrorSource: Unknown source, ErrorMsg: Unknown description"

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;

Any other ideas ?

topic MySQL INFILE CSV in Connectivity & Data Prep

MySQL INFILE CSV