ГлавнаяРазноеИмпорт данных из excel в sql

NC’s Blog. Импорт данных из excel в sql


перенос данных из sql в excel « NC’s Blog

Microsoft Office Exel уже довольно давно стал ключевой программой при переносе разных баз, тк практически любая даже очень старая база работает с Exel и перевести в него данный не состовляет ни каких проблем. Это пошаговое руководство описывает различные способы импорта данных из листов Microsoft Excel в базы данных Microsoft SQL Server.

Требования перед началом

В приведенном ниже списке перечислены рекомендованные оборудование, программное обеспечение, сетевая инфраструктура, а также необходимые пакеты обновления:

  • Экземпляр Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Microsoft SQL Server 2005 или Microsoft SQL Server 2008
  • Microsoft Visual Basic 6.0 для примеров объектов ADO, использующих Visual Basic

В ряде разделов данной статьи предполагается, что пользователь обладает достаточными знаниями в следующих областях:

  • Службы преобразования данных
  • Связанные серверы и распределенные запросы
  • Разработка объектов ADO на Visual Basic

Описание

В примерах, приведенных в данной статье, импорт данных Excel выполняется с помощью следующих функций:

  • Службы преобразования данных SQL Server (DTS)
  • Службы интеграции Microsoft SQL Server 2005 (SSIS)
  • Связанные серверы SQL Server
  • Распределенные запросы SQL Server
  • Поставщик объектов данных ActiveX(ADO) и Microsoft OLE DB для SQL Server
  • Поставщик ADO и Microsoft OLE DB для Jet 4.0

Примеры

Import или Append

В примерах команд SQL, используемых в статье, показаны запросы Create Table для импорта данных Excel в новую таблицу SQL Server с использованием конструкций SELECT…INTO…FROM. При сохранении ссылок на объекты-источники и получатели выражения, приведенные в примерах, могут быть преобразованы в запросы Append с использованием конструкций INSERT INTO…SELECT…FROM.

Использование DTS или SSIS

Для импорта данных Excel в таблицы SQL Server могут быть использованы мастер импорта служб преобразования данных (DTS) SQL Server или мастер импорта и экспорта SQL Server. При работе с мастером и выборе исходных таблиц Excel помните, что имена объектов Excel со знаком доллара ($) являются именами листов (например Лист1$), а имена объектов без знака доллара являются названиями именованных диапазонов Excel.

Использование связанного сервера

Для упрощения запросов книга Excel может быть настроена как связанный сервер в SQL Server. Для получения дополнительных сведений щелкните приведенный ниже номер статьи базы знаний Майкрософт: 306397 (http://support.microsoft.com/kb/306397/RU/ )

Следующая программа импортирует данные из рабочего листа «Customers» связанного сервера Excel «EXCELLINK» в новую таблицу SQL Server с именем XLImport1:

←** SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$] **→

При использовании OPENQUERY источнику может быть передан сквозной запрос:

←** SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,

‘SELECT * FROM [Customers$]’)

**→

Использование распределенных запросов

Если устанавливать существующее подключение к книге Excel как связанный сервер нежелательно, данные могут быть импортированы с использованием функций OPENDATASOURCE или OPENROWSET. В следующих примерах кода также производится импорт данных из рабочего листа Excel «Customers» в новые таблицы SQL Server:

←** SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$] SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers$]) SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]' **→
Использование ADO и SQLOLEDB

Синтаксис «распределенных запросов», приведенный в разделе Использование распределенных запросов, может быть использован также в приложении ADO для импорта данных Excel в SQL Server, если для подключения к SQL Server используется Microsoft OLE DB для SQL Server (SQLOLEDB).

Для работы следующего примера программы на Visual Basic 6.0 требуется добавление ссылки на проект в объекты данных ActiveX (ADO). В этом примере показано использование функций OPENDATASOURCE и OPENROWSET для подключения SQLOLEDB.

on Visual Basic

←** Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _ "Initial Catalog=<database>;User ID=<user>;Password=<password>" 'Import by using OPENDATASOURCE. strSQL = "SELECT * INTO XLImport6 FROM " & _ "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _ "'Data Source=C:\test\xltest.xls;" & _ "Extended Properties=Excel 8.0')...[Customers$]" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff 'Import by using OPENROWSET and object name. strSQL = "SELECT * INTO XLImport7 FROM " & _ "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _ "'Excel 8.0;Database=C:\test\xltest.xls', " & _ "[Customers$])" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff 'Import by using OPENROWSET and SELECT query. strSQL = "SELECT * INTO XLImport8 FROM " & _ "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _ "'Excel 8.0;Database=C:\test\xltest.xls', " & _ "'SELECT

nikpro.wordpress.com

import - Как импортировать таблицу Excel в SQL Server?

Мне нужно импортировать таблицу Excel в SQL Server 2005. Какие шаги мне нужно предпринять, чтобы выполнить это?

задан Fee 23 янв. '09 в 14:50 источник поделиться

Мастера "Data Transformation Services" - ваш друг.

В инструкциях здесь подразумевается SQL Server 2000

  • Открыть SQL Server Enterprise Manager
  • Щелкните правой кнопкой мыши соответствующий сервер
  • Выберите Импорт данных
  • Запустите мастер.
    • Нажмите "Далее" на первом экране
    • Выберите "Microsoft Excel" в качестве источника данных
    • Введите путь к файлу XLS и нажмите "Далее" .
    • Введите сведения о подключении для своей базы данных и нажмите "Далее" .

В зависимости от того, что вы хотите делать с данными, у вас есть несколько новых вариантов. Для одноразовых заданий мне обычно проще всего импортировать все данные excel, а затем редактировать его в SQL Server, но если вы собираетесь повторять это действие для нескольких файлов, вы можете создать действительно сексуальный импорт script.

Если вы собираетесь запустить его снова, вы можете сохранить пакет DTS с помощью мастера, а затем отредактировать его в разделе "Услуги преобразования данных" в Enterprise Manager. Это хороший способ узнать, как работает DTS.

ответ дан RB. 23 янв. '09 в 14:53 источник поделиться

вы также можете сделать это с помощью OPENROWSET

INSERT INTO SOMETABLE SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]') ответ дан SQLMenace 23 янв. '09 в 17:15 источник поделиться

Альтернативным быстрым и (очень) грязным решением является добавление формулы к листу excel, например:

="INSERT INTO table1(col1, col2, col3) SELECT " & A1 & ", '" & B1 & "', '" & C1 & "'"

Скопируйте это вниз (CTRL + D), и вы хорошо пойдете.

ответ дан edosoft 23 янв. '09 в 15:01 источник поделиться

Если ваша таблица не организована хорошо для индивидуального импорта в таблицы SQL (например, форматирование для чтения с подзаголовками, пробелы, столбцы сумматора и т.д.), тогда есть готовые инструменты вы можете использовать этот тип вещей. См. Khronos Excel Pump для примера. ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ - я работаю в компании, которая продает это. Это довольно настраиваемо, поэтому вы можете хранить таблицу в человеческом макете и надежно импортировать в структурированные таблицы SQL, включая замену текстовых значений значениями полей идентификатора или поворот широких сеток на узкие таблицы и т.д.

ответ дан Bailes 12 нояб. '17 в 3:52 источник поделиться

qaru.site

Импорт данных из Excel в БД SQL

Вопрос: Trouble с импортом данных из excel файла на форму C#, которая связана с БД

Доброго времени суток всем! Прошу Вас помочь решить 2 данные проблемы. Предполагаю, что тема могла совпасть с чьей-нибудь, но мне ничего не помогло из того, что мне предложили и из того, что искал на данном форуме. Первая проблема кажется совсем простой, но я впал в ступор и не могу уже думать нормально. В общем, мой проект - типичная связка MS Visual Studio + MS SQL. Обе проблемы связаны только с одной формой "Аммиак". Значит, данные могут находиться в аммиакdataGridView из БД, а также могут вручную заноситься на клиенте (это и так всем понятно), а еще у меня реализован импорт данных из Excel файла. Вот тут-то и весь сыр-бор. Когда я импортирую эти самые данные, 1) они записываются поверх существующих строк, то есть импортируемые данные удаляют имеющиеся строки и сами записываются (логично, что не должно быть так). И тут же возникает другая проблема,2) импортируемые данные не сохраняются на сервере, отображаются в DataGridView и всё. Я понимаю, что DataGridView служит только для представления данных, и что нужно сохранять(как на клиенте, так и на сервере) через адаптер(и/или датасет), но что-то не получается у меня.

P.S. Если Вы считаете эти вопросы нубскими и т.п., а также настроены ответить мне грубо типа "мы ничего не должны решать за тебя", "сам изучай все книги, документацию", "поищи в интернете, там полно таких тем" и бла-бла, то прошу не отписываться здесь. Я здесь прошу помощи у добрых людей. Я верю, что на этом форуме найдутся такие люди, которые помогут/посоветуют бескорыстно. Я сам помогаю всем во всём, чем могу(и не обязательно с программированием). Я пишу этот постскриптум, потому что начитался таких ответов в разных темах, куда меня заносили мои поиски. Если кого обидел, то простите.

Код импорта:

Код C#
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 using System.Data.OleDb   private void import_btn_Click(object sender, EventArgs e)       { OpenFileDialog openDialog = new OpenFileDialog();             openDialog.Filter = "Файл Excel|*.XLSX;*.XLS";                         if (openDialog.ShowDialog() == DialogResult.OK)             {                 this.pathbox.Text = openDialog.FileName;             }             String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +                     pathbox.Text +                     ";Extended Properties='Excel 12.0 XML;HDR=YES;IMEX=1';";             OleDbConnection connection =         new OleDbConnection(constr);     connection.Open();                         DataTable schemaTable = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,                 new object[] { null, null, null, "TABLE" });               string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];             string select = String.Format("SELECT * FROM [{0}]", sheet1);             OleDbDataAdapter ad = new OleDbDataAdapter(select, connection);                            DataSet dataset = new DataSet();                ad.Fill(dataset);                connection.Close();                  if (dataset.Tables.Count == 0)                {                    MessageBox.Show("Ошибка, результат не содержит строк");                    return;                }                  bindingSource.DataSource = dataset.Tables[0];                аммиакdataGridView.DataSource = bindingSource;                this.аммиакTableAdapter.Update(azotDataSet);//данная строка ничего не обновляет на сервере      }
Значит, схема такая, есть azotdataset, в котором есть несколько таблиц (в т.ч. и таблица аммиак). Я предполагаю, что нужно передать новые значения аммиакdatagridview в адаптер, и вот тогда все обновится. Я пробовал приравнивать, но Visual Studio ругается из-за невозможности конвертации разных типов (аля DataAdapter нельзя переделать в DataTable), что в принципе логично. Так что я полностью раздавлен данной безвыходной (для меня!) ситуацией.

Буду очень признателен, если Вы мне поможете.

forundex.ru