Импорт CSV в SQL Server

IT блог --- Импорт в SQL Server

CSV представляет собой компактный текстовый формат для хранения табличных данных. С файлами в формате CSV способны работать (экспортировать/импортировать данные) все современные приложения для работы с таблицами (например, Excel). Не так давно передо мной возникла задача выгрузить данные из CSV-файла в базу данных. Решение этой проблемы я приведу в данной статье.

Использование оператора BULK INSERT

Оператор BULK INSERT в SQL Server позволяет осуществлять импорт данных из файла в таблицу. В предложении WITH для данного оператора можно задавать множество опций, но нам нужны только две из них:

FIELDTERMINATOR

Указывает разделить для столбцов. По умолчанию, разделителем является символ табуляции (\t). В CSV разделителем по умолчанию является запятая.

ROWTERMINATOR

Указывает разделитель для строк.

Давайте попробуем выгрузить данные для простого CSV-Файла. Этот файл содержит 10 строк для простого отчета и вы можете скачать его по следующей ссылке. Используйте следующий код:

IF (OBJECT_ID('CSV_Export') IS NOT NULL) DROP TABLE dbo.CSV_Export;

CREATE TABLE CSV_Export (
	DateReport VARCHAR(15),
	CountView INT,
	CountClicks INT,
	CTR FLOAT,
	RPM FLOAT,
	Profit FLOAT
)

BULK INSERT dbo.CSV_Export
FROM 'D:\Report.csv'
WITH (fieldterminator = '	', rowterminator = '\n');

В качестве разделителя в исходном CSV-файле используется знак табуляции (Tab). В CSV также можно использовать запятые, точки с запятой и другие символы в качестве разделителя столбцов. Главное указать символ в параметре FIELDTERMINATOR.

Выберите теперь все строки из таблицы CSV_Export с помощью оператора SELECT для проверки результата:

select top 100 * from CSV_Export;

На рисунке ниже показан результат:

Как правило, данные из CSV-файла нужно сохранять во временную таблицу, а затем из нее вставлять в основную, т.к. зачастую необходимо парсить даты, числа с плавающей запятой и другие форматы. Их можно спарсить в строку во временной таблице и привести к нужному формату в основной. Например, если мы изменим тип столбца DateReport на DATETIME, SQL Server выдаст следующую ошибку:

В следующем примере показано, как можно обойти эту ошибку и получить дату:

-- Создаем временную таблицу и импортируем данные из CSV
IF (OBJECT_ID('tempdb..#csv_temp') IS NOT NULL) DROP TABLE #csv_temp;
CREATE TABLE #csv_temp (
    DateReport VARCHAR(15),
	CountView INT,
	CountClicks INT,
	CTR FLOAT,
	RPM FLOAT,
	Profit FLOAT
);

BULK INSERT #csv_temp
FROM 'D:\Report.csv'
WITH (fieldterminator = '	', rowterminator = '\n');

-- Создаем основную таблицу
IF (OBJECT_ID('CSV_Export') IS NOT NULL) DROP TABLE dbo.CSV_Export;
CREATE TABLE CSV_Export (
	DateReport DATETIME,
	CountView INT,
	CountClicks INT,
	CTR FLOAT,
	RPM FLOAT,
	Profit FLOAT
)

-- Заполняем данными из временной таблицы
INSERT INTO CSV_Export
SELECT CONVERT(datetime, DateReport, 105), -- Строку в дату
	CountView, CountClicks, CTR, RPM, Profit
FROM #csv_temp;

-- Проверить
select top 100 * from CSV_Export;

Теперь в основной таблице столбец DateReport будет храниться в формате даты:

Использование SQL Server Management Studio

Импортировать данные из CSV можно стандартными средствами программы SQL Server Management Studio. Для этого выполните следующие шаги:

1. В обозревателе объектов Object Explorer выберите целевую базу данных, щелкните по ней правой кнопкой мыши и выберите из контекстного меню команду Tasks --> Import Data.

   

2. На экране появится диалоговое окно, в котором нужно выбрать источник данных DataSource из выпадающего списка. Выберите вариант Flat File Source и нажмите кнопку Next. На следующем экране укажите путь до файла CSV на жестком диске. Для нашего тестового файла нужно снять галочку «Column names in the first data row», т.к. в файле не содержатся заголовки столбцов в первой строке.

   

3. После этого SQL Server Management Studio загрузит файл и отобразит данные в таблице:

   

4. Обратите внимание, что программа автоматически определит разделитель между столбцами и строками в CSV-файле. Вы также можете настроить параметры столбцов (по умолчанию SSMS задаст столбцы в формате NVARCHAR(50) с названиями Column 0, Column 1 и т.д.) Перейдите на вкладку Advanced в левом боковом меню:

   

5. Как видите я изменил названия столбцов. Если у вас есть строки длиной больше 50, попросите SQL Server проверить все столбцы в файле. Это можно сделать с помощью кнопки Suggest Types (Предложить типы). SQL Server проанализирует первые 100 строк в файле и укажет предлагаемые типы для каждого столбца. В процессе проверки указывается ошибка. В зависимости от размера файла вы можете выбрать вариант, чтобы просмотреть весь файл или просто выбранные поля.

   

6. На следующем экране вы можете выбрать таблицу, в которую нужно импортировать данные. Здесь можно выбрать одну из существующих таблиц в базе данных, либо таблицу dbo.report, которую SSMS сгенерирует автоматически. Я рекомендую использовать второй вариант, а потом вручную преобразовать типы данных в основную таблицу.

   

7. После всех выполненных шагов SQL Server Management Studio отобразит процесс выполнения, сгенерирует таблицу report и заполнит ее данными:

   

На следующем рисунке отображена сгенерированная таблица report в окне Object Explorer:

Теперь выполните следующую инструкцию:

INSERT INTO CSV_Export
SELECT CONVERT(datetime, DateReport, 105), -- Строку в дату
	CountView, CountClicks, CTR, RPM, Profit
FROM report;

select top 100 * from CSV_Export;

В результате мы получим результаты, аналогичные подходу с использованием кода.