Импортируйте файл Excel в SQL Server построчно

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

Я запутался в том, как сделать это правильно. Я могу использовать SQLCommand с такими параметрами:

SqlCommand sqlCommand = new SqlCommand("insert into TestTable(GUID,Name,Pricing) values(@GUID,@Name,@Pricing)", sqlConn);
foreach (DataRow dr in ds.Tables[0].Rows) //<-- this is my Excel file to iterate through
{
 sqlCommander.Parameters.Clear();
 String refGUID = Guid.NewGuid().ToString();
 sqlCommander.Parameters.AddWithValue("GUID", refGUID);
 sqlCommander.Parameters.AddWithValue("Name", dr.ItemArray[0]);
 sqlCommander.Parameters.AddWithValue("Pricing", dr.ItemArray[1]);
 sqlCommander.ExecuteNonQuery();
}

Или я могу использовать "подключенный" режим так:

SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT GUID, Name, Pricing FROM TestTable", sqlConn);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

DataSet myDataSet = new DataSet();
dataAdapter.Fill(myDataSet, "TestTable");
foreach (DataRow dr in ds.Tables[0].Rows) //<-- this is my Excel file to iterate through
{
 DataRow row = myDataSet.Tables[0].NewRow();
 row["GUID"] = refGUID;
 row["Name"] = dr.ItemArray[0];
 row["Pricing"] = dr.ItemArray[1];
 myDataSet.Tables[0].Rows.Add(row);
 dataAdapter.Update(myDataSet);
}

Теперь мои вопросы следующие:

  1. Это лучше отправить INSERT Команда для каждой строки (это было бы SqlCommand метод) или лучше заполнить специальный DataSet (2-й метод)? Я предполагаю, что это довольно "глупо" иметь 1000 вставок на сервер SQL?
  2. dataAdapter.Update(myDataSet) <- я должен сделать это ПОСЛЕ итерации по всем строкам Excel или для каждой строки (как показано в приведенном выше примере кода), будет ли это волшебным образом создавать транзакцию?
  3. Какой метод я должен использовать? Существует также LINQ to SQL - почему бы не использовать это (производительность может быть из-за другого уровня)?
  4. Что происходит с DataSet когда при чтении файла Excel возникает какая-то ошибка - обновление все еще отправляется на сервер SQL или все потеряно?

Вкратце: я хочу импортировать файл Excel на SQL-сервер построчно при внесении изменений в импортируемые данные (и я не хочу использовать пакеты служб SSIS [потому что помимо преобразования данных я делаю гораздо больше с файлом Excel, таким как импорт его в Sharepoint и запуск рабочих процессов] или BizTalk)
"" Как это сделать красиво?
В итоге я пошел дальше и купил Aspose Cells. У Aspose есть очень хороший набор инструментов.

Источник

5 ответов

Решение

Вы упоминаете, что не хотите использовать SSIS - но рассматривали ли вы SqlBulkCopy? Тогда вам не нужно ничего, кроме.NET, но вы все равно можете использовать самый быстрый / самый прямой импорт.

Это примет DataTable, так что вы можете подготовить свои данные в DataTable и затем нажать триггер. Транзакции опционально поддерживаются IIRC. Для больших данных вы также можете реализовать IDataReader, чтобы обеспечить полностью потоковую загрузку (при этом все еще обрабатывая каждую строку в пути).

Источник

DataSet, вероятно, в конечном итоге отправит операторы INSERT на сервер, поэтому, на мой взгляд, лучше всего просто отправлять операторы INSERT без DataSet. Вы также можете лучше контролировать процесс, например, проверять отдельные строки на наличие ошибок, вести журнал и т. Д.

Источник

Учитывая ваше описание вашей проблемы: "Я хочу импортировать файл Excel на SQL-сервер построчно, внося изменения в импортируемые данные" - SSIS - идеальный инструмент для этой работы.

Импорт данных Excel с службами SQL Server Integration Services SSIS с проблемами данных в Юникоде и не в Юникоде

Источник

Вы можете преобразовать свою таблицу обработанных данных в XML и передать ее хранимой процедуре на сервере Sql (в одном запросе), и ваша хранимая процедура будет анализировать XML для создания записей.

Источник 
INSERT INTO [dbo].[TableName]
           ([ColumnName1]
           ,[ColumnName2])
)
SELECT [ColumnName1]
           ,[ColumnName2]

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source= PathToFile.xls;Extended Properties=Excel 8.0')...[Sheet1$]
Источник
Другие вопросы по тегам