Export a table as csv in mysql from shell script [duplicate]

Question

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?

Added | sed 's/\t/,/g' - Did not help
Tried OUTFILE but it did not work.
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'\'''

The script has problems and does not execute. Please fix it. Also see How to create a Minimal, Complete, and Verifiable example and How to use Shellcheck. — jww
– jww, Commented Jun 14, 2019 at 21:28

Sloan Thrasher · Accepted Answer · 2019-06-14 21:07:37Z

2

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

answered Jun 14, 2019 at 21:07

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

spencer7593 Over a year ago

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.

suresh Over a year ago

This one still gives the result as | delimited and all comes as a single column on csv

Sloan Thrasher Over a year ago

Dd you use the parameters suggested by @spencer7593 in your sql statement that control the formatting of the output?

suresh Over a year ago

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

Sloan Thrasher Over a year ago

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.

|

spencer7593 · Accepted Answer · 2019-06-14 21:05:07Z

1

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.

answered Jun 14, 2019 at 21:05

spencer7593

109k15 gold badges122 silver badges148 bronze badges

1 Comment

suresh Over a year ago

OUTFILE is not an option for me. Thank you so much for your answer.

Collectives™ on Stack Overflow

Export a table as csv in mysql from shell script [duplicate]

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related