Вставить файл в sql
Обновлено: 02.07.2024
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. Для этого выполните следующие шаги:
В обозревателе объектов Object Explorer выберите целевую базу данных, щелкните по ней правой кнопкой мыши и выберите из контекстного меню команду Tasks --> Import Data.
На экране появится диалоговое окно, в котором нужно выбрать источник данных 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:
Теперь выполните следующую инструкцию:
В результате мы получим результаты, аналогичные подходу с использованием кода.
Мастер импорта неструктурированных файлов позволяет легко скопировать данные из неструктурированного файла (CSV-файл, TXT-файл) в новую таблицу в вашей базе данных. Мастер импорта неструктурированных файлов поддерживает файлы форматирования с разделителями-запятыми и с фиксированной шириной. В этом обзоре описано, почему нужно использовать этот мастер, как его найти, а также приведен простой пример.
Почему нужно использовать этот мастер?
Этот мастер создан на основе интеллектуальной платформы Program Synthesis using Examples (PROSE) и позволяет улучшить текущий процесс импорта. Для пользователей, которые не обладают глубокими знаниями в предметной области, импорт данных часто представляет собой трудную и утомительную задачу, чреватую ошибками. При использовании мастера достаточно указать входной файл и уникальное имя таблицы, и платформа PROSE сделает все остальное.
PROSE анализирует шаблоны данных во входном файле и определяет имена столбцов, типы, разделители и т. д. Платформа запоминает структуру файла и выполняет все действия по обработке данных.
Подробные сведения о том, как улучшен пользовательский интерфейс мастера импорта неструктурированных файлов, см. в этом видео.
Предварительные требования
Эта функция доступна в SQL Server Management Studio (SSMS) 17.3 и более поздних версий. Убедитесь, что вы используете последнюю версию. Ее можно найти здесь.
Приступая к работе
Чтобы открыть мастер импорта неструктурированных файлов, выполните следующие действия.
- Откройте SQL Server Management Studio.
- Подключитесь к экземпляру ядра СУБД SQL Server или к узлу localhost.
- Разверните узел Базы данных, щелкните правой кнопкой мыши базу данных ("test" в примере ниже), выберите Задачи, а затем — Импорт неструктурированного файла над пунктом меню "Импорт данных".
Дополнительные сведения о различных функциях мастера см. в следующем руководстве:
Учебник
При выполнении действий, описанных в этом учебнике, вы можете использовать свой собственный неструктурированный файл. Если у вас нет собственного файла, можете скопировать следующий CSV-файл из Excel. Назовите этот файл example.csv и сохраните его в формате CSV в удобном месте, например на рабочем столе.
Шаг 1. Открытие мастера и страница "Приступая к работе"
Откройте мастер, как описано здесь.
Первая страница мастера — это страница приветствия. Если вы не хотите, чтобы она открывалась снова, установите флажок Больше не показывать это окно.
Шаг 2. Указание входного файла
Имя новой таблицы должно быть уникальным. В противном случае вы не сможете продолжить работу мастера.
Шаг 3. Просмотр данных
Мастер открывает окно предварительного просмотра для первых 50 строк данных. Если в данных есть ошибки, нажмите кнопку "Отмена". В противном случае перейдите к следующей странице.
Шаг 4. Изменение столбцов
Мастер определяет имена столбцов, типы данных и т. д. Здесь можно изменить поля, если они определены неверно (например, указать тип данных с плавающей точкой вместо целочисленного типа).
Столбцы, в которых обнаруживаются пустые значения, будут иметь флажок "Разрешить значения NULL". Однако если в столбце предполагается наличие значений NULL, а флажок "Разрешить значения NULL" не установлен, здесь можно обновить определение таблицы, чтобы разрешить значения NULL в одном или во всех столбцах.
Когда все будет готово, нажмите кнопку "Далее".
Шаг 5. Сводка
Это страница сводки, на которой отображается текущая конфигурация. Если возникли проблемы, можно вернуться к предыдущим страницам мастера. В противном случае нажмите кнопку "Готово", чтобы начать импорт.
Шаг 6. Результаты
На этой странице показан результат импорта. Если на ней есть зеленая галочка, импорт завершен успешно. В противном случае проверьте конфигурацию и входной файл на наличие ошибок.
Устранение неполадок
В этом подразделе содержатся инструкции по добавлению файлов данных или журналов в базу данных на сервере SQL Server при помощи SQL Server Management Studio или Transact-SQL.
В этом разделе
Перед началом работы
Добавление файлов данных или журналов в базу данных при помощи следующих средств.
Перед началом
Ограничения
Добавить или удалить файл во время выполнения инструкции BACKUP невозможно.
Для каждой базы данных может указываться не более 32 767 файлов и 32 767 файловых групп.
безопасность
Permissions
Необходимо разрешение ALTER на базу данных.
Использование среды SQL Server Management Studio
Добавление файлов данных или журналов в базу данных
В обозревателе объектов подключитесь к экземпляру компонента Компонент SQL Server Database Engine и разверните его.
Разверните узел Базы данных, щелкните правой кнопкой мыши базу данных, в которую необходимо добавить файлы, и выберите пункт Свойства.
В диалоговом окне Свойства базы данных перейдите на вкладку Файлы .
Чтобы добавить данные или файл журнала транзакций, нажмите кнопку Добавить.
В сетке Файлы базы данных введите логическое имя файла. Имя файла должно быть уникальным в пределах базы данных.
Выберите тип файла, данные или журнал.
Выберите файловую группу, в которую следует добавить файл данных, или выберите <new filegroup> , чтобы создать новую. Журналы транзакций не могут быть помещены в файловые группы.
Укажите исходный размер файла. Файл данных следует делать как можно большего размера, в соответствии с максимальным предполагаемым объемом данных в базе данных.
Укажите, как должен расширяться файл, нажав кнопку ( . ) в столбце Авторасширение. Выберите один из следующих параметров.
Чтобы разрешить выбранному файлу расти по мере необходимости, установите флажок Разрешить авторасширение и выберите один из следующих параметров.
Чтобы файл увеличивался с фиксированным приращением, выберите параметр В мегабайтах и укажите значение.
Чтобы файл увеличивался на определенный процент от текущего размера, выберите параметр В процентах и укажите значение.
Укажите максимальный размер файла, выбрав один из следующих параметров.
Чтобы указать максимальный размер, до которого может увеличиваться файл, выберите параметр Ограничение размера файла (МБ) и укажите нужное значение.
Чтобы разрешить файлу увеличиваться по мере необходимости, выберите параметр Неограниченный рост размера файлов.
Чтобы предотвратить рост файла, снимите флажок Разрешить авторасширение . При этом файл не превысит размер, указанный в столбце Начальный размер (МБ) .
Максимальный размер базы данных зависит от доступного пространства на диске и от ограничений лицензии, устанавливаемых используемой версией SQL Server .
Укажите путь к файлу. Указанный путь к добавляемому файлу должен существовать.
Данные и журналы транзакций по умолчанию помещаются на один и тот же диск и в один и тот же каталог. Это сделано в соответствии с требованиями, предъявляемыми системами с одним диском, но для рабочей среды это может оказаться неоптимальным. Дополнительные сведения см. в статье Файлы и группы файлов базы данных.
Использование Transact-SQL
Добавление файлов данных или журналов в базу данных
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере выполняется добавление в базу данных группы из двух файлов. В примере в базе данных AdventureWorks2012создается файловая группа Test1FG1 и добавляются два файла по 5 МБ в эту файловую группу.
Главное меню » Базы данных » База данных MySQL » 10 Примеров входной загрузки данных из текстового файла в таблицы MySQL
(2 оценок, среднее: 5,00 из 5)
В базе данных MySQL (или MariaDB), используется команда “load data infile” вы можете загрузить данные из текстового файла в таблицы.
Команда загрузки данных из входного файла обеспечивает несколько гибких вариантов для загрузки различных форматов данных из текстового файла в таблицы.
Следующие примеры загрузки данных рассматриваются в данном руководстве:
- Базовый пример для загрузки данных из текстового файла
- Загрузка данных с помощью опции “Fields terminated by”
- Загрузить данные с помощью опции “Enclosed by”
- Использование экранирующего символа в текстовых данных файла
- Загрузить данных с помощью опции “Lines terminated by”
- Игнорировать строки префикса при отправке файлов с помощью опции “Starting By”
- Игнорировать строки заголовка при загрузки файла
- Загрузить только определенные столбцы (и игнорировать другие) при загрузки из файла
- Использование переменной во время загрузки с опцией “Set”
- Написать Shell Скрипт для загрузки данных из текстового файла
1. Базовый пример для загрузки данных из текстового файла
В следующем примере файл worker.txt имеет значения полей, которые отделены от вкладки.
По умолчанию команда загрузки файла данных использует TAB в качестве поля по умолчанию как разделитель.
Во-первых, перейдите в базу данных, куда вы хотите загрузить текстовый файл. В этом примере мы будем загружать вышеуказанный файл worker.txt в таблицу сотрудников, расположенной базе данных MySQL под названием andreyexbase.
Следующая команда MySQL будет загружать записи из выше указанного файла worker.txt в таблицу сотрудников, как показано ниже. Эта команда не использует никаких дополнительных опций.
Примечание: В приведенном выше примере, команда предполагает, что файл worker.txt находится в директории базы данных. Например, если вы выполняете приведенную выше команду в базу данных andreyexbase, а затем поместите файл в: /var/lib/mysql/andreyexbase/
Ниже будет вывод предыдущей команды.
В приведенном выше:
После загрузки данных, следующее, что мы увидим в таблице сотрудников.
Примечание: Если вы хотите сделать резервную копию MySQL и восстановить всю базу данных MySQL, используйте команду Mysqldump.
2. Выгрузка данных с помощью опции “Fields terminated by”
В следующем примере, во входном файле worker2.txt, значения полей разделяются запятыми.
Для того, чтобы загрузить вышеуказанные записи в таблицe сотрудников, используйте следующую команду.
Во время загрузки, используя вариант “FIELDS TERMINATED BY”, вы можете указать запятую как разделитель полей, как показано ниже.
Опять же, этот параметр используется только тогда, когда значения поля отделяются друг от друга ничем, кроме TAB. Если поля разделяет двоеточие, вы будете использовать следующую опцию в приведенной выше команде:
Ниже приведены несколько основных ошибок, которые могут произойти во время загрузки MySQL
3. Загрузить данные с помощью опции “Enclosed by”
В следующем примере, текстовый файл имеет значения текстового поля, заключенные в двойные кавычки, т.е. name и department имеют двойные кавычки вокруг них.
В этом случае используйте вариант “enclosed by”, как показано ниже.
Приведенная выше команда будет загружать записи должным образом, как показано ниже с помощью команды SELECT в mysql:
4. Использование экранирующего символа в текстовых данных файла
Допустим, у вас запятая в значении той или иной области.
Записи также не загружается должным образом, потому что есть запятая в значении одного из полей.
Правильный файл: Для того, чтобы решить данную проблему, используйте обратную косую черту (\) перед запятой в значении имени поля, как показано ниже.
Ниже будет работать на этот раз без каких-либо ошибок, так как мы указали \ в качестве экранирующего символа.
Вы можете также использовать экранирующий символ, как показано ниже. В этом примере мы используем ^ вместо стандартного \.
5. Выгрузка данных с помощью опции “Lines terminated by”
Вместо того, чтобы все записи были указанны на отдельной строке, вы также можете указать их на той же строке.
В следующем примере, каждая запись отделена символом |.
Чтобы загрузить вышеуказанный файл, используйте вариант, как показано ниже.
Приведенная выше команда будет загружать записи из worker5.txt, как показано ниже.
Ниже приведены несколько моментов, чтобы иметь в виду:
- Если входной файл поступает из окна, то вы можете использовать эту функцию: LINES TERMINATED BY ‘\r\n’
- Если вы используете CSV файл для загрузки данных в таблицу, то попробуйте одну из этих: 1) LINES TERMINATED BY ‘\r’ 2) LINES TERMINATED BY ‘\r\n’
6. Игнорировать строки префикса при отправке файлов с помощью опции “Starting By”
Вы также можете иметь некоторый префикс к записям в текстовом файле ввода, который может быть проигнорирован во время загрузки.
Ниже приводится выход указанной команды:
7. Игнорировать строки заголовка при загрузки из файла
В следующем входном текстовом файле, первая строка является строкой заголовка, который имеет название столбцов.
Во время загрузки, мы хотим, игнорировать 1-й строку заголовка из файла worker7.txt. Для этого используйте опцию IGNORE 1, как показано ниже.
Как видно из следующих выходных данных, даже если входной файл имеет 6 строк, он игнорирует 1-ю линию (которая является строка заголовка) и загрузит оставшиеся 5 строк.
8. Загрузить только определенные столбцы (и игнорировать другие) при загрузки из файла
В следующем примере мы имеем значения только для трех полей. У нас нет столбца department в этом примере файла.
Для того, чтобы загрузить значения из входной записи на определенный столбец в таблице, укажите имена столбцов во время загрузки данных INFILE, как показано ниже. В последней строке в следующей команде есть имена столбцов, которые должны использоваться для загрузки записей из входного текстового файла.
Опять же, имейте в виду, что, когда вы не указываете список столбцов, то команда будет ожидать, что все столбцы должны присутствовать в файле ввода.
Кроме того, если вы не указали список столбцов в последней строке, вы получите ошибку синтаксиса, как показано ниже.
Для этого примера, давайте использовать следующий файл worker2.txt.
В этом примере, мы хотим, увеличить зарплату на 500, прежде чем загружать его в таблицу. Например, заработная плата для Andreyex (является первой записью) 5000. Но, во время загрузки мы хотим увеличить на 500 до 5500, и обновлять это измененное значение в таблице.
Для этого используйте команду SET и использовать зарплату в качестве переменной и сделать приращение, как показано ниже.
Как видно из следующего вывода, столбец зарплата увеличивается на 500 для всех записей во время загрузки данных из текстового файла.
10. Запись Shell Скрипт для загрузки данных из текстового файла
Иногда вы можете загрузить данные из текстового файла автоматически, без необходимости входа в MySQL каждый раз.
Скажем, мы хотим поставить следующую команду внутри сценария оболочки и выполнить это автоматически для базе данных andreyexbase.
Чтобы выполнить загрузку из командной строки, вы можете использовать опцию -e в команде mysql и выполнить его из строки Linux, как показано ниже.
Или, вы можете положить, внутри сценария оболочки, как показано ниже. В этом примере сценарий оболочки loads-data.sh имеет указанную выше команду MySQL.
Дайте разрешение на выполнение на этот скрипт loads-data.sh, и выполните его из командной строки, которая будет загружать данные автоматически в таблицу. Можно также запланировать это как cronjob для загрузки данных из файла автоматически в таблицу на запланированный интервал.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.
В этой статье мы расскажем вам, как использовать оператор LOAD DATA INFILE для импорта CSV-файла в таблицу MySQL.
Оператор LOAD DATA INFILE позволяет считывать данные из текстового файла и очень быстро импортировать их в таблицу базы данных.
Перед импортом файла, вам необходимо подготовить следующее:
- Таблицу базы данных, в которую будут импортированы данные из файла;
- CSV-файл с соответствующим числом столбцов и соответствующим форматом данных в каждом столбце;
- Учетную запись пользователя, который подключается к серверу базы данных MySQL и имеет привилегии FILE и INSERT .
Предположим, что мы имеем таблицу, которая называется discounts со следующей структурой:
Мы используем оператор CREATE TABLE , чтобы создать таблицу discounts :
Следующий оператор импортирует данные из файла c:tmpdiscounts.csv в таблицу discounts :
Поскольку первая строка файла содержит заголовки столбцов, которые не должны быть импортированы в таблицу, мы игнорируем ее, указав опцию IGNORE 1 ROWS .
Теперь мы можем проверить, импортированы ли данные в таблицу discounts :
Преобразование данных при импорте
Иногда формат данных не соответствует целевым столбцам таблицы. В простых случаях, вы можете преобразовать их с помощью условия SET в операторе LOAD DATA INFILE .
Предположим, что столбец данных срока действия скидок в файле discount_2.csv имеет формат мм / дд / гггг:
При импорте данных в таблицу discounts мы должны преобразовать их в формат даты MySQL с помощью функции str_to_date() :
Импорт файла клиента для замещения на сервере базы данных MySQL
Можно импортировать данные от клиента (локального компьютера) на удаленный сервер базы данных MySQL, с помощью оператора LOAD DATA INFILE .
При использовании опции LOCAL в LOAD DATA INFILE клиентская программа считывает файл на стороне клиента и отправляет его на сервер MySQL. Файл будет загружен во временную папку базы данных сервера операционной системы, например, C: Windows Temp для ОС Windows или / TMP для Linux .
Эта папка не настраивается и не задается MySQL.
Давайте рассмотрим следующий пример:
Разница заключается только в опции LOCAL оператора. Если вы загружаете большой CSV файл, вы увидите, что с опцией LOCAL загрузка осуществляется немного медленнее, потому что требуется определенное время, чтобы передать файл серверу базы данных.
Учетная запись пользователя, под которым мы подключается к серверу MySQL для импорта данных, может не иметь привилегию FILE , если используется опция LOCAL .
При импорте файла от клиента на удаленный сервер базы данных с помощью LOAD DATA LOCAL могут возникнуть некоторые проблемы с безопасностью , о которых вы должны знать, чтобы избежать потенциальных рисков.
Импорт CSV файла с помощью MySQL Workbench
MySQL Workbench предоставляет инструмент для импорта данных в таблицу БД. Он позволяет редактировать данные перед внесением изменений.
Ниже приведены этапы импорта данных в таблицу БД:
Откройте таблицу, в которую будут загружаться данные:
Просмотрите данные, нажмите кнопку Apply :
В MySQL Workbench появится диалоговое окно « Apply SQL Script to Database », нажмите кнопку Apply , чтобы добавить данные в таблицу БД.
Мы рассказали вам, как импортировать CSV в таблицу MySQL с помощью LOAD DATA LOCAL , а также с использованием MySQL Workbench .
С помощью этих методов можно загружать данные и из других файлов текстовых форматов, таких, например, как текстовый файл с разделителями табуляции.
Читайте также: