Как экспортировать результаты SQL в CSV и Excel

1. Экспортируйте результат

Перед пояснением: CSV — это обычный текст с запятыми. Что такой файл может сказать о том, 01234 это номер или код?

В графическом инструменте, таком как Браузер БД для SQLite, запустите запрос и используйте кнопку экспорта над результатами или выберите «Файл», затем «Экспорт», затем «Таблицы» в формате CSV. В pgAdmin или Студия управления SQL Serverом эквивалентом является опция «Сохранить результаты как» или «Экспорт» в таблице результатов.

Из командной строки это делают три строки:

sqlite3 mydata.db
.mode csv
.headers on
.once results.csv
SELECT * FROM invoices;

.mode csv устанавливает формат вывода, .headers on сохраняет имена столбцов и .once отправляет в этот файл только вывод следующей команды. Есть и другие режимы, о которых стоит знать: markdown для вставки в документацию, json для подачи другой программы, и insert для генерации операторов SQL, которые перестраивают строки в другом месте.

Что касается вступительного вопроса, то ответ — ничего. CSV вообще не содержит информации о типе, что является корнем всего нижеперечисленного.

2. Доказательство того, что с файлом все в порядке.

Я экспортировал строку, содержащую четыре элемента, которые обычно ломаются: код с ведущими нулями, слово с ударением, десятичную дробь и дату. Затем я посмотрел на фактические байты на диске.

postcode,name,amount,d
01234,"Café",5.1,2026-03-04

Нули есть. 01234, все пять символов. Акцент тоже присутствует и хранится в виде двух байтов, которые UTF-8 использует для é. Дата указана в однозначной форме год-месяц-день. От экспорта ничего не потеряно.

Одна вещь действительно изменилась, и ее стоит назвать, потому что это делает экспорт, а не Excel: я попросил 5.10 и в файле написано 5.1. Завершающие нули — это форматирование отображения, а не часть значения числа, поэтому они не сохраняются при записи. Если в выводе важны два десятичных знака, отформатируйте столбец в текстовом виде в запросе, а не ожидайте, что файл запомнит.

3. Два способа открытия

Дважды щелкните этот файл, и Excel немедленно откроет его, без вопросов. В этом удобстве и заключается проблема, поскольку для его открытия Excel автоматически требует, чтобы Excel решил, что означает каждый столбец, и он плохо решает два конкретных вопроса.

01234 выглядит числом, поэтому оно становится числом 1234, а нули исчезают навсегда. Слово с ударением возникает как Café, поскольку Excel использовал другой набор символов, чем тот, который использовался в файле.

Теперь откройте тот же файл другим способом. В Excel перейдите к Данные, затем Получить данные, затем Из текста/CSVи выберите файл. Появится предварительный просмотр с раскрывающимися списками, точно такими, как описано в импорт CSV в Power BI, потому что это один и тот же базовый инструмент. Установите для параметра «Происхождение файла» значение UTF-8, выберите Преобразование данных, установите тип столбца почтового индекса на «Текст» и загрузите.

Оба раза один и тот же файл. Второй маршрут задает вам вопросы, на которые от вашего имени отвечает первый маршрут, и в этом вся разница. Ведущие нули исправляют рассказывает об этом более подробно и Кодировка символов объясняет é.

4. Исправление акцентов одним флажком

Если люди будут продолжать дважды щелкать ваш файл, и они будут это делать, вы можете решить проблему с кодировкой в ​​​​конце экспорта.

A знак порядка байтов — это три невидимых байта в самом начале файла, которые объявляют «это UTF-8». Excel читает их и получает правильный набор символов, не спрашивая об этом. Большинство экспортированных баз данных не включают его, поэтому акценты разбиваются.

В sqlite3 добавление одного флага:

.once --bom results.csv
SELECT * FROM invoices;

Прежде чем читать дальше, скажите, что это исправляет, а что нет. Это исправляет акценты, потому что в файле теперь указана собственная кодировка. Он не исправляет ведущие нули, поскольку никакой знак порядка байтов не может сказать Excel, что столбец цифр на самом деле является кодом. Нули требуют либо маршрута импорта, либо изменения в запросе.

В том же инструменте есть похожий трюк: .excel отправляет следующий результат прямо в ваше приложение для работы с электронными таблицами, и это занимает --bom слишком. Удобен для быстрого просмотра, менее хорош в качестве повторяемого шага.

5. Когда вам действительно нужен настоящий .xlsx

Переименование CSV в .xlsx не превращает ее в книгу, и Excel будет жаловаться при ее открытии. Если вам действительно нужна настоящая рабочая книга с сохраненными типами и без танцев с импортом, создайте ее.

import sqlite3
import pandas as pd

con = sqlite3.connect('mydata.db')
df = pd.read_sql("SELECT * FROM invoices", con)
df.to_excel('results.xlsx', index=False)

Это требует openpyxl установлен рядом с pandas. Преимущество реально: в xlsx хранится тип каждой ячейки, поэтому текстовый столбец остается текстовым, нули сохраняются, а акценты никогда не подвергаются сомнению. Получатель дважды щелкает по нему, и все правильно.

index=False имеет большее значение, чем кажется. Без него вы получите дополнительный безымянный первый столбец, содержащий номера строк, что затем сбивает с толку того, кто получает файл, что он думает, что это идентификатор.

Выбирайте CSV, когда файл передается другой программе, и xlsx, когда человек собирается его открыть. В этом все правило, и оно обычно решается само собой, как только вы спрашиваете, кто на другом конце провода.

6. Проверки, которые нужно запускать каждый раз

Четыре проверки менее чем за минуту, и они ловят почти все.

1. Количество строк. Сравните строки в файле со строками, возвращенными запросом. Тихое усечение выглядит точно так же, как успешный экспорт.
2. Оба конца каждого столбца кода. Отсортируйте по нему и посмотрите на первое и последнее значения. Отсутствующие ведущие нули отображаются немедленно.
3. Одно акцентированное или неанглийское значение. Если у вас есть данные, найдите их и просмотрите. Эта единственная ячейка говорит о том, что кодировка сохранилась.
4. Одно свидание. Сравните это с датой, которую вы знаете. Перестановка дня и месяца — самое распространенное скрытое повреждение общего файла.

Теперь представьте себе последний экспорт, который вы кому-то отправили. Какой из этих четырех вы проверили, прежде чем нажать «Отправить»? Для большинства из нас честный ответ — нет, и это нормально до тех пор, пока файл не попадет к клиенту.

Что пошло не так и как это исправить

Шесть, которые стоит знать.

Ведущие нули исчезли. Excel читает код как число. Импортируйте маршрут со столбцом «Текст» или отправьте xlsx.

é где é должно быть. Кодирование. Экспортировать с --bomили установите для источника файла значение UTF-8 при импорте.

Длинный номер стал 1.23457E+14. Научное обозначение. Все, что превышает 15 цифр, также безвозвратно теряет свои последние цифры, что важно для номеров карт и длинных идентификаторов. Установите для столбца значение «Текст» при импорте и никогда не позволяйте файлу сохраняться из этого состояния.

Сроки истекают на месяц. День и месяц меняются местами в зависимости от локали. Экспортировать даты как YYYY-MM-DD, который нельзя неправильно прочитать, и явно задайте тип при импорте.

Строки разделены по строкам. Текстовое поле содержит разрыв строки. Правильное цитирование решает эту проблему, но убедитесь, что все, что читает файл, учитывает разрывы строк в кавычках.

Слишком много строк для Эксель. Рабочий лист останавливается на 1 048 576 строк. Если вы близки к этому, ответом будет суммировать запрос, а не экспортировать больше, и Обработка больших наборов данных покрывает остальное.