How do I convert MySQL CLI query output to CSV

Question

A colleague on the other side of the world sent me some output from a MySQL CLI client query in text format. She can't convert it to CSV for me or use the INTO OUTFILE output flag to output directly to CSV. How can I convert it to CSV format? It looks like this:

+-------------+------------------+------------------------------------------+-------------+
| pet_id      | pet_identity_id  | identity_value                           | pet_species |
+-------------+------------------+------------------------------------------+-------------+
|       77626 |          3140819 | [email protected]                  | dog         |
|       77625 |          3140818 | [email protected]                  | cat         |
|       77622 |          3140815 | [email protected]                       | aardvark    |
|       77583 |          3140776 | [email protected]                  | cow         |
+-------------+------------------+------------------------------------------+-------------+
4 rows in set (0.01 sec)

I want it to look like this CSV format (either pipe- or comma-separated):

"pet_id"|"pet_identity_id"|"identity_value"|"pet_species"
77626|3140819|"[email protected]"|"dog"
77625|3140818|"[email protected]"|"cat"
77622|3140815|"[email protected]"|"aardvark"
77583|3140776|"[email protected]"|"cow"

I've found various questions which let you do this in the CLI client using the INTO OUTFILE notation, but nothing to just convert a query sample sent to you by someone in the form you see on screen in the MySQL client.

Possible duplicate of [How to output MySQL query results in CSV format?](https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format) — Nic3500, Nov 07 '17 at 12:17
Thanks @Nic3500 I edited the question to explain better that this applies to output where you don't have access to the CLI yourself - in this case someone has sent you (or you find online) sample output cut-and-paste from the CLI. — Sam Critchley, Nov 07 '17 at 12:23
There's another similar method here http://tlug.dnho.net/?q=node/209 — Sam Critchley, Nov 07 '17 at 15:15

Sam Critchley · Answer 1 · 2017-11-07T12:18:32.347

Here's a little shell script using sed which can do just that:

#!/bin/bash
# A script to convert MySQL CLI output to CSV format

# cat the file and pipe to the next step
cat $1 | \
# grep only the lines with '|' in them
grep "\|" | \
# Remove the lines which begin with '+'
sed -e '/^+/d' | \
# Remove the whitespace around the '|' characters
sed -e 's/[[:space:]]*|[[:space:]]*/|/g' | \
# Put a double quote before every '|' character
sed -e 's/|\(.\{1\}\)/\"&/g' | \
# Put a double quote after every '|' character
sed -e 's/\(.\{1\}\)|/&\"/g' | \
# Remove the extra '"|' from the beginning of each line
sed -e 's/^\"|//g' | \
# Remove the extra '"' from the end of each line
sed -e 's/\"$//g' | \
# Remove the '|' from the end of each line
sed -e 's/|$/\"/g' | \
# Remove the quotes from any purely numeric fields
sed -e 's/"\([[:digit:]]*\)"/\1/g'

Just save the file as e.g. convert-mysql.sh then copy and paste the MySQL output into a text file mysql-output.txt and run e.g.:

$ bash ./convert-mysql.sh mysql-output.txt

That will give you this output:

"pet_id"|"pet_identity_id"|"identity_value"|"pet_species"
77626|3140819|"[email protected]"|"dog"
77625|3140818|"[email protected]"|"cat"
77622|3140815|"[email protected]"|"aardvark"
77583|3140776|"[email protected]"|"cow"

This works on a Mac although there might be slight differences in sed on various Linux flavours, for example [[:digit:]]* in my shell script above was [[:digit:]]+ in some examples I found.

tonypdmtr · Answer 2 · 2017-11-07T23:00:38.317

From what I understand you're looking for some method to convert a 'framed' table as output by MySQL into CSV. So, here's my own little Lua script (http://www.lua.org) that also does proper internal quote escaping according to RFC4180:

local tabs,counter                      --to keep track of column markers
for line in io.lines(arg[1]) do
  if line:match '^%+[+-]+%+$' then      --frame line
    tabs = {}
    counter = 0
    for ch in line:gmatch '.' do
      counter = counter + 1
      if ch == '+' then tabs[#tabs+1] = counter end
    end
  elseif line:sub(1,1) == '|' then      --data line
    for _,tab in ipairs(tabs) do
      line = line:sub(1,tab-1) .. '\0' .. line:sub(tab+1)
    end
    line = line:gsub('%Z+',
      function(s)
        s = s:gsub('^%s*(.-)%s*$','%1')       --remove leading & trailing spaces (optional)
        if s ~= '' and not s:match '^-?%d-%.?%d+$' then
          s = '"' .. s:gsub('"','""') .. '"'  --add quotes while escaping internal ones
        end
        return s
      end)
    line = line:gsub('%z','|')
    print(line:sub(2,-2))
  end
end

It should handle everything except for multi-line fields. You can feed it the file to process by filename as first argument, or via standard input / pipe.

EDIT: Improved version to correctly handle embedded | (pipe) characters.

How do I convert MySQL CLI query output to CSV

2 Answers2