Изменить формат вывода для командной строки MySQL в CSV
Я хочу получить данные CSV без заголовка из вывода запроса в MySQL в командной строке. Я запускаю этот запрос на другой машине с сервера MySQL, поэтому все эти ответы Google с "INTO OUTFILE" не подходят.
Итак, я запустил mysql -e "select people, places from things"
. Это выводит материал, который выглядит примерно так:
+--------+-------------+
| people | places |
+--------+-------------+
| Bill | Raleigh, NC |
+--------+-------------+
Ну, это нехорошо. Но эй, смотри! Если я просто подключу его к чему-либо, он превратит его в список, разделенный табуляцией:
people places
Bill Raleigh, NC
Это лучше - по крайней мере, программно-анализируемый. Но я не хочу TSV, я хочу CSV, и я не хочу этого заголовка. Я могу избавиться от заголовка с помощью mysql <stuff> | tail -n +2
, но это беспокоит, что я бы хотел избежать, если MySQL просто имеет флаг, чтобы опустить его. И я не могу просто заменить все вкладки запятыми, потому что это не обрабатывает содержимое с запятыми.
Итак, как я могу заставить MySQL опустить заголовок и предоставить мне данные в формате CSV?
Ответы
Ответ 1
Я закончил писать свою собственную командную строку, чтобы позаботиться об этом. Он похож на cut
, за исключением того, что он знает, что делать с цитируемыми полями и т.д. Этот инструмент, в сочетании с ответом @Jimothy, позволяет мне получить CSV без заголовка с удаленного сервера MySQL. У меня нет доступа к файловой системе на мою локальную машину с помощью этой команды:
$ mysql -N -e "select people, places from things" | csvm -i '\t' -o ','
Bill,"Raleigh, NC"
csvmaster on github
Ответ 2
В качестве частичного ответа: mysql -N -B -e "select people, places from things"
-N
указывает, что он не печатает заголовки столбцов. -B
- это "пакетный режим" и использует вкладки для разделения полей.
Если значения разделенных табуляторами не будут достаточными, см. fooobar.com/questions/11692/....
Ответ 3
Как сохранить результаты в CSV на стороне клиента без дополнительных нестандартных инструментов.
В этом примере используется только mysql
клиент и awk
.
Однострочная:
mysql --skip-column-names --batch -e 'select * from dump3' t | awk -F'\t' '{ sep=""; for(i = 1; i <= NF; i++) { gsub(/\\t/,"\t",$i); gsub(/\\n/,"\n",$i); gsub(/\\\\/,"\\",$i); gsub(/"/,"\"\"",$i); printf sep"\""$i"\""; sep=","; if(i==NF){printf"\n"}}}'
Логическое объяснение того, что нужно делать
-
Во-первых, давайте посмотрим как выглядят данные в режиме RAW (с опцией --raw
). база данных и таблица соответственно t
и dump3
Вы можете увидеть, что поле, начинающееся с "новой строки" (в первой строке), разделено на три строки из-за появления новых строк в значении.
mysql --skip-column-names --batch --raw -e 'select * from dump3' t
one line 2 new line
quotation marks " backslash \ two quotation marks "" two backslashes \\ two tabs new line
the end of field
another line 1 another line description without any special chars
- Данные OUTPUT в пакетном режиме (без опции
--raw
) - каждая запись изменена на однострочные тексты, экранируя символы типа \
<tab>
и new-lines
mysql --skip-column-names --batch -e 'select * from dump3' t
one line 2 new line\nquotation marks " backslash \\ two quotation marks "" two backslashes \\\\ two tabs\t\tnew line\nthe end of field
another line 1 another line description without any special chars
- И вывод данных в формате CSV
Подсказка - сохранить данные в формате CSV с экранированными символами.
Для этого нужно преобразовать специальные объекты, которые mysql --batch
создает (\t
как вкладки \\
как обратную сортировку и \n
в качестве новой строки) в эквивалентные байты для каждого значения (поля).
Тогда целое значение сбрасывается на "
и заключено также в "
.
Btw - использование одних и тех же символов для экранирования и вложения мягко упрощает вывод и обработку, потому что у вас нет двух специальных символов.
По этой причине все, что вам нужно делать со значениями (с точки зрения формата CSV), - это изменить "
на ""
значения whithin. В более общем виде (с экранированием и заключением соответственно \
и "
) вам нужно сначала изменить \
на \\
, а затем изменить "
на \"
.
И объяснение команд пошагово:
# we produce one-line output as showed in step 2.
mysql --skip-column-names --batch -e 'select * from dump3' t
# set fields separator to because mysql produces in that way
| awk -F'\t'
# this start iterating every line/record from the mysql data - standard behaviour of awk
'{
# field separator is empty because we don't print a separator before the first output field
sep="";
-- iterating by every field and converting the field to csv proper value
for(i = 1; i <= NF; i++) {
-- note: \\ two shlashes below mean \ for awk because they're escaped
-- changing \t into byte corresponding to <tab>
gsub(/\\t/, "\t",$i);
-- changing \n into byte corresponding to new line
gsub(/\\n/, "\n",$i);
-- changing two \\ into one \
gsub(/\\\\/,"\\",$i);
-- changing value into CSV proper one literally - change " into ""
gsub(/"/, "\"\"",$i);
-- print output field enclosed by " and adding separator before
printf sep"\""$i"\"";
-- separator is set after first field is processed - because earlier we don't need it
sep=",";
-- adding new line after the last field processed - so this indicates csv record separator
if(i==NF) {printf"\n"}
}
}'
Ответ 4
mysqldump
утилита может помочь вам, в основном с опцией --tab
, она завернута в оператор SELECT INTO OUTFILE
.
Пример:
mysqldump -u root -p --tab=/tmp world Country --fields-enclosed-by='"' --fields-terminated-by="," --lines-terminated-by="\n" --no-create-info
Это создаст файл csv format /tmp/Country.txt
Ответ 5
Как насчет использования sed? Он поставляется с большинством (всего?) ОС Linux.
sed 's/\t/<your_field_delimiter>/g'
.
В этом примере используется GNU sed (Linux). Для POSIX sed (AIX/Solaris), я полагаю, вы написали бы буквальную TAB вместо \t
Пример (для выхода CSV):
#mysql mysql -B -e "select * from user" | while read; do sed 's/\t/,/g'; done
localhost,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
localhost,bill,*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0,,
127.0.0.1,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
::1,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,
%,jim,*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,,,,,0,0,0,0,,
Ответ 6
Вышеупомянутые решения работают только в особых случаях. Вы столкнетесь со всеми неприятностями со встроенными запятыми, встроенными кавычками и другими вещами, которые делают CSV трудным в общем случае.
Сделайте себе одолжение и используйте общее решение - сделайте это правильно, и вам больше не придется об этом думать. Одним очень сильным решением является утилита командной строки csvkit
- доступна для всех операционных систем через Python. Установите через pip install csvkit
. Это даст вам правильные данные CSV:
mysql -e "select people, places from things" | csvcut -t
Это создает данные, разделенные запятыми, с заголовком, все еще на месте. Чтобы удалить строку заголовка:
mysql -e "select people, places from things" | csvcut -t | tail -n +2
Это создает то, что запросил OP.