Mysql to excel linux

Exporting results of a Mysql query to excel?

you can simply use hold control key and click on the first row then hold shift with the control key and keep scrolling to the last row and click on it. now you are selecting all the rows you can right click then copy row then go to excel and paste. you can also right click on the header and copy column names

@shadysherif — Maybe, but probably best just as a comment. It kinda assumes that someone is using a GUI, for example — something that isn’t specified in the question or tags. But up to you!

9 Answers 9

The typical way to achieve this is to export to CSV and then load the CSV into Excel.

TL;DR:

SELECT . FROM someTable WHERE etc INTO OUTFILE 'someTableExport.csv' CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY '\r\n'; 

mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables table1 table2 table3 

mysqlsh --user="mysqlUserName" --host="serverHostName" --port=3306 --schema="databaseName" # Once connected, run this: util.exportTable("tableName", "file:///C:/Users/You/Desktop/test.csv", < dialect: "csv", fieldsEscapedBy: "">) 

First, a caution about Excel:

Excel’s vs. MySQL’s default CSV formats:

Remember that Excel has its own _{underdocumented} ideas about how CSV files should be formatted and these stand in-contrast to MySQL’s own ideas about CSV files; though Excel is largely compliant with RFC 4180 you still need to prod and poke MySQL and its associated tooling to generate CSV files that Excel won’t misinterpret:

• Excel 2007-2013: — Requires leading UTF-8 BOM
• Excel 2016+: — Handles BOM-less UTF-8 with some prodding

As per the table above, MySQL can generate Excel-friendly CSV files, excepting that SQL NULL s will always be interpreted by Excel as literal text, though it’s trivial to use PowerQuery or even just Find-and-Replace in Excel to replace them with empty cells.

Excel and special CSV text markers

Excel and UTF-8 encoding:

Surprisingly, it wasn’t until Excel was 31 years old (Excel 2016) when Excel added built-in support for UTF-8 encoding in files without needing a BOM, but it still defaults to importing and exporting CSV files using your system-default non-Unicode encoding (e.g. Windows-1252 ).

When importing CSV into Excel, be sure to select Codepage 65001 for correct UTF-8 handling as Excel still defaults to non-Unicode-based codepages for some reason.
Note that opening a CSV file in Excel won’t display the Text Import Wizard. (As of Excel 2021) you need to copy-and-paste CSV text into Excel and use the popup menu to use the legacy (frozen-in-1994) wizard, or use Data > From Text/CSV on the ribbon to use the newer (but less flexible, imo) PowerQuery-based CSV import wizard:

Your options:

Option	SELECT INTO OUTFILE	mysqldump —tab	mysqldump > file.csv	mysqlsh	MySQL Workbench
Server-side CSV	True	True	True	True	Broken
Remote (client-side) CSV	False	False	False	True	Broken
MySQL Server version support	All versions	All versions	All versions	Only 5.7 and later	All versions

Option 1: Exporting an Excel-friendly CSV using INTO OUTFILE :

• You can do a server-side CSV export by using the INTO OUTFILE clause of a SELECT query.
  • Because this is «normal» SQL that’s executed by the MySQL server this works regardless of whatever MySQL client tool you’re using, so you don’t need to install MySQL Workbench.
  • . but because this is a server-side export you need to have permission to write to the server’s filesystem which you might not have, in which case consider using specialty export tools like mysqldump (see below).
  • FIELDS.
    • TERMINATED BY (default: ‘\t’ , for Excel use ‘,’ )
    • [OPTIONALLY] ENCLOSED BY (default: » , should be ‘»‘ with the OPTIONALLY keyword)
    • ESCAPED BY (default: ‘\\’ , for Excel use » )
    • TERMINATED BY (default: ‘\n’ , for Excel use ‘\r\n’ )
    • STARTING BY (default: » , for Excel you can omit this or use the MySQL default).

    So your query ( SELECT * FROM document WHERE documentid. ) would look something like this:

    SELECT * FROM document WHERE documentid IN ( SELECT documentid FROM TaskResult WHERE taskResult = 2429 ) INTO OUTFILE 'someTableExport.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '' LINES TERMINATED BY '\r\n'; 

    Option 2: Exporting an Excel-friendly CSV using mysqldump :

    • To store dump into CSV file using the —tab command-line option, as per mysqldump ‘s documentation.
    • Unfortunately mysqldump ‘s —tab= option won’t work for remote MySQL servers: this is because —tab=»fileName.csv» can only represent a path on the server.
      • While you can use stdout redirection to generate a local file (i.e. mysqldump —etc > output.csv ) you cannot use the —fields-terminated-by and other format options with stdout , making it useless for Excel-compatible output. So if you’re remote and cannot ssh -in then you will need to use MySQL Shell ( mysqlsh ) instead (see below).

      In the OP’s case, due to limitations inherent in how the —where= command-line option works, they’ll want to export both tables ( document and TaskResult ) and apply their filter logic in Excel PowerQuery or similar. Perform the export like so:

      mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables document TaskResult 

      • The above command-line should work without modification in Windows’ cmd.exe , macOS’s zsh , and bash on Linux — provided mysqldump is in your PATH .
      • The use of hex-encoded chars means sidestepping the hassle of figurig out how to pass double-quotes and line-breaks as literals in your shell and terminal ( 0x22 is » , 0x2C is , , and 0x0D0A is \r\n ).
      • Avoid using the —password= (aka -p ) option on the mysqldump command-line, as it will mean your password will be saved in plaintext to your terminal or console history file, which is an obvious massive security risk.
        • So if you’re in an interactive command-line session need to specify a password then mysqldump will prompt you for the password immediately when the program runs so it won’t be saved to your history file.
        • If you want to run mysqldump in a non-interactive context (e.g. from within a web-application, daemon, or other process) then there’s (normally) no history file to worry about, but you should still consider alternative approaches before resorting to handling passwords in an insecure way.

        Option 3: Exporting an Excel-friendly CSV using MySQL Workbench:

        Unfortunately, you can’t (unless you don’t have any double-quotes anywhere in your data): As of late 2022 MySQL Workbench has an open bug regarding its own CSV export feature: output files never escape double-quote characters in text, so pretty much all CSV-handling software out there will report a malformed CSV file or import data to the wrong columns — so this makes it completely unsuitable for use with Excel.

        Option 4: Exporting an Excel-friendly CSV using MySQL Shell (aka mysqlsh ):

        • This is probably the simplest option, but you might need to install the MySQL Shell as it doesn’t come in-box in most MySQL installations.
        • MySQL Shell supports connecting to MySQL Server 5.7 and later (but not older versions). If you’re still using MySQL Server 5.6 or earlier (then you really should update to 5.7 or later anyway) you’ll have to stick with mysqldump run locally on the MySQL Server itself (but you can use an ssh session, of course).
          • The new «MySQL X» protocol ( mysqlx://user@host/schema ) is not supported by MySQL 5.7, but mysqlsh supports non-X connections with old-style command-line parameters.
          1. Install MySQL Shell if it isn’t already installed.
          2. If you start MySQL Shell without any command-line arguments (e.g. because you use the Start Menu shortcut on Windows)) then use the \connect command to connect.
             • For MySQL 5.7 use \connect mysql://username@hostname
             • For MySQL 8.0+ there are a variety of ways to connect, including the «MySQL X» protocol as well as «Classic» connections. Consult the docs for more info.
             • If your username contains literal @ characters then you need to percent-encode them (e.g. if you’re using Azure MySQL then your full username will be like username%40servername@servername.mysql.database.azure.com ).
             • Immediately after you submit the \connect command you will be prompted for your password interactively.
          3. If you can start mysqlsh with arguments, then you can run mysqlsh —user=»userName» —host=»hostName» —port=3306 —schema=»dbName» directly without using the \connect command.
          4. Once connected, run the util.exportTable(tableName, outputUri, options) command with the following arguments:
             • tableName : your table name.
               • Unfortunately there doesn’t seem to be a way to apply a WHERE filter or export the results of a SELECT query, (though as with mysqldump you could always save your query results to a new TABLE , then export that table, then DROP TABLE when you’re done with it. Remember that TEMPORARY TABLE won’t work here as tables created in one session aren’t visible from any other session — and mysqlsh will have its own session.
             • outputUri : To save the file locally use a file:/// URI.
               • On Windows you can use a forward-slash as a directory-name separator instead of a backslash. e.g. file:///C:/Users/Me/Desktop/export.csv .
             • options : To ensure compatibility with Excel specify < dialect: "csv", fieldsEscapedBy: "">.
               • The dialect: «csv» option sets Excel-compatible defaults for all-but-one of the OUTFILE parameters, so you must also specify fieldsEscapedBy: «» , otherwise SQL NULL will be be rendered as \N (literally) while double-quotes and line-breaks inside text values will be backslash-escaped, which Excel doesn’t support.

          Источник

          How to save MySQL query output to excel or .txt file? [duplicate]

          How do you save output of a MySQL query to a MS Excel sheet? Even if it’s only possible to store the data in a .txt file, it will be okay.

          — Goto PHPmyadmin in browser and choose database — Execute your sql query in the SQL tab in phpMyAdmin or select tables. — After execution / after select the table , scroll down the page and look for «Query results operations» — Click «Export» link and there is a page to export all the results you can select desired format

          2 Answers 2

          MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other application which accepts data in CSV format.

          Given a query such as

          SELECT order_id,product_name,qty FROM orders 

          which returns three columns of data, the results can be placed into the file /tmp/orders.txt using the query:

          SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt' 

          This will create a tab-separated file, each row on its own line. To alter this behavior, it is possible to add modifiers to the query:

          SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' 

          In this example, each field will be enclosed in double quotes, the fields will be separated by commas, and each row will be output on a new line separated by a newline (\n). Sample output of this command would look like:

          "1","Tech-Recipes sock puppet","14.95" "2","Tech-Recipes chef's hat","18.95" 

          Keep in mind that the output file must not already exist and that the user MySQL is running as has write permissions to the directory MySQL is attempting to write the file to.

           SELECT Your_Column_Name FROM Your_Table_Name INTO OUTFILE 'Filename.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' 

          Or you could try to grab the output via the client:

          You could try executing the query from the your local client and redirect the output to a local file destination:

          mysql -user -pass -e "select cols from table where cols not null" > /tmp/output 

          Hint: If you don’t specify an absolute path but use something like INTO OUTFILE ‘output.csv’ or INTO OUTFILE ‘./output.csv’ , it will store the output file to the directory specified by show variables like ‘datadir’; .

          Источник

          Читайте также:  Astra linux ssh server настройка доступа