Это сообщение будет посвящено экспорту данных из файла Word средствами SQL Server-а. Мы напишем хранимую процедуру, которая будет получать в качестве параметра полное имя doc-файла и номер таблицы в нем. Предполагается что таблицы в документе нумеруются с 1 в том порядке, в каком они появляются в файле. Процедура должна будет вернуть содержимое таблицы документа в виде таблицы сервера.
С помощью такой процедуры можно, например возпользоваться конструкцией insert into ... exec ... для вставки содержимого Word-таблицы в таблицу SQL-сервера. Предполагается, что на серверном компьютере установлен Word, и для работы с Word-ом будут использоваться COM-объекты.
Для работы с объектами будут использоваться процедуры sp_OA... Конечно, более эффективным и безопасным решением могло бы стать использование clr-сборок. Да и для работы с word-ом могут быть библиотеки, работающие напрямую с файлом без OLE. Я этого не проверял, и решил воспользоваться процедурами OLE из любви к экзотическим задачам. К тому же решение может пригодиться для сопровождения унаследованных серверов, версии которых не позволяют использовать более современные средства.
Перед написанием кода процедуры сделаем несколько предварительных замечаний. Когда процедуры sp_OACreate или sp_OAMethod создают объект, то в конце он обязательно должен быть уничтожен с помощью вызова процедуры sp_OADestroy. К тому же понадобится открывать Word-файл. В конце файл обязательно надо будет закрыть, вызывая метод Close. Также необходимо будет вызвать метод Quit для приложения Word, чтобы в процессах не остался висеть процесс WINWORD. В связи с этим, на случай ошибок в работе процедуры, весь код по работе с объектами будет помещен в блок try блока try/catch. А инструкции для закрытия приложений и уничтожения объектов будут располагаться после блока catch, чтобы они были гарантированно вызваны. Это сделает код более безопасным.
Есть еще одно важное замечание, связанное с доступами. Для того, чтобы можно было работать с документами на стороне сервера, на диске, на котором установлена операционная система, должна существовать специальная директория. Для 64-х битных операционных систем это директория C:\Windows\SysWOW64\config\systemprofile\Desktop. Для 32-х битных это директория C:\Windows\System32\config\systemprofile\Desktop. По умолчапнию, в новых версия Windows, этой директории может не быть. В этом случае ее надо создать и предоставить права на работу с ней учетной записи, под которой работает служба SQL Server-а. Если этого не сделать, то применение метода Open будет завершаться ошибкой, поскольку сервер не будет видеть файл, открываемый с помощью sp_OAMethod.
Теперь можно переходить в коду. Первая часть кода стандартна для таких задач. Сперва запускается Word, затем открывается файл, в фале находится коллекция таблиц. После чего создается объект таблица (она отыскивается по номеру элемента в коллекции). Затем с помощью свойств Rows.Count, Columns.Count можно получить число строк и столбцов таблицы. В упрощенном виде код выглядит так:
set @oper = N'создание объекта'
exec @res = sp_OACreate N'Word.Application', @App out
set @han = @App if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'открытие файла'
exec @res = sp_OAMethod @App, 'Application.Documents.Open', @Doc out, @DocName
set @han = @App if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'коллекция таблиц'
exec @res = sp_OAMethod @Doc, N'Tables', @TabCol out
set @han = @Doc if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'таблица'
exec @res = sp_OAMethod @TabCol, N'Item', @Tab out, @TabNum
set @han = @TabCol if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'строки'
exec @res = sp_OAGetProperty @Tab, N'Rows.Count', @Rows out
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'столбцы'
exec @res = sp_OAGetProperty @Tab, N'Columns.Count', @Cols out
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
Эта часть кода, которая будет идти в самом начале блока try. Далее возникла мысль о том, что можно идти в цикле по строкам и ячейкам таблицы, складывая значения ячеек во временную таблицу. Например так:
while @curRow <= @Rows
begin
set @curCol = 1
while @curCol <= @Cols
begin
set @cmd = N'Cell(' + cast ( @curRow as varchar ( 10 ) ) + ',' + cast ( @curCol as varchar ( 10 ) ) + ').range.Text'
exec @res = sp_OAGetProperty @Tab, @cmd, @CurCell out
if @res <> 0 raiserror ( @oper, 16, 1 )
set @CurCell = rtrim ( ltrim ( @CurCell ) )
if unicode ( right ( @CurCell, 1 ) ) = 7
begin
set @CurCell = substring ( @CurCell, 1, len ( @CurCell ) - 1 )
end
if unicode ( right ( @CurCell, 1 ) ) = 13
begin
set @CurCell = substring ( @CurCell, 1, len ( @CurCell ) - 1 )
end
if @curCol = 1
begin
set @sql = N'insert into ' + @TabName + N' ( col1 ) select N''' + @CurCell
end
else
begin
set @sql = N'update ' + @TabName + N' set col' + cast ( @curCol as nvarchar ( 100 ) ) + ' = N''' + @CurCell + N'''
where iRowId = ' + cast ( @curRow as nvarchar ( 100 ) )
end
exec sp_executesql @sql
set @curCol += 1
end
set @curRow += 1
end
В этом этом коде предполагается, что предварительно создана временная таблица, структура которой совпадает с таблицей Word-а. Ее можно создать, используя динамичекий sql и информацию о числе столбцов в Word-таблице. В цикле, на каждой итерации, в таблицу происходит либо вставка, в случае, если обрабатывается новая строка таблицы. Либо идет обновление столбца одной строки таблицы, если работает внутренний цикл, который считывает столбцы текущей строки таблицы документа.
Однако при использовании этого подхода возникла проблема. Оказалась, что на больших объемах, когда в таблице тысячи или десятки тысяч строк, код работает чудовищно долго. Можно было бы грешить на то, что на каждой итерации работает логируемая операция insert или update, для которой к тому же, возможно, всякий раз ищется план выполнения из-за использования Ad Hoc-запросов. Но профайлер показал, что это время является мизерным по сравнению с общим. Основное время затрачивается на считывание очередной ячейки. Потребовалось изменить подход. В объектной модели Word-а я нашел метод ConvertToText. Он оказался исключительно полезным. Метод выполняет конкатенацию всех строк и столбцов таблицы документа, возвращая результирующую строку. В качестве разделителя строк используется новая строка. А разделитель столбцов можно задать в параметре метода (по умолчанию символ тире ("-")). Этот метод работает радикально быстрее чем цикл по всем ячейкам.
Далее встает вопрос. Результат такой конкатенации будет возвращен серверу. Что же с ним делать для того, чтобы быстро распарсить и загрузить в таблицу?
Первое, что потребуется сделать, это создать временную таблицу с одним столбцом типа nvarchar ( max ), и положить в нее строку. Далее с помощью программы bcp можно скопировать содержимое таблицы в текстовый файл. Операция нелогируемая и очень быстрая. Далее, используя информацию о разделителях строк и столбцов, можно выполнить загрузку файла в таблицу с помощью инструкции bulk insert. Инструкция также нелогируемая и работает чрезвычайно быстро.
По алгоритму вроде бы все. То есть почти все. После применения метода ConvertToText с помощью sp_OAMethod мы получаем объект типа Range. Для получения результата конкатенации требуется использовать метод Text через вызов процедуры sp_OAMethod с созданным объектом типа Range в качестве параметра. Метод имеет выходной параметр, который надо поймать в третьем параметре процедуры sp_OAMethod. Это делается так:
exec @res = sp_OAMethod @Tab, N'ConvertToText', @ran out, ';'
exec @res = sp_OAMethod @ran, N'Text', @txt out
Однако у процедуры sp_OAMethod есть одна не очень приятная особенность. Если длина выходной строки будет больше 8000 символов, то прочитать значение не получится. В старых версия SQL Server-а просто не было типов данных varchar ( max ) / nvarchar ( max ), и поэтому третий параметр не может принадлежать таким типам. Выход такой. Требуется вызвать sp_OAMethod, указывая, что выходной параметр не нужен. То есть указать в третьем параметре null. Тогда вызов процедуры сам вернет результат метода, и его можно сохранить используя конструкцию insert into ... exec ... Пример:
Ниже представлен общий код процедуры. После него будут даны комментарии к коду и результаты сравнительного тестирования различных методов на скорость обработки больших таблиц.
if sessionproperty ( N'quoted_identifier' ) = 0
set quoted_identifier on
go
if sessionproperty ( N'ansi_nulls' ) = 0
set ansi_nulls on
go
if object_id ( N'dbo.ExportWordTableUsingConvertToTxt', N'P' ) is null
exec ( N'create proc dbo.ExportWordTableUsingConvertToTxt as return 1' )
go
alter proc dbo.ExportWordTableUsingConvertToTxt
(
@DocName nvarchar ( 300 ),
@TabNum int = 1
)
as
begin
set nocount, xact_abort on
@sql nvarchar ( max ), @TabName nvarchar ( 100 ), @CurColName int, @txt nvarchar ( max ), @ran int,
@FileName varchar ( 255 ) = 'C:\Users\edynak\Desktop\tmp.txt'
begin try
set @oper = N'создание объекта'
exec @res = sp_OACreate N'Word.Application', @App out
set @han = @App if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'открытие файла'
exec @res = sp_OAMethod @App, 'Application.Documents.Open', @Doc out, @DocName
set @han = @App if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'коллекция таблиц'
exec @res = sp_OAMethod @Doc, N'Tables', @TabCol out
set @han = @Doc if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'таблица'
exec @res = sp_OAMethod @TabCol, N'Item', @Tab out, @TabNum
set @han = @TabCol if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'строки'
exec @res = sp_OAGetProperty @Tab, N'Rows.Count', @Rows out
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'столбцы'
exec @res = sp_OAGetProperty @Tab, N'Columns.Count', @Cols out
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'конвертация таблицы'
exec @res = sp_OAMethod @Tab, N'ConvertToText', @ran out, ';'
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'тест таблицы'
if object_id ( N'tempdb..#tmp', N'U' ) is not null
drop table #tmp
create table #tmp ( val varchar ( max ) null )
insert into #tmp ( val )
exec @res = sp_OAMethod @ran, N'Text', null
set @han = @ran if @res <> 0 raiserror ( @oper, 16, 1 )
select top 1 @txt = val
from #tmp
set @oper = N'ячейка'
set @curCol = 1
set @curRow = 1
set @CurColName = 1
set @sql = N''
set @sql = @sql + case when @CurColName = 1 then N'' else N', ' end +
N'col' + cast ( @CurColName as nvarchar ( 100 ) ) + N' nvarchar ( 100 ) null'
set @CurColName += 1
end
if object_id ( N''tempdb..' + @TabName + ''', N''U'' ) is not null
drop table ' + @TabName + '
create table ' + @TabName + '
(
' + @sql + '
) on [PRIMARY]
'
exec sp_executesql @sql
if object_id ( N''tempdb..' + @TmpGlobTab + ''', N''U'' ) is not null
drop table ' + @TmpGlobTab + '
create table ' + @TmpGlobTab + ' ( val varchar ( max ) null )
'
exec sp_executesql @sql
select replace ( val, nchar ( 13 ), N''+'' )
from #tmp
update ' + @TmpGlobTab + '
set val = left ( val, len ( val ) - 1 )
'
exec sp_executesql @sql
from ''' + @FileName + '''
with
(
firstrow = 1,
codepage = 1251,
datafiletype = N''char'',
batchsize = 5000000,
rowterminator = N''+'',
fieldterminator = N'';''
)
'
exec sp_executesql @sql
from ' + @TabName + '
'
exec sp_executesql @sql
end try
begin catch
if error_message () <> @oper
begin
set @ErrMsg = N'Описание ошибки: ' + error_message () + N'; номер: ' + cast ( error_number () as nvarchar ( 100 ) ) + N'; строка: ' +
cast ( error_line () as nvarchar ( 100 ) )
end
else
begin
exec sp_OAGetErrorInfo @han, @src out, @desc out
set @ErrMsg = N'Тип ошибки: ' + @oper + N'; код: ' + cast ( @res as nvarchar ( 100 ) ) + '; источник: ' + isnull ( @src, N'неизвестен' ) +
'; описание: ' + isnull ( @desc, N'неизвестен' )
end
end catch
exec sp_OAMethod @App, 'Quit', null
exec sp_OADestroy @ran
exec sp_OADestroy @Tab
exec sp_OADestroy @TabCol
exec sp_OADestroy @Doc
exec sp_OADestroy @App
set @sql = N'
if object_id ( N''tempdb..' + @TabName + ''', N''U'' ) is not null
drop table ' + @TabName + '
'
exec sp_executesql @sql
drop table ' + @TmpGlobTab + '
'
exec sp_executesql @sql
raiserror ( @ErrMsg, 16, 1 )
end
end
go
В коде выше запускается Word, открывается файл. После этого код получает возможность работы с таблицей. К ней применяется метод ConvertToText, результат которого записывается во временную таблицу #tmp. Такая таблица локальна и не видна другим соединениям. Поэтому далее создается глобальная временная таблица, в которую копируется содержимое #tmp. Промежуточная таблица #tmp, используется ввиду желания не задавать в коде имени глобальной временной таблицы, из-за чего с последней можно работать только с помощью динамического sql. На всякий случай, символы новой строки заменяются на символ +, на случай если пустые строки пропадут при работе с bcp. Также в самом конце строки есть символ новой строки, который преобразован в символ +. При bulk insert сервер подумал бы, что после этого последнего символа не хватает строки, из-за чего bulk insert выдал бы ошибку. Поэтому в update-е глобальной временной таблицы последний символ удаляется из строки. После копирования содержимого таблицы в текстовый файл, работает bulk insert, который использует информацию о разделителях столбцов и строк: символы плюса ("+") и точки с запятой (";").
В блоке catch логируется ошибка. Она может быть связана с кодом процедуры или с работой объектов. В зависимости от этого либо вызывается sp_OAGetErrorInfo либо используются стандартные функции логирования ошибки.
После блока catch производится сборка мусора. Обратите внимание, что метод Close для документа обязательно вызывается с паратром 0. Это гарантирует, что изменения внесенные кодом процедуры в документ не сохранятся. Это важно, ведь метод ConvertToText конвертирует таблицу в самом файле. Также с помощью процедуры sp_OADestroy уничтожаются объекты. Производится удаление созданных глобальных временных таблиц и на всякий случай удаляется созданный текстовый файл.
Результаты по скорости такие. Тестирование проводил на компьютере с ОС Win7 x64 (4 Gb оперативной памяти, один 4-х ядерный процессор). Сделал несколько Word-файлов, содержащих по таблице с 4-я столбцами. Все поля заполняются одинаковыми значениями.
В 1-ом файле в таблице 1000 строк. Процедура, выполяющая конвертацию таблицы в текст отработала за 1 сек. Процедура, делающая цикл по ячейкам работала 30 сек.
Для файла с таблицей, содержащей 10 тыс. строк конвертация отработала за 45 сек., с циклом по ячейкам время работы составило уже 22 мин. 10 сек.
Для файла с 50-ю тысячами строк процедура с конвератцией таблицы в текст работала 28 мин. 27 сек., процедура с циклом по ячейкам - 8 час. 23 мин. 29 сек..
И наконец, word-таблица со 100 тысячами строк импортировалась через конверацию 2 часа 36 мин. 21 сек. Процедура с циклом по ячейкам работала несколько суток, после чего соединения отключилось.
С помощью такой процедуры можно, например возпользоваться конструкцией insert into ... exec ... для вставки содержимого Word-таблицы в таблицу SQL-сервера. Предполагается, что на серверном компьютере установлен Word, и для работы с Word-ом будут использоваться COM-объекты.
Для работы с объектами будут использоваться процедуры sp_OA... Конечно, более эффективным и безопасным решением могло бы стать использование clr-сборок. Да и для работы с word-ом могут быть библиотеки, работающие напрямую с файлом без OLE. Я этого не проверял, и решил воспользоваться процедурами OLE из любви к экзотическим задачам. К тому же решение может пригодиться для сопровождения унаследованных серверов, версии которых не позволяют использовать более современные средства.
Перед написанием кода процедуры сделаем несколько предварительных замечаний. Когда процедуры sp_OACreate или sp_OAMethod создают объект, то в конце он обязательно должен быть уничтожен с помощью вызова процедуры sp_OADestroy. К тому же понадобится открывать Word-файл. В конце файл обязательно надо будет закрыть, вызывая метод Close. Также необходимо будет вызвать метод Quit для приложения Word, чтобы в процессах не остался висеть процесс WINWORD. В связи с этим, на случай ошибок в работе процедуры, весь код по работе с объектами будет помещен в блок try блока try/catch. А инструкции для закрытия приложений и уничтожения объектов будут располагаться после блока catch, чтобы они были гарантированно вызваны. Это сделает код более безопасным.
Есть еще одно важное замечание, связанное с доступами. Для того, чтобы можно было работать с документами на стороне сервера, на диске, на котором установлена операционная система, должна существовать специальная директория. Для 64-х битных операционных систем это директория C:\Windows\SysWOW64\config\systemprofile\Desktop. Для 32-х битных это директория C:\Windows\System32\config\systemprofile\Desktop. По умолчапнию, в новых версия Windows, этой директории может не быть. В этом случае ее надо создать и предоставить права на работу с ней учетной записи, под которой работает служба SQL Server-а. Если этого не сделать, то применение метода Open будет завершаться ошибкой, поскольку сервер не будет видеть файл, открываемый с помощью sp_OAMethod.
Теперь можно переходить в коду. Первая часть кода стандартна для таких задач. Сперва запускается Word, затем открывается файл, в фале находится коллекция таблиц. После чего создается объект таблица (она отыскивается по номеру элемента в коллекции). Затем с помощью свойств Rows.Count, Columns.Count можно получить число строк и столбцов таблицы. В упрощенном виде код выглядит так:
set @oper = N'создание объекта'
exec @res = sp_OACreate N'Word.Application', @App out
set @han = @App if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'открытие файла'
exec @res = sp_OAMethod @App, 'Application.Documents.Open', @Doc out, @DocName
set @han = @App if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'коллекция таблиц'
exec @res = sp_OAMethod @Doc, N'Tables', @TabCol out
set @han = @Doc if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'таблица'
exec @res = sp_OAMethod @TabCol, N'Item', @Tab out, @TabNum
set @han = @TabCol if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'строки'
exec @res = sp_OAGetProperty @Tab, N'Rows.Count', @Rows out
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'столбцы'
exec @res = sp_OAGetProperty @Tab, N'Columns.Count', @Cols out
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
Эта часть кода, которая будет идти в самом начале блока try. Далее возникла мысль о том, что можно идти в цикле по строкам и ячейкам таблицы, складывая значения ячеек во временную таблицу. Например так:
while @curRow <= @Rows
begin
set @curCol = 1
while @curCol <= @Cols
begin
set @cmd = N'Cell(' + cast ( @curRow as varchar ( 10 ) ) + ',' + cast ( @curCol as varchar ( 10 ) ) + ').range.Text'
exec @res = sp_OAGetProperty @Tab, @cmd, @CurCell out
if @res <> 0 raiserror ( @oper, 16, 1 )
set @CurCell = rtrim ( ltrim ( @CurCell ) )
if unicode ( right ( @CurCell, 1 ) ) = 7
begin
set @CurCell = substring ( @CurCell, 1, len ( @CurCell ) - 1 )
end
if unicode ( right ( @CurCell, 1 ) ) = 13
begin
set @CurCell = substring ( @CurCell, 1, len ( @CurCell ) - 1 )
end
if @curCol = 1
begin
set @sql = N'insert into ' + @TabName + N' ( col1 ) select N''' + @CurCell
end
else
begin
set @sql = N'update ' + @TabName + N' set col' + cast ( @curCol as nvarchar ( 100 ) ) + ' = N''' + @CurCell + N'''
where iRowId = ' + cast ( @curRow as nvarchar ( 100 ) )
end
exec sp_executesql @sql
set @curCol += 1
end
set @curRow += 1
end
В этом этом коде предполагается, что предварительно создана временная таблица, структура которой совпадает с таблицей Word-а. Ее можно создать, используя динамичекий sql и информацию о числе столбцов в Word-таблице. В цикле, на каждой итерации, в таблицу происходит либо вставка, в случае, если обрабатывается новая строка таблицы. Либо идет обновление столбца одной строки таблицы, если работает внутренний цикл, который считывает столбцы текущей строки таблицы документа.
Однако при использовании этого подхода возникла проблема. Оказалась, что на больших объемах, когда в таблице тысячи или десятки тысяч строк, код работает чудовищно долго. Можно было бы грешить на то, что на каждой итерации работает логируемая операция insert или update, для которой к тому же, возможно, всякий раз ищется план выполнения из-за использования Ad Hoc-запросов. Но профайлер показал, что это время является мизерным по сравнению с общим. Основное время затрачивается на считывание очередной ячейки. Потребовалось изменить подход. В объектной модели Word-а я нашел метод ConvertToText. Он оказался исключительно полезным. Метод выполняет конкатенацию всех строк и столбцов таблицы документа, возвращая результирующую строку. В качестве разделителя строк используется новая строка. А разделитель столбцов можно задать в параметре метода (по умолчанию символ тире ("-")). Этот метод работает радикально быстрее чем цикл по всем ячейкам.
Далее встает вопрос. Результат такой конкатенации будет возвращен серверу. Что же с ним делать для того, чтобы быстро распарсить и загрузить в таблицу?
Первое, что потребуется сделать, это создать временную таблицу с одним столбцом типа nvarchar ( max ), и положить в нее строку. Далее с помощью программы bcp можно скопировать содержимое таблицы в текстовый файл. Операция нелогируемая и очень быстрая. Далее, используя информацию о разделителях строк и столбцов, можно выполнить загрузку файла в таблицу с помощью инструкции bulk insert. Инструкция также нелогируемая и работает чрезвычайно быстро.
По алгоритму вроде бы все. То есть почти все. После применения метода ConvertToText с помощью sp_OAMethod мы получаем объект типа Range. Для получения результата конкатенации требуется использовать метод Text через вызов процедуры sp_OAMethod с созданным объектом типа Range в качестве параметра. Метод имеет выходной параметр, который надо поймать в третьем параметре процедуры sp_OAMethod. Это делается так:
exec @res = sp_OAMethod @Tab, N'ConvertToText', @ran out, ';'
exec @res = sp_OAMethod @ran, N'Text', @txt out
Однако у процедуры sp_OAMethod есть одна не очень приятная особенность. Если длина выходной строки будет больше 8000 символов, то прочитать значение не получится. В старых версия SQL Server-а просто не было типов данных varchar ( max ) / nvarchar ( max ), и поэтому третий параметр не может принадлежать таким типам. Выход такой. Требуется вызвать sp_OAMethod, указывая, что выходной параметр не нужен. То есть указать в третьем параметре null. Тогда вызов процедуры сам вернет результат метода, и его можно сохранить используя конструкцию insert into ... exec ... Пример:
insert into #tmp ( val )
exec @res = sp_OAMethod @ran, N'Text', null
Ниже представлен общий код процедуры. После него будут даны комментарии к коду и результаты сравнительного тестирования различных методов на скорость обработки больших таблиц.
if sessionproperty ( N'quoted_identifier' ) = 0
set quoted_identifier on
go
if sessionproperty ( N'ansi_nulls' ) = 0
set ansi_nulls on
go
if object_id ( N'dbo.ExportWordTableUsingConvertToTxt', N'P' ) is null
exec ( N'create proc dbo.ExportWordTableUsingConvertToTxt as return 1' )
go
alter proc dbo.ExportWordTableUsingConvertToTxt
(
@DocName nvarchar ( 300 ),
@TabNum int = 1
)
as
begin
set nocount, xact_abort on
declare @App int, @Doc int, @res int, @src nvarchar ( 300 ), @desc nvarchar ( 300 ), @oper nvarchar ( 100 ), @ErrMsg nvarchar ( max ),
@han
int, @TabCol int, @Tab int, @Rows int, @Cols int, @CurCell varchar ( 100 ), @curCol int, @curRow int, @cmd varchar ( 8000 ),@sql nvarchar ( max ), @TabName nvarchar ( 100 ), @CurColName int, @txt nvarchar ( max ), @ran int,
@FileName varchar ( 255 ) = 'C:\Users\edynak\Desktop\tmp.txt'
begin try
set @oper = N'создание объекта'
exec @res = sp_OACreate N'Word.Application', @App out
set @han = @App if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'открытие файла'
exec @res = sp_OAMethod @App, 'Application.Documents.Open', @Doc out, @DocName
set @han = @App if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'коллекция таблиц'
exec @res = sp_OAMethod @Doc, N'Tables', @TabCol out
set @han = @Doc if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'таблица'
exec @res = sp_OAMethod @TabCol, N'Item', @Tab out, @TabNum
set @han = @TabCol if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'строки'
exec @res = sp_OAGetProperty @Tab, N'Rows.Count', @Rows out
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'столбцы'
exec @res = sp_OAGetProperty @Tab, N'Columns.Count', @Cols out
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'конвертация таблицы'
exec @res = sp_OAMethod @Tab, N'ConvertToText', @ran out, ';'
set @han = @Tab if @res <> 0 raiserror ( @oper, 16, 1 )
set @oper = N'тест таблицы'
if object_id ( N'tempdb..#tmp', N'U' ) is not null
drop table #tmp
create table #tmp ( val varchar ( max ) null )
insert into #tmp ( val )
exec @res = sp_OAMethod @ran, N'Text', null
set @han = @ran if @res <> 0 raiserror ( @oper, 16, 1 )
select top 1 @txt = val
from #tmp
set @oper = N'ячейка'
set @curCol = 1
set @curRow = 1
set @CurColName = 1
set @sql = N''
while
@CurColName <=
@Cols
beginset @sql = @sql + case when @CurColName = 1 then N'' else N', ' end +
N'col' + cast ( @CurColName as nvarchar ( 100 ) ) + N' nvarchar ( 100 ) null'
set @CurColName += 1
end
set
@TabName = quotename ( N'##' + cast ( newid () as nvarchar ( 100 ) ) )
set
@sql = N'if object_id ( N''tempdb..' + @TabName + ''', N''U'' ) is not null
drop table ' + @TabName + '
create table ' + @TabName + '
(
' + @sql + '
) on [PRIMARY]
'
exec sp_executesql @sql
declare
@TmpGlobTab nvarchar ( max ) = quotename ( N'##' + cast ( newid () as nvarchar ( 100 ) ) )
set
@sql = N'if object_id ( N''tempdb..' + @TmpGlobTab + ''', N''U'' ) is not null
drop table ' + @TmpGlobTab + '
create table ' + @TmpGlobTab + ' ( val varchar ( max ) null )
'
exec sp_executesql @sql
set
@sql = N'
insert into '
+ @TmpGlobTab + ' ( val )select replace ( val, nchar ( 13 ), N''+'' )
from #tmp
update ' + @TmpGlobTab + '
set val = left ( val, len ( val ) - 1 )
'
exec sp_executesql @sql
set
@cmd = 'bcp "select * from ' +
@TmpGlobTab + '" queryout ' + @FileName + ' -S' + @@servername + ' -T -c -C 1251 -r \n'
exec
xp_cmdshell @cmd, no_output
set
@sql = N'
bulk insert '
+ @TabName + 'from ''' + @FileName + '''
with
(
firstrow = 1,
codepage = 1251,
datafiletype = N''char'',
batchsize = 5000000,
rowterminator = N''+'',
fieldterminator = N'';''
)
'
exec sp_executesql @sql
set
@sql = N'
select *from ' + @TabName + '
'
exec sp_executesql @sql
end try
begin catch
if error_message () <> @oper
begin
set @ErrMsg = N'Описание ошибки: ' + error_message () + N'; номер: ' + cast ( error_number () as nvarchar ( 100 ) ) + N'; строка: ' +
cast ( error_line () as nvarchar ( 100 ) )
end
else
begin
exec sp_OAGetErrorInfo @han, @src out, @desc out
set @ErrMsg = N'Тип ошибки: ' + @oper + N'; код: ' + cast ( @res as nvarchar ( 100 ) ) + '; источник: ' + isnull ( @src, N'неизвестен' ) +
'; описание: ' + isnull ( @desc, N'неизвестен' )
end
end catch
-- закрытие объектов, сборка мусора
exec sp_OAMethod @Doc, 'Close', null, 0exec sp_OAMethod @App, 'Quit', null
exec sp_OADestroy @ran
exec sp_OADestroy @Tab
exec sp_OADestroy @TabCol
exec sp_OADestroy @Doc
exec sp_OADestroy @App
set @sql = N'
if object_id ( N''tempdb..' + @TabName + ''', N''U'' ) is not null
drop table ' + @TabName + '
'
exec sp_executesql @sql
set @sql = N'
if
object_id ( N''tempdb..'
+ @TmpGlobTab + ''', N''U'' ) is not nulldrop table ' + @TmpGlobTab + '
'
exec sp_executesql @sql
set @cmd = 'del ' + @FileName
exec xp_cmdshell @cmd, no_output
if isnull ( @ErrMsg, N'' ) <> N''
beginraiserror ( @ErrMsg, 16, 1 )
end
end
go
В коде выше запускается Word, открывается файл. После этого код получает возможность работы с таблицей. К ней применяется метод ConvertToText, результат которого записывается во временную таблицу #tmp. Такая таблица локальна и не видна другим соединениям. Поэтому далее создается глобальная временная таблица, в которую копируется содержимое #tmp. Промежуточная таблица #tmp, используется ввиду желания не задавать в коде имени глобальной временной таблицы, из-за чего с последней можно работать только с помощью динамического sql. На всякий случай, символы новой строки заменяются на символ +, на случай если пустые строки пропадут при работе с bcp. Также в самом конце строки есть символ новой строки, который преобразован в символ +. При bulk insert сервер подумал бы, что после этого последнего символа не хватает строки, из-за чего bulk insert выдал бы ошибку. Поэтому в update-е глобальной временной таблицы последний символ удаляется из строки. После копирования содержимого таблицы в текстовый файл, работает bulk insert, который использует информацию о разделителях столбцов и строк: символы плюса ("+") и точки с запятой (";").
В блоке catch логируется ошибка. Она может быть связана с кодом процедуры или с работой объектов. В зависимости от этого либо вызывается sp_OAGetErrorInfo либо используются стандартные функции логирования ошибки.
После блока catch производится сборка мусора. Обратите внимание, что метод Close для документа обязательно вызывается с паратром 0. Это гарантирует, что изменения внесенные кодом процедуры в документ не сохранятся. Это важно, ведь метод ConvertToText конвертирует таблицу в самом файле. Также с помощью процедуры sp_OADestroy уничтожаются объекты. Производится удаление созданных глобальных временных таблиц и на всякий случай удаляется созданный текстовый файл.
Результаты по скорости такие. Тестирование проводил на компьютере с ОС Win7 x64 (4 Gb оперативной памяти, один 4-х ядерный процессор). Сделал несколько Word-файлов, содержащих по таблице с 4-я столбцами. Все поля заполняются одинаковыми значениями.
В 1-ом файле в таблице 1000 строк. Процедура, выполяющая конвертацию таблицы в текст отработала за 1 сек. Процедура, делающая цикл по ячейкам работала 30 сек.
Для файла с таблицей, содержащей 10 тыс. строк конвертация отработала за 45 сек., с циклом по ячейкам время работы составило уже 22 мин. 10 сек.
Для файла с 50-ю тысячами строк процедура с конвератцией таблицы в текст работала 28 мин. 27 сек., процедура с циклом по ячейкам - 8 час. 23 мин. 29 сек..
И наконец, word-таблица со 100 тысячами строк импортировалась через конверацию 2 часа 36 мин. 21 сек. Процедура с циклом по ячейкам работала несколько суток, после чего соединения отключилось.
