-1

I am trying to export a result set into a csv file and load it to mysql.

mysql -e "select *  from temp" > '/usr/apps/{path}/some.csv'

The out put file is not importable. It has the query, headers and bunch of unwanted lines. All I want is just the COMMA delimited VALUES in the file, so that I can import it back.

What did I try so far?

  1. Added | sed 's/\t/,/g' - Did not help
  2. Tried OUTFILE but it did not work.
  3. Tried SHOW VARIABLES LIKE "secure_file_priv" which gave null.

OUTFILE will not work for me because I get the error "The MySQL server is running with the --secure-file-priv option so it cannot execute this statement". I cannot edit the variable secure-file-priv. And it has a null value right now.

I get the file output as below image. I used the alias mysql2csv='sed '\''s/\t/","/g;s/^/"/;s/$/"/;s/\n//g'\'''

enter image description here

suresh
  • 783
  • 1
  • 7
  • 19
  • See https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format – Сергей Емельянов Jun 14 '19 at 21:05
  • The script has problems and does not execute. Please fix it. Also see [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) and [How to use Shellcheck](http://github.com/koalaman/shellcheck). – jww Jun 14 '19 at 21:28
  • @jww I just wrote it for example. please see now. – suresh Jun 14 '19 at 22:27

2 Answers2

1

This page shows you how to export to a CSV using the command line:

https://coderwall.com/p/medjwq/mysql-output-as-csv-on-command-line

From that page:

# add alias to .bashrc
alias mysql2csv='sed '\''s/\t/","/g;s/^/"/;s/$/"/;s/\n//g'\'''

$ mysql <usual args here> -e "SELECT * FROM foo" | mysql2csv > foo.csv
Sloan Thrasher
  • 4,806
  • 2
  • 19
  • 39
  • Consider that this may not work as expected if some columns in foo may have values that contain tab (`\t`) and/or double quote (`"`) characters. – spencer7593 Jun 14 '19 at 21:11
  • This one still gives the result as | delimited and all comes as a single column on csv – suresh Jun 14 '19 at 22:01
  • Dd you use the parameters suggested by @spencer7593 in your sql statement that control the formatting of the output? – Sloan Thrasher Jun 15 '19 at 02:58
  • @SloanThrasher I don't think i can put them in my sql unless i do an output file. Is that incorrect. Thank you so much for your help. – suresh Jun 17 '19 at 16:34
  • 1
    Yes, it will output to a file (as will the example in your question). by including the formatting options in your query, it will format the CSV properly. If you use the option INTO OUTFILE, you don't need to pipe the result to a file. Since it's shell command, you will need to properly escape the quote and any backslash characters. – Sloan Thrasher Jun 17 '19 at 20:01
0

Since you're trying things, why not try something like the example given in the MySQL Reference Manual?

https://dev.mysql.com/doc/refman/8.0/en/select-into.html

Excerpt:

Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

From a shell script, if we are going to have literal SQL in the script, we may need to take care with the single quote and the backslash characters. For example, the \n (backslash n characters) need to be sent to the MySQL server as part of the SQL text. We have to be careful that the backslash character doesn't get swallowed by the shell script.

spencer7593
  • 99,718
  • 14
  • 99
  • 122