Как перевести sql в csv
Импорт CSV в SQL Server
CSV представляет собой компактный текстовый формат для хранения табличных данных. С файлами в формате CSV способны работать (экспортировать/импортировать данные) все современные приложения для работы с таблицами (например, Excel). Не так давно передо мной возникла задача выгрузить данные из CSV-файла в базу данных. Решение этой проблемы я приведу в данной статье.
Использование оператора BULK INSERT
Оператор BULK INSERT в SQL Server позволяет осуществлять импорт данных из файла в таблицу. В предложении WITH для данного оператора можно задавать множество опций, но нам нужны только две из них:
FIELDTERMINATOR
Указывает разделить для столбцов. По умолчанию, разделителем является символ табуляции (\t). В CSV разделителем по умолчанию является запятая.
ROWTERMINATOR
Указывает разделитель для строк.
Давайте попробуем выгрузить данные для простого CSV-Файла. Этот файл содержит 10 строк для простого отчета и вы можете скачать его по следующей ссылке. Используйте следующий код:
В качестве разделителя в исходном CSV-файле используется знак табуляции (Tab). В CSV также можно использовать запятые, точки с запятой и другие символы в качестве разделителя столбцов. Главное указать символ в параметре FIELDTERMINATOR.
Выберите теперь все строки из таблицы CSV_Export с помощью оператора SELECT для проверки результата:
На рисунке ниже показан результат:
Как правило, данные из CSV-файла нужно сохранять во временную таблицу, а затем из нее вставлять в основную, т.к. зачастую необходимо парсить даты, числа с плавающей запятой и другие форматы. Их можно спарсить в строку во временной таблице и привести к нужному формату в основной. Например, если мы изменим тип столбца DateReport на DATETIME, SQL Server выдаст следующую ошибку:
В следующем примере показано, как можно обойти эту ошибку и получить дату:
Теперь в основной таблице столбец DateReport будет храниться в формате даты:
Использование SQL Server Management Studio
Импортировать данные из CSV можно стандартными средствами программы SQL Server Management Studio. Для этого выполните следующие шаги:
На экране появится диалоговое окно, в котором нужно выбрать источник данных DataSource из выпадающего списка. Выберите вариант Flat File Source и нажмите кнопку Next. На следующем экране укажите путь до файла CSV на жестком диске. Для нашего тестового файла нужно снять галочку «Column names in the first data row», т.к. в файле не содержатся заголовки столбцов в первой строке.
После этого SQL Server Management Studio загрузит файл и отобразит данные в таблице:
Обратите внимание, что программа автоматически определит разделитель между столбцами и строками в CSV-файле. Вы также можете настроить параметры столбцов (по умолчанию SSMS задаст столбцы в формате NVARCHAR(50) с названиями Column 0, Column 1 и т.д.) Перейдите на вкладку Advanced в левом боковом меню:
Как видите я изменил названия столбцов. Если у вас есть строки длиной больше 50, попросите SQL Server проверить все столбцы в файле. Это можно сделать с помощью кнопки Suggest Types (Предложить типы). SQL Server проанализирует первые 100 строк в файле и укажет предлагаемые типы для каждого столбца. В процессе проверки указывается ошибка. В зависимости от размера файла вы можете выбрать вариант, чтобы просмотреть весь файл или просто выбранные поля.
На следующем экране вы можете выбрать таблицу, в которую нужно импортировать данные. Здесь можно выбрать одну из существующих таблиц в базе данных, либо таблицу dbo.report, которую SSMS сгенерирует автоматически. Я рекомендую использовать второй вариант, а потом вручную преобразовать типы данных в основную таблицу.
После всех выполненных шагов SQL Server Management Studio отобразит процесс выполнения, сгенерирует таблицу report и заполнит ее данными:
На следующем рисунке отображена сгенерированная таблица report в окне Object Explorer:
Теперь выполните следующую инструкцию:
В результате мы получим результаты, аналогичные подходу с использованием кода.
SQL в CSV с помощью DBMS_SQL
Часто при решении задач системной интеграции требуется представить некоторый объем данных в том или ином формате. При этом потребителем данных может быть кто угодно, а вот источником почти всегда является корпоративная база данных. К примеру, производитель может требовать у поставщика периодические отчеты о движении своих товаров в формате XLSX или XML, etc.
Существует множество инструментальных средств для преобразования данных в различные форматы, и возможность их использования зависит от принятого на предприятии технологического стека и программной архитектуры. При этом всегда хочется, чтобы цепочка, состоящая из различных библиотек, фреймворков, системных слоев, используемых для преобразования исходных данных, была как можно короче. Это бы сократило временные затраты на разработку решения и повысило его производительную эффективность.
Если принять, что по факту в корне процесса выборки данных лежит SQL-запрос, то в идеале цепочку преобразований хотелось бы видеть такой:
где
— исходные данные,
— SQL-запрос на выборку данных,
— функция, которая преобразует выборку в требуемый формат,
— данные в требуемом формате.
Для Oracle PL/SQL существует ряд встроенных и сторонних пакетов, которые реализуют подобную функциональность. Это DBMS_XMLGEN, DBMS_XMLQUERY, AS_XLSX, PL/JSON и другие.
Однако, когда встал вопрос о преобразовании данных в формат CSV, готовых решений почему-то не нашлось. Пришлось делать самому, далее будет показано, как.
Создать инструмент (пакет PL/SQL), который на входе принимает произвольный SELECT-запрос в виде строки или в виде курсорной переменной, а на выходе возвращает объект типа CLOB, инкапсулирующий данные в формате CSV. В случае любой ошибки должен возвращаться NULL. Сам формат CSV в представлении не нуждается — это строки, элементы которых разделены некоторым символом, чаще всего «;», но в общем случае в качестве разделителя может выступать произвольный символ. Примем, что для разделения строк используются символы 0x0D + 0x0A. Первая строка в CSV-файле, как правило, является заголовочной и определяет имена столбцов.
Определим интерфейс пакета
Здесь две перегруженных процедуры, разница между ними в том, что одна из них принимает запрос в виде строки, а другая — в виде ссылки на курсор. Второй параметр является выходным, это требуемый результат в CLOB-объекте. Наконец, третий параметр — CSV-разделитель.
В реализации этих процедур нам поможет встроенный пакет DBMS_SQL, который позволяет работать с динамическими курсорами, когда заранее (на этапе компиляции) неизвестно, сколько именно столбцов задействовано в выборке.
Возможности DBMS_SQL позволяют динамически разбирать и выполнять произвольные запросы. Для процедуры, принимающей запрос в виде строки, это происходит так:
Для процедуры, принимающей курсорную переменную, все проще — начиная с 11-ой версии Oracle стало доступно преобразование «курсорная переменная → номер курсора SQL».
Функция DBMS_SQL.TO_CURSOR_NUMBER преобразует переменную REFCURSOR (сильно- или слаботипизированную) в номер курсора SQL, который затем может передаваться подпрограммам DBMS_SQL. При этом курсорная переменная должна быть открыта до ее передачи функции DBMS_SQL.TO_CURSOR_NUMBER.
Таким образом, оба варианта вызова свелись к получению номера динамического курсора и связанного с ним набора данных. Следующий этап — получение информации о столбцах этого набора и извлечение самих данных.
Пакет DMBS_SQL позволяет описывать столбцы динамического курсора, возвращая информацию о каждом столбце в ассоциативном массиве записей.
Для этого необходимо объявить коллекцию PL/SQL на базе типа коллекции DBMS_SQL.DESC_TAB (или DESC_TAB2, если запрос может возвращать имена столбцов, длина которых превышает 30 символов). После этого можно использовать методы коллекций для перебора таблицы и извлечения информации о курсоре.
Далее пакету DBMS_SQL необходимо сообщить тип каждого столбца, выбранного при помощи динамического запроса. Это делается посредством вызова DEFINE_COLUMN.
Во втором аргументе DEFINE_COLUMN передается число — последовательная позиция столбца в списке. Третий аргумент задает тип данных столбца курсора. В нем передается выражение соответствующего типа. Иначе говоря, DBMS_SQL.DEFINE_COLUMN передается не строка с названием типа (скажем, «VARCHAR2»), а переменная, определенная с типом VARCHAR2.
При определении столбца символьного типа в четвертом аргументе необходимо задать максимальную длину значений, которые могут загружаться в курсор.
Подготовительные операции завершены, теперь можно сформировать строку заголовка и начинать извлекать данные.
Данные извлекаются построчно с помощью DBMS_SQL.FETCH_ROWS и последующих вызовов DBMS_SQL.COLUMN_VALUE для получения значения отдельных столбцов.
Далее остается только собрать результирующий CSV
Варианты использования пакета
В разработке помогла книга
Фейерштейн С., Прибыл Б. — Oracle PL/SQL. Для профессионалов.
Экспорт данных в CSV файл из Microsoft SQL Server, используя Access 2003
Если у Вас возникла необходимость выгружать данные из базы MS SQL Server в текстовый формат CSV, то из данного материала Вы узнаете, как это можно сделать средствами Access 2003.
Итак, если у Вас встала задача выгрузить данные с SQL сервера в CSV файл, то ее можно решить несколькими способами, например:
Все они не универсальные и не идеальные, поэтому какой способ использовать зависит от ситуации. Например, для администратора или программиста баз данных SSIS будет отличным вариантов для массового экспорта данных, но он не подходит, если необходимо, например, интегрировать данную возможность в свое приложение, чтобы обычные пользователи могли пользоваться ей.
Программа BCP тоже не подходит, даже если вызывать ее средствами T-SQL, используя хранимую процедуру, так как для этого потребуется включить возможность выполнения xp_cmdshell, а это не безопасно.
Конструкция INSERT INTO OPENROWSET подразумевает наличие уже созданного CSV файла с нужными заголовками полей, тоже не подходит, так как это неудобно.
Остается только использовать что-то другое, например, Access, и для меня это оказалось лучшим вариантом, так как приложение, в которое мне необходимо было интегрировать возможность экспорта в CSV, как раз реализовано в виде ADP проекта Access 2003.
Сейчас в тестовом варианте я покажу Вам, как можно в ADP проекте Access 2003 реализовать возможность выгрузки результатов запросов в CSV файл.
Исходные данные
В качестве примера я буду использовать СУБД Microsoft SQL Server 2012 Express.
Допустим, у нас будет база данных Test, а в ней следующая таблица
Заполним таблицу тестовыми данными
Посмотрим, что у нас получилось
Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих. В нем подробно рассмотрены все базовые конструкции языка T-SQL.
И эти данные нам нужно выгрузить в CSV файл и не просто выгрузить, а реализовать функционал, с помощью которого обычные пользователи могли бы сами экспортировать данные. В качестве разделителя допустим, нам необходимо использовать точку с запятой.
Реализация экспорта данных с SQL сервера в формат CSV в Access 2003
Так как CSV это обычный текстовый формат данных, где значения разделяются запятой или другим символом нам достаточно написать код VBA, который позволял бы сохранять текстовый файл с расширением CSV.
Для этого открываем свой ADP проект, он соответственно должен быть подключен к тестовой базе Test (или к той, которая нужна Вам).
Создаем форму или открываем на редактирование существующую форму и добавляем на ней одну кнопку, например, я ее назвал ExportInCSV.
Примечание! Сразу скажу, что в примере ниже я показываю сам принцип выгрузки, но данную возможность можно сделать более универсальной, например, на форму добавить текстовое поле и в него писать SQL запрос, для того чтобы передавать в процедуру или сделать возможность самостоятельно указывать любой разделитель, в примере ниже он прописан вручную в коде или, например, Вам нужно динамически формировать название экспортируемого файла все это под свои нужды Вы легко можете доработать самостоятельно.
В «Обработку события» нашей кнопки вставляем следующий код VBA (я его подробно прокомментировал):
Примечание! Для работы FileDialog требуется подключить библиотеку Microsoft Office 11.0 Object Library. Это делается следующим образом: в редакторе Microsoft Visual Basic (где вы вставляете код VBA) откройте меню Tools->References, найдите нужную библиотеку, поставьте галочку и нажмите «ОК».
Сохраняем и запускаем нашу форму, щелкаем по кнопке (в моем случае ExportInCSV), в итоге у нас откроется диалоговое окно выбора пути сохранения файла, мы выбираем каталог для экспорта и жмем «Сохранить». Затем подтверждаем свое действие и жмем «ОК».
После того как выгрузка будет завершена, появится соответствующее сообщение. Для проверки открываем каталог, в который выгрузился файл, находим файл TestFile.csv и открываем его любым блокнотом, например, мне нравится Notepad++.
Видим, что нужные данные у нас выгрузились и разделитель точка с запятой, все как мы и хотели.
Экспорт данных из SQL в csv
С помощью утилиты ScriptExecuter можно сделать выгрузку данных из базы SQL с нужными критериями в файл формата .csv. Например по блюдам, ценам, продажам (наличная, безналичная оплаты, возвраты) за определенный промежуток времени.
Состав дистрибутива
Для выгрузки необходимо скачать дистрибутив ScriptExecuter c ftp://ftp.ucs.ru. В дистрибутиве находятся :
Настройка утилиты
DECLARE
@RSIFR INT = :rsifr
SELECT TOP 1000
[SIFR]
,[CODE]
FROM [RK7Base].[dbo].[MENUITEMS]
where (CODE=2)
Ниже приведены варианты команд из файла readme.txt
Запуск утилиты
Перед запуском нужно создать ярлык у приложения ScriptExecuter и в свойствах ярлыка прописать параметр -list:list.txt
Выгрузка из SQL на sFTP и из StoreHouse
Есть доработка, позволяющая выгрузить данные из StoreHouse.
К статье приложен архив, содержащий 2 файла.
На компьютере должен быть установлен MS SQL.
Использование. Запустите скрипт 105829. Или откройте его как текстовый файл, скопируйте и вставьте в консоль MS SQL
ИТ База знаний
Полезно
— Онлайн генератор устойчивых паролей
— Онлайн калькулятор подсетей
— Руководство администратора FreePBX на русском языке
— Руководство администратора Cisco UCM/CME на русском языке
— Руководство администратора по Linux/Unix
Навигация
Серверные решения
Телефония
FreePBX и Asterisk
Настройка программных телефонов
Корпоративные сети
Протоколы и стандарты
Экспорт и импорт CSV в MySQL
Онлайн курс по Linux
Мы собрали концентрат самых востребованных знаний, которые позволят тебе начать карьеру администратора Linux, расширить текущие знания и сделать уверенный шаг к DevOps
Это подробное руководство покажет вам, как экспортировать базу данных MySQL в файл CSV и импортировать файл CSV обратно в базу данных MySQL.
Экспорт MySQL в CSV
Экспорт MySQL в CSV с phpMyAdmin
Инструмент phpMyAdmin предоставляет бесплатный графический интерфейс для управления базами данных MySQL. Вы можете использовать его для экспорта любой из отслеживаемых баз данных в файл CSV.
Экспорт из MySQL в CSV с помощью командной строки
Вы можете выполнить экспорт без излишеств через CLI, выбрав все данные в таблице и указав место, куда их нужно сохранить.
Начните с открытия оболочки MySQL, затем переключитесь на базу данных, которую вы хотите экспортировать.
Введите следующую команду:
Примечание. В этом примере используется местоположение файла Linux. Если вы работаете в Windows, вы можете использовать c:/folder/file.csv для вашего местоположения файла.
Дополнительные параметры для экспорта из MySQL
Чтобы указать отдельные наборы данных для экспорта из таблицы:
myTable реальным именем экспортируемой таблицы.
Примечание: вы можете заметить команду SELECT * FROM в скобках. Мы обернули команду в функцию, которая добавляет метку времени.
Экспорт с заголовками столбцов
Используйте оператор UNION для добавления заголовков столбцов в экспортируемый файл:
Работа с пустыми значениями
Экспорт MySQL в CSV с помощью mysqldump
Вы можете использовать приложение mysqldump для экспорта базы данных MySQL в файл CSV. Введите в командной строке следующее:
Примечание. Ваша учетная запись должна иметь права доступа к каталогу, в котором сохранен файл CSV. Вы можете сохранить его по другому пути, но это должен быть путь, к которому у MySQL есть полный доступ.
Экспорт MySQL в CSV с использованием CSV Engine
Этот оператор изменяет формат базы данных на CSV. Затем вы можете скопировать файл CSV в другую систему.
В определенных конфигурациях, CSV Engine или метод mysqldump также могут сэкономить много времени.
Импорт CSV в MySQL
Импорт файла CSV с помощью командной строки
Шаг 1: Доступ к оболочке MySQL
Откройте окно терминала и войдите в MySQL, используя следующую команду:
Замените username вашим реальным именем пользователя. Система предложит вам ввести пароль для вашего пользователя MySQL. Ввод правильного пароля дает вам доступ к клиенту MySQL.
Шаг 2. Создание таблицы MySQL для импорта в CSV
Столбцы в вашей таблице MySQL должны соответствовать данным из файла CSV, который вы планируете импортировать. Если у вас уже есть таблица, готовая для импорта в CSV, вы можете перейти к шагу 3 руководства.
Выберите базу данных, введя следующую команду:
Замените database_name именем базы данных, в которую вы импортируете данные. Используйте следующую команду для создания новой таблицы:
Замените значение table_name на имя, которое вы хотите использовать для своей таблицы. Переменная column_n представляет имена для каждого столбца в вашем CSV-файле, и вы должны отредактировать их соответствующим образом.
Вы можете добавлять, удалять или изменять типы данных, используемые в этом примере, в соответствии с вашими потребностями. Посетите официальную документацию MySQL по типам данных, чтобы узнать больше.
Шаг 3: Импорт CSV в MySQL Table
Импортируйте данные из файла CSV в базу данных MySQL, используя следующие строки:
Давайте, в свою очередь, рассмотрим действия, стоящие за каждой из этих строк:
Импорт CSV с помощью phpMyAdmin
Если ваш сервер или база данных использует phpMyAdmin, вы можете использовать графический интерфейс для импорта файла CSV.
Заключение
Теперь вы знаете, как экспортировать CSV-файлы из MySQL и импортировать CSV-файлы в MySQL из командной строки и с помощью phpMyAdmin.
Онлайн курс по Linux
Мы собрали концентрат самых востребованных знаний, которые позволят тебе начать карьеру администратора Linux, расширить текущие знания и сделать уверенный шаг к DevOps