Блог

Я программно экспортирую данные из Excel в SQL Server 2005 с помощью SqlBulkCopy. Это отлично работает, единственная проблема, с которой я сталкиваюсь, заключается в том, что он не сохраняет последовательность строк, которая у меня есть в файле Excel. У меня нет столбца для упорядочения, я просто хочу, чтобы записи вставлялись в том же порядке, в каком они отображаются в электронной таблице Excel.

Я не могу изменить файл Excel, и мне приходится работать с тем, что у меня есть. Сортировка по любому из существующих столбцов нарушит последовательность.

Пожалуйста, помогите.

P.S. В итоге я вставил столбец идентификатора в электронную таблицу, похоже, что нет способа сохранить порядок во время экспорта/импорта

Я не думаю, что порядок строк задан или гарантирован SQL, если вы не используете предложение «УПОРЯДОЧИТЬ ПО».

Из поста Билла Вона (http://betav.com/blog/billva/2008/08/sql_server_indexing_tips_and_t.html):

Использование Order By: Даже если таблица имеет кластеризованный индекс (в котором данные хранятся в физическом порядке), SQL Server не гарантирует, что строки будут возвращены в этом (или любом конкретном) порядке, если не используется предложение ORDER BY.

Еще одна ссылка с информацией:

http://sqlblogcasts.com/blogs/simons/archive/2007/08/21/What-is-the-position-of-a-row—.aspx

После долгих исследований кажется очевидным, что невозможно сохранить порядок строк с помощью команды Bulk Insert, написанной так, как она представлена Microsoft. Вам нужно либо самостоятельно добавить столбец идентификатора непосредственно в файл импорта, использовать оболочку или другой внешний скрипт, либо обойтись без этого. Кажется, что это была бы необходимая (и простая) функция для добавления Microsoft, но после более чем десятилетнего отсутствия от них, этого не произойдет.

Тем не менее, мне нужно было сохранить фактический порядок записей в файле импорта после импорта, так как более высокие записи заменяли бы более низкие, если бы столбец набора имел то же значение.

Поэтому я пошел другим путем. Мои ограничения были:

• Я вообще не мог изменить исходный файл. (и создал плохой прецедент!)
• Я не мог использовать внешний скрипт. Слишком сложно. Это должно было быть простое решение на основе T-Sql, без выполнения команд. Это должно было войти в единую процедуру, чтобы ее можно было автоматизировать.

Мне понравилась логика использования Powershell для создания упорядоченных операторов вставки для каждой строки, а затем запуска в Sql. По сути, это была очередь каждой записи для отдельной вставки, а не для МАССОВОЙ вставки. Да, это сработало бы, но также было бы очень медленно. У меня часто есть файлы с более чем 500 тысячами строк в них. Мне нужно было что-то БЫСТРО.

Поэтому я наткнулся на XML. Массовая загрузка файла непосредственно в одну переменную XML. Это позволит сохранить порядок записей по мере добавления каждой из них в XML. Затем проанализируйте переменную XML и вставьте результаты в таблицу, одновременно добавив столбец идентификаторов.

Предполагается, что файл импорта является стандартным текстовым файлом, каждая запись которого заканчивается строкой (Символ(13) Символ(10))

Мой подход состоит из 2 шагов:

1. Выполните инструкцию IMPORT SQL (используя OPENROWSET), инкапсулируя каждую запись XML-тегами. Запишите результаты в переменную XML.
2. Проанализируйте переменную по тегам XML в таблицу, добавив увеличивающийся столбец [ID].

    ---------------------------------
   Declare @X xml;
   ---------------------------------
   SELECT @X=Cast('<X>' Replace([BulkColumn],Char(13) Char(10),'</X><X>') '</X>' as XML)
   FROM OPENROWSET (BULK N'\FileServerImportFolderImportFile_20170120.csv',SINGLE_CLOB) T
   ---------------------------------
   SELECT [Record].[X].query('.').value('.','varchar(max)') [Record]
   ,ROW_NUMBER() OVER (ORDER BY (SELECT 100)) [ID]
   --Into #TEMP 
   FROM @X.nodes('X') [Record](X);
   ---------------------------------
    

   • Теги XML заменяют каждую строку.
   • Если файл заканчивается строкой, в конце будет добавлена пустая строка. Просто удалите последнюю строку.

Я записал это в свою процедуру с помощью динамического sql, чтобы я мог передать имя файла и установить идентификатор, начинающийся с 1 или 0 (в случае, если есть строка заголовка).

Я смог запустить это с файлом из 300 тысяч записей примерно за 5 секунд.

Вы также можете определить столбец идентификаторов в своей таблице, который автоматически увеличивается во время загрузки данных. Таким образом, вы сможете отсортировать его позже, когда захотите, чтобы записи снова были в том же порядке.

Если вы можете сохранить электронную таблицу Excel в формате CSV, очень легко сгенерировать список операторов ВСТАВКИ с любым языком сценариев, которые будут выполняться в том же порядке, что и электронная таблица. Вот краткий пример в Groovy, но любой язык сценариев сделает это так же легко, если не проще:

 def file1 = new File('c:\temp\yourSpreadsheet.csv')
def file2 = new File('c:\temp\yourInsertScript.sql')

def reader = new FileReader(file1)
def writer = new FileWriter(file2)

reader.transformLine(writer) { line ->
    fields =  line.split(',')

    text = """INSERT INTO table1 (col1, col2, col3) VALUES ('${fields[0]}', '${fields[1]}', '${fields[2]}');"""

}
 

Затем вы можете выполнить свой «yourInsertScript.sql» в своей базе данных, и ваш заказ будет таким же, как в вашей электронной таблице.