Exporting from MySQL, importing in QV as flat text

[#1]

I would like to import some data from a MySQL database into Qlikview.

The main problem here is that I don't have (direct) access to the database, so I need to export from MySQL and import to Qlikview through flat text files.

I first tried XML with the following command: mysqldump --xml test > test.xml

But Qlikview was unable to make anything useful from this, since the fields are labeled through a name attribute instead of the tag itself. ( I know I could manually iterate over all columns with the script, but this obviously is not maintainable nor desired)

So now I want to simply export as CSV using the following MySQL query:

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

ESCAPED BY ‘\\’

LINES TERMINATED BY '\n'

FROM users WHERE 1

(source: ariejan de vroom)

However, what do I need to tell Qlikview regarding the quoting? I can choose from 'no quotes', 'standard', or 'MSQ' (whatever that may be). How does this relate to the Enclosed and / or Escaped character in my MySQL query? Which is the correct option?

[Gysbert_Wassenaar]

The only way to be sure is to simply try both settings (with a small data set).

This is what the help file says about the different quoting styles:

Quotes

Quotes is a file specifier for the Load statement that defines whether quotes can be used and the precedence between quotes and separators.

For text files only. Available options are:

no quotes

msq

If the specifier is omitted, standard quoting is used, i.e. the quotes " " or ' ' can be used, but only if they are the first and last non blank character of a field value.

If Quotation Marks in Scripting are not to be accepted in a text file, the no quotes specifier should be used.

msq is used to specify modern style quoting, allowing multi-line content in fields. Fields containing end-of-line characters must be enclosed within double quotes.

One limitation of the msq specifier is that single double-quote (") characters appearing as first or last character in field content will be interpreted as start or end of multi-line content, which may lead to unpredicted results in the data set loaded. In this case you should use standard quoting instead, omitting the specifier.

[petter]

MSQ - Modern Style Quotion allows optional quotes probably like your MySQL select statement would produce. However the escape character is not something that QlikView would be able to process automatically.

Maybe your tables and the characters in the strings in those tables would not need to be escaped at all - which means that you don't have to worry about ESCAPED BY '\\'.  The double back-slash would probably mean that a single back-slash would be used to indicate that the following character should be read literally and not as a special symbol.

LINES TERMINATED BY '\n'

Means that each record or row is terminated by a newline which should be the normal thing for QlikView when reading CSV. Although a newline/carriage-return/line-feed inside a double quoted string would be part of the string and not signify a new row/record if you use MSQ.

[Peter_Cammaert]

Another solution that avoids those horrible file exchange issues altogether is to replicate your MySQL data in a local MySQL database. And then use an ODBC connection to extract data to QlikView. Works flawlessly.

The export-import step can be automated using some scripting. Data can be archived/compressed and sent via email or ftp and imported into a local db, thereby avoiding non-MySQL translation issues.

Only requires some scripting.

Peter

[#5]

Sure, that might be a possible solution if:

- Installation rights on the machine running Qlikview were a non-issue

- Access rights to the database is a non-issue

- Data would encompass the entire database and not a restricted subset

- Security and data sensitivity is a non-issue (ftp and email are NOT secure)

[Peter_Cammaert]

As far as I know (very limited information is available, and I'ld rather not second guess), you

• don't have to install your local copy of MySQL on the same machine that is running QlikView.
• Data security, data access, data volume and file transfer issues are exactly the same if you export your entire database to csv or xml.

It's just that the format issues go away and reliability may go up because of the exclusive MySQL-to-MySQL data exchange. In my experience, exports to foreign text formats (whether csv, xml, fixed, ...) should be avoided whenever possible in enterprise environments. YMMV.

Best,

Peter