Планирование импорта плоских файлов с разными именами в SQL Server 2014

Question

Поскольку я новичок в SQL Server, и мои сценарии еще не очень хорошо отработаны. Мне нужны предложения по вопросу ниже.

Я получаю файлы с удаленного сервера на мою машину (около 700 в день) следующим образом:

ABCD.100.1601310200
ABCD.101.1601310210
ABCD.102.1601310215

Соглашение об именах: Здесь первая часть "ABCD" остается прежней, средняя часть - это идентификатор последовательности, который находится в инкрементном порядке для каждого файла. Последняя часть - это отметка времени.

Структура файла Файл не имеет определенного расширения, но может быть открыт с помощью блокнота / Excel. Поэтому может называться плоским файлом. Каждый файл состоит из 95 столбцов и 20000 строк с фиксированным значением мусора в верхних 4 и нижних 4 строках столбца 1.

Теперь мне нужно создать базу данных на сервере SQL, где я мог бы импортировать данные из этих плоских файлов с помощью планировщика. Предложение необходимо.

Answer 1

Возможно, есть и другие способы сделать это, но это один из способов:

1. Создайте файл формата для ваших таблиц. Вам нужно создать его только один раз. Используйте этот файл в скрипте импорта на шаге 2.
2. Создать скрипт импорта на основе OPENROWSET(BULK '<file_name>', FORMATFILE='<format_file>'
3. Запланируйте выполнение сценария из шага 2 в SQL Server для базы данных, в которую вы хотите импортировать данные

---

Создать файл формата

Это создает файл формата для использования на следующем шаге. Следующий скрипт создает файл формата в C:\Temp\imp.fmt на основе существующей таблицы (заменить TEST_TT с базой данных, в которую вы импортируете). Это создает такой файл формата с , как полевой сепаратор. Если файлы имеют вкладку в качестве разделителя, удалите -t, переключатель.

DECLARE @cmd VARCHAR(8000);
SET @cmd='BCP TEST_TT.dbo.[ABCD.100.1601310200] format nul -f "C:\Temp\imp.fmt" -c -t, -T -S ' + (SELECT @@SERVERNAME);
EXEC master..xp_cmdshell @cmd;

Перед выполнением этого вы должны перенастроить SQL Server, чтобы xp_cmdshell хранимая процедура. Вам нужно сделать это только один раз.

EXEC sp_configure 'show advanced options', 1 
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1 
GO
RECONFIGURE
GO

---

скрипт импорта

Этот скрипт предполагает:

• Файлы должны быть импортированы в отдельные таблицы
• Файлы находятся в C:\Temp
• Формат файла C:\Temp\imp.fmt (генерируется на предыдущем шаге)

---

SET NOCOUNT ON;
DECLARE @store_path VARCHAR(256)='C:\Temp';
DECLARE @files TABLE(fn NVARCHAR(256));
DECLARE @list_cmd VARCHAR(256)='DIR ' + @store_path + '\ABCD.* /B';
INSERT INTO @files EXEC master..xp_cmdshell @list_cmd;
DECLARE @fullcmd NVARCHAR(MAX);
SET @fullcmd=(
    SELECT
        'IF OBJECT_ID('''+QUOTENAME(fn)+''',''U'') IS NOT NULL DROP TABLE '+QUOTENAME(fn)+';'+
        'SELECT * INTO '+QUOTENAME(fn)+' '+
        'FROM OPENROWSET(BULK '''+@store_path+'\'+fn+''',FORMATFILE=''C:\Temp\imp.fmt'') AS tt;'
    FROM 
        @files 
    WHERE 
        fn IS NOT NULL
    FOR XML PATH('')
);
EXEC sp_executesql @fullcmd;