Импорт данных в excel из access vba: Импорт или связывание данных в книге Excel

Как в Access на VBA импортировать данные в базу Microsoft SQL Server? | Info-Comp.ru

Очень часто у программистов и пользователей, у которых реализовано приложение в связке клиент (Access – adp проект) и сервер (Microsoft SQL Server – база данных) возникает необходимость импорта данных, с помощью adp проекта, в базу данных, причем чтобы это могли делать простые пользователи, т.е. максимально просто, без привлечения программистов. И сегодня мы поговорим о реализации данной возможности.

Так как импортируемые данные могут быть разные, сегодня мы рассмотрим 2 примера импорта.

  1. Импорт данных из Excel
  2. Импорт изображения в базу данных и ее дальнейшее отображение в отчетах и формах

Импорт данных из Excel в Microsoft SQL Server на VBA Access

И начнем мы с импорта данных из таких форматов как xls и dbf, так как это очень часто требуется в работе, а если у кого и не часто, то все равно рано или поздно такая необходимость возникнет.

Когда у меня стояла задача реализовать такой импорт, я нашел много способов и средствами Microsoft SQL сервера, и встроенными средствами Access, но в каждом из них были небольшие минусы, иными словами, не для каждодневного использования пользователями. Но мне встретился такой способ, который меня полностью устроил и именно о нем я сейчас расскажу.

Данный способ заключается в подключении напрямую к файлу источнику через поставщика Microsoft.Jet.OLEDB.4.0.

Для начала давайте определимся, с какими данными мы будем работать.

Допустим, у нас есть файл «file.xls» со следующими данными:

Pole1

Pole2

Pole3

Pole4

1

4

7

10

2

5

8

11

3


6

9

12

Примечание! Называйте лист в файле, на котором располагаются данные, также как и сам файл.

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

Таблицу создадим вот так:

   
   CREATE TABLE [dbo].[testimport](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [col1] [int] NULL,
        [col2] [int] NULL,
        [col3] [int] NULL,
        [col4] [int] NULL,
        
   CONSTRAINT [PK_testimport] PRIMARY KEY CLUSTERED 
   (
        [id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO

Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих. В нем подробно рассмотрены все базовые конструкции языка T-SQL.

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

Вы добавили кнопку, теперь в событие «Нажатие кнопки» добавьте следующий код:

   
   'Объявляем переменные
   Dim FileDialog As FileDialog
   Dim cnn As ADODB.Connection
   Dim rs  As ADODB.Recordset
   Dim rstProj As ADODB.Recordset
   Dim path  As String
   Dim counter As Integer
   Dim strSQL As String
   Dim strFile As String
   
   'создаем объект "Диалоговое окно выбора файла"
   Set FileDialog = Application.FileDialog(msoFileDialogOpen)
   'Убираем множественный выбор файлов, нам нужен только один
   FileDialog.AllowMultiSelect = False
   'очистим и установим новые фильтры
   FileDialog. Filters.Clear
   FileDialog.Filters.add "Excel и DBF", "*.xls, *.dbf"
   'зададим фильтр по умолчанию (с индексом 1 т.е. первый), можно и не писать,
   'но вдруг Вы заходите установить несколько фильтров
   FileDialog.FilterIndex = 1
   
   'проверяем, что сделал пользователь, выбрал файл или нет
   If FileDialog.Show = False Then
      'Если нет, то выходим
      Set dlgFile = Nothing
      Exit Sub
   End If
   'получаем путь к файлу
   path = Trim(FileDialog.SelectedItems(1))
   'очищаем переменную с объектом
   Set FileDialog = Nothing
  
   'проверяем, что за путь нам вернулся, если ничего не вернулось, то все пропускаем
   If path <> "" Then
    'Создаем подключение к источнику
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
    'Определим имя файла для запроса
    strFile = Mid(path, InStrRev(path, "\") + 1, Len(path) - InStrRev(path, "\") - 4)
    'Смотрим, что за файл выбрал пользователь
    'Если XLS то заходим сюда
    If UCase(path) Like "*. XLS" Then
      'Задаем путь к файлу
      cnn.Properties("Data Source") = path
      'Задаем тип файла
      cnn.Properties("Extended Properties") = "Excel 8.0"
      'Строка запроса
      strSQL = "select pole1, pole2, pole3, pole4  from [" + strFile + "$]"
    'Если DBF то сюда
    ElseIf UCase(path) Like "*.DBF" Then
      'Задаем путь к файлу
      cnn.Properties("Data Source") = Mid(path, 1, InStrRev(path, "\"))
      'Задаем тип файла
      cnn.Properties("Extended Properties") = "dBase IV"
      'Строка запроса
      strSQL = "select pole1, pole2, pole3, pole4  from  [" + strFile + "]"
    End If
   
   'устанавливаем подключение к источнику
   cnn.Open
   'создаем Recordset для данных из источника
   Set rs = New ADODB.Recordset
   'открываем и считываем данные из источника
   rs.Open strSQL, cnn
   'создаем Recordset для данных из базы
   Set rstProj = New ADODB. Recordset
   'Открываем данный Recordset
   rstProj.Open "dbo.testimport", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   'переменная для подсчета количества импортируемых записей
   counter = 0
   
   'Запускаем цикл для переноса данных из источника в базу,
   'т.е. одна итерация цикла это одна строка в файле и базе данных
    While Not (rs.EOF)
      'присваиваем значения из столбца источника столбцу в базе данных
      With rstProj
        .AddNew
        .Fields("col1") = rs.Fields("pole1").Value
        .Fields("col2") = rs.Fields("pole2").Value
        .Fields("col3") = rs.Fields("pole3").Value
        .Fields("col4") = rs.Fields("pole4").Value
        .Update
        'Увеличиваем наш счетчик
       counter = counter + 1
      End With
      'читаем следующую строку
      rs.MoveNext
    Wend
    
   'закрываем подключение к базе MSSql
    rstProj. Close
    Set rstProj = Nothing
    'закрываем источник данных
    cnn.Close
    Set cnn = Nothing
    'Очистим Recordset
    Set rs = Nothing
    'и выведем на экран, сколько мы импортировали строк
    MsgBox counter
    
   End If

Общий смысл заключается в том, что Вы выбираете файл, а если конкретней, то считываете путь к файлу, затем подключаетесь к этому файлу, считываете данные и записываете в свою базу данных.

Примечание! Если Вы хотите накапливать данные в таблице (в нашем случае testimport), то в файле источнике, добавляете какой-нибудь признак, чтобы потом иметь возможность выбрать те данные, которые Вы только что загрузили (например, столбец с датой, и в базе соответственно тоже добавьте), если не хотите, то перед импортом данных в  таблицу, очищайте ее, иначе все данные будут накапливаться.

Импорт изображения в Microsoft SQL Server на VBA Access

Теперь перейдем к импорту изображения и его дальнейшее отображение. Мне это понадобилось тогда, когда возникла необходимость выводить некую картинку в отчетах, но при условии того, что проект adp был один общий, а баз было несколько, т.е. для разных групп пользователей (несколько филиалов), и картинки во всех филиалах разные, поэтому статически прописать в отчетах было нельзя, поэтому мне пришлось искать другие решения.

Примечание! Примеры ниже тестовые, поэтому Вы можете создать свою таблицу со своими полями, и, соответственно, свою форму.

Создадим таблицу

   
   CREATE TABLE [dbo].[kartinki](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [Picture] [varbinary] (MAX)  ,
        
   CONSTRAINT [PK_kartinki] PRIMARY KEY CLUSTERED 
   (
        [id] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY]
   GO

На форму добавьте объект «Рисунок», первоначально придется выбрать любой рисунок для добавления данного объекта на форму, потом его можно очистить (я его назвал kartinka). Источник записей формы будет наша вновь созданная таблица. И, конечно же, кнопочку для добавления рисунка. Для кнопки в событии «Нажатие кнопки» вставьте следующий код:

   
   'Объявляем переменные
   Dim rs As Recordset
   Dim FileDialog  As FileDialog
   Dim path As String
   Set rstProj = New ADODB.Recordset
   'тоже что и в примере выше
   Set FileDialog = Application.FileDialog(msoFileDialogOpen)
   FileDialog.AllowMultiSelect = False
   FileDialog.Filters.Clear
   FileDialog.Filters.add "Картинки и рисунки", "*.jpg, *.gif, *.bmp, *.png"
   'Для разнообразия добавим еще один фильтр
   FileDialog.Filters.add "Все файлы", "*.*"
   FileDialog.FilterIndex = 1
   
   If FileDialog.Show = False Then
      Set FileDialog = Nothing
      Exit Sub
   End If

   path = Trim(FileDialog.SelectedItems(1))
   
   If path <> "" Then
      Set FileDialog = Nothing
      'присваиваем нашей картинке на форме выбранную картинку, т. е. задаем путь
      kartinka.Picture = path
      rstProj.Open "[dbo].[kartinki]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
      Set rs = rstProj
      'создаем новую строку и переносим бинарные данные в нашу таблицу в базе
      rs.AddNew
      rs![Picture] = kartinka.PictureData
      rs.Update
      'все это дело закрываем
      rstProj.Close
      Set rstProj = Nothing
      Set rs = Nothing
      'пересчитаем данные на форме
      Me.Recalc
   End If

Для наглядности, чтобы увидеть, что Вы импортировали картинку в базу, на форме в событии «Текущая запись» вставьте следующий код:

   
   If Me.NewRecord Then
      kartinka.Picture = ""
   Else
      kartinka.PictureData = Me![Picture]
   End If

Теперь, где Вам нужно выводить картинку, например, в отчетах, Вы также создадите объект рисунок и в событии отчета «Открытие» будете присваивать значение свойства PictureData из базы данных и все. Например, вот так:

   
   Dim strSQL As String
   Set rs = New ADODB.Recordset
   strSQL = "select picture from dbo.kartinki"
   rs.Open strSQL, CurrentProject.Connection
   rez = CStr(rs.Fields(0))
   rs.Close
   Me.kartinka.PictureData = rez
   Set rs = Nothing

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

Смысл заключается в использовании свойства PictureData объекта «Рисунок», и таким способом у Вас будут без проблем отображаться картинки разных форматов, в отличие от распространенного ole объекта, для которого необходимо устанавливать специальные ole сервера для корректного отображения различных форматов файлов.

Вот в принципе и все, если что непонятно пишите в комментариях, может, чем помогу. Удачи!

import from excel with VBA to msSQL Server


Вопрос задан


Изменён
3 года 4 месяца назад


Просмотрен
229 раз

Доброго времени суток!

Встала задача импорта(посредством VBA) некоторых данных из excel в таблицу на mssql server с уже существующими данными, если импортируемые данные новые то они должны импортироваться корректно, если импортируемые данные уже существуют, то необходимо заменить лишь некоторые столбцы. Сейчас всё это делается посредством INSERT вручную.

Прошу помощи так как не силён в VBA

Спасибо!

  • sql-server
  • excel
  • vba






2

Работающий вариант посредством объекта ADO — Adodb

Sub InsertSQL()
   Dim St As String, sn As Single, CustomerName As String
   Dim Rst As New ADODB.Recordset
   sn = cells(6,5).Value ' число с плавающей точкой
   CustomerName  = cells(6,6).Value ' строка
   St = " INSERT INTO tbl_Orders (fld_Value, fld_Custom_ID) Values (" &
   St = St & Cstr(sn) & ", " & CustomerName & ")"
   ConnectionString = "Provider=SQLOLEDB.1; Data Source=111.121.11.111; Initial 
   Catalog = BazaName; Persist Security Info=True; User ID=imyausera; 
   Password=1234546"
   Rst.CursorType = adOpenStatic
   Rst.CursorLocation = adUseClient
   Rst.Open ConStr, ConnectionString 
   Rst.Close
   Set Rst = Nothing
End Sub

Обратите внимание на

  1. Типы переменных и соответ. типы в базе данных SQL
  2. Синтаксис запроса SQL и правильность в конечном счете (генерация строки)

  3. Необходимо добавить расширение

    1. Правильность адресации к серверу и т.д.
    2. Для замены строки в SQL таблице применяете инструкцию «UPDATE ….»







Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации


Почта

Необходима, но никому не показывается





Нажимая на кнопку «Отправить ответ», вы соглашаетесь с нашими пользовательским соглашением, политикой конфиденциальности и политикой о куки


Выбор и импорт файла и листа Excel для импорта в Access

  • Пример загрузки — 529,3 КБ

Введение

Общим требованием в Microsoft Access является возможность импорта файлов. По сравнению с работой с C# и Visual Basic.Net это несколько отличается.

Реализация

Первое требование — иметь возможность просматривать файл Excel . Вот код, который выполняется в виде, когда « Обзор ” кнопка нажата:

VB.NET

 Частная подкнопкаBrowse_Click()
    ' Удалите существующую информацию перед просмотром
    Dim itemsString As String
    textBoxExcelFileToImport = ""
    списокBoxWorksheets.RowSource = ""
    subFormData.Visible = Ложь
    DoEvents
   
    ' Разрешить пользователю переходить к файлу Excel для импорта
    textBoxExcelFileToImport = ПолучитьExcelFile
    Если IsNull (textBoxExcelFileToImport), то выйдите из Sub
   
    ' Заполните поле списка, которое будет содержать имена листов
    itemsString = Join(ExcelSheetsNameList(textBoxExcelFileToImport), ";")
    listBoxWorksheets.RowSource = строка элементов
Конец суб

Этот код сначала очищает информацию в форме, которая может быть применима к последнему выбранному файлу, а затем пользователю предоставляется диалоговое окно для выбора файла с расширением « xlsx ». Если файл не выбран, код возвращается, в противном случае открывается файл Excel, чтобы найти его имена листов, которые отображаются в поле со списком, чтобы пользователь мог выбрать лист для импорта.

Сначала очищается информация в форме , включая удаление имени файла TextBox , имена листов ListBox и скрытие подчиненной формы.

Затем программа должна позволить пользователю выбрать файл Excel для использования. Реализация Browse выполняется в методе GetExcelFile :

VB.NET

 Public Function GetExcelFile()
    Установите fDialog = Application.FileDialog(3)
   
    С помощью fDialog
        ' Разрешить пользователю делать множественный выбор в диалоговом окне
        .AllowMultiSelect = Ложь
        ' Установить заголовок диалогового окна.
        .Title = "Выберите файл Excel для импорта"
        ' Очистим текущие фильтры и добавим свои. 
        .Фильтры.Очистить
        .Фильтры.Добавить "Excel 2007", "*.xlsx"
       
        ' Показать диалоговое окно. Если метод .Show возвращает True, пользователь выбрал
        ' файл. Если метод .Show возвращает False, пользователь нажал кнопку «Отмена». '
        Если .Показать = Истина Тогда
            GetExcelFile = .ВыбранныеЭлементы(1)
        Еще
            GetExcelFile = ноль
        Конец, если
    Конец с
Завершить функцию

Этот метод позволяет пользователю перейти к файлу и возвращает путь к файлу.

После выбора файла Excel вам необходимо заполнить имя листа ListBox именами  Worksheet . Метод ExcelSheetsNameList возвращает имена рабочих листов :

VB.NET

 Публичная функция ExcelSheetsNameList (путь как строка) как строка ()
    OpenExcelWorkbook (путь)
    Dim shts() как строка
    ReDim shts(m_OpenWorkbook. Sheets.Count - 1)
    Для x = 1 Для m_OpenWorkbook.Sheets.Count
        штс(х - 1) = m_OpenWorkbook.Sheets(x).имя
    Следующий х
    ExcelSheetsNameList = шт.
Завершить функцию

Имена Worksheet возвращаются в массиве , который необходимо преобразовать в строку с разделителями » ; » , выполненную в обработчике события щелчка Browse . ListBox RowSource устанавливается на эту строку с разделителями .

Эта функция использует OpenExcelWorkbook для фактического открытия рабочей книги :

VB.NET

 Открытая функция OpenExcelWorkbook (путь как строка)
    Если m_OpenExcel ничто, то
        Установите m_OpenExcel = CreateObject("Excel.Приложение")
    Конец, если
   
    Если Пусто(m_OpenWorkbook) Тогда
        m_OpenExcel.Workbooks.Открыть путь
        Установите m_OpenWorkbook = m_OpenExcel. ActiveWorkbook
    ИначеЕсли m_OpenWorkbook.FullName <> путь Тогда
        m_OpenExcel.Workbooks.Открыть путь
        Установите m_OpenWorkbook = m_OpenExcel.ActiveWorkbook
    Конец, если
Завершить функцию

Этот код хранит копию Workbook объекта в переменной m_OpenWorkbook и приложения Excel в m_OpenExcel , чтобы сократить обработку. Вызов NewImportUtilitiesModuleDispose в NewImportUtilitiesModule гарантирует удаление этих объектов .

Далее пользователь должен выбрать имя из списка Worksheet . Когда лист выбран, он автоматически обрабатывается:

VB.NET

 Частный подчиненный списокBoxWorksheets_Click()
    Тусклый лист как рабочий лист
    Dim firstCell As String
    Диапазон затемнения Как строка
    Dim sheetRange As String
    subFormData.SourceObject = Пусто
    УдалитьTableSafe "T_Temp"
    DoEvents
   
    Установить лист = GetExcelWorksheet (textBoxExcelFileToImport, listBoxWorksheets)
    первая ячейка = FindFirstNonEmptyCell (лист, 10)
    Если firstCell="" Тогда
        MsgBox "Не удалось найти ячейку с содержимым, которая должна быть верхней" _
& "левый угол содержимого для импорта"
        листбоксворкшитс = ""
    Еще
        диапазон = FindDataCells (лист, первая ячейка)
        SheetRange = listBoxWorksheets & "!" & спектр
        DoCmd. TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "T_Temp",
textBoxExcelFileToImport, True, sheetRange
        subFormData.SourceObject = "Table.T_Temp"
        subFormData.Visible = Истина
    Конец, если
    Установить лист = Ничего
Конец суб

Перед всей этой обработкой в ​​обработчике события клика для ListBox выбора ссылка на временную таблицу, используемую в RowSource формы sub , удаляется путем установки RowSource в значение Empty. Затем временная таблица удаляется. Затем рабочий лист с указанным именем возвращается с использованием GetExcelWorksheet :

VB.NET

 Открытая функция GetExcelWorksheet (путь как строка, имя листа как строка) как рабочий лист
    Тусклая рабочая книга как рабочая книга
    Тусклый лист как рабочий лист
    OpenExcelWorkbook (путь)
    Установите GetExcelWorksheet = m_OpenWorkbook. Sheets(имя листа)
Завершить функцию

Эта функция использует OpenExcelWorkbook , чтобы убедиться, что рабочая книга открыта (и если нет, открывает ее, а затем возвращает рабочий лист с совпадающим именем.

Затем выполняется поиск рабочей книги по найти первую ячейку, которая не является Empty . Это делается с помощью функции FindFirstNonEmptyCell :

VB.NET

 Общедоступная функция FindFirstNonEmptyCell (лист как рабочий лист, ограничение как целое число) как строка
    Для i = 1 Для ограничения
        Для j = 1 To i
            ячейка = лист.ячейки (j, i - j + 1)
            Если Не Является Пустым (ячейка), то
                FindFirstNonEmptyCell = лист.диапазон(лист.ячейки(j, i - j + 1),
                                лист.ячейки(j, i - j + 1)).address(False, False)
                Выход из функции
            Конец, если
        Следующий
    Следующий
Завершить функцию

Поиск выполняется с поиском в левом верхнем углу Cell , поиском ближайших ячеек и остановкой после указанного предела, в данном случае 10. Он возвращает код для найденной ячейки (например, « ). В3 "). Существует также функция , которая найдет первую ячейку с указанным содержимым вместо первой непустой ячейки ( FindCellWithSpecifiedContent ).
Затем в рабочем листе выполняется поиск первых Пустая Ячейка вправо и вниз от найденной Ячейка с использованием функции FindDataCells :

VB.NET

 Публичная функция FindDataCells(лист как строка) AsCell As
    Dim startRow, startColumn, currentRow, currentColumn As Integer
    текущаяСтрока = лист.диапазон(initalCell).Строка
    начальная строка = текущая строка
    currentColumn = лист.диапазон(initalCell).Колонка
    начальная колонка = текущая колонка
   
    Пока не пусто (лист. ячейки (currentRow, currentColumn))
        текущая строка = текущая строка + 1
        текущая колонка = текущая колонка + 1
    Венд
   
    Если не является пустым (лист.  ячейки (текущая строка - 1, текущая колонка)) тогда
        текущая строка = текущая строка - 1
        Пока не пусто (лист. ячейки (currentRow, currentColumn))
            текущая колонка = текущая колонка + 1
        Венд
        текущая колонка = текущая колонка - 1
    ElseIf Not IsEmpty(sheet.cells(currentRow, currentColumn - 1)) Тогда
        текущая колонка = текущая колонка - 1
        Пока не пусто (лист. ячейки (currentRow, currentColumn))
            текущая строка = текущая строка + 1
        Венд
        текущая строка = текущая строка - 1
    Конец, если
    FindDataCells = лист.диапазон(лист.ячейки(startRow, startColumn),
            лист.ячейки (текущая строка, текущая колонка)). Адрес (ложь, ложь)
Завершить функцию

Эта функция будет искать в ячейках рабочего листа вправо и вниз последнюю не- пустую ячейку , сначала продвигаясь по диагонали вправо и вниз, а затем либо вправо, либо вниз, чтобы найти размер данные. Он возвращает код экстента найденных ячеек (например, «B3:E56»).

Затем выполняется DoCmd.TransferSpreadsheet . Когда это завершено, на таблицу ссылаются в подчиненной форме SourceObjec t, и подчиненная форма становится видимой.

Заключение

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

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

Другое ограничение заключается в том, что этот код предназначен для работы только с электронными таблицами, которые были сохранены в новейшем формате, а не с данными « CSV ». Это можно легко адаптировать, включив другие типы файлов в диалоговый фильтр и установив TransferSpreadsheet аргументы изменены для обработки других типов.

Одна из возможных проблем заключается в том, что TransferSpreadsheet заключается в том, что при создании таблицы тип поля будет основываться на первой записи, так что если первая запись является числом, то этот столбец будет для числовой. Это может вызвать ошибки импорта при импорте данных после первой строки.

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

История

  • 18-09-06: Начальная версия

Последние несколько лет работает разработчиком C# по контракту, в том числе 3 года в Microsoft. Ранее работал с Visual Basic и Microsoft Access VBA и разрабатывал код для Word, Excel и Outlook. Начал работать с WPF в 2007 году, когда работал в команде Microsoft WPF. Последние восемь лет работал в основном старшим разработчиком WPF/C# и Silverlight/C#. В настоящее время работает разработчиком WPF в компании BioNano Genomics в Сан-Диего, Калифорния, переделывая свой пользовательский интерфейс для своей системы камер. с ним можно связаться по адресу [email protected].

4. Интеграция из интерфейса Access

Использование VBA из Access очень эффективно и будет подробно рассмотрено в оставшейся части книги. Однако Access имеет несколько очень мощных функций, доступных прямо из пользовательского интерфейса для импорта, экспорта и связывания данных с Excel. Возможность сделать это из пользовательского интерфейса Access
облегчает многие задачи. Например, предположим, что у вас есть две электронные таблицы Excel с данными о клиентах, и вы хотите знать, какие клиенты указаны на одной таблице, но отсутствуют на другой. Вы можете попробовать использовать функции VLOOKUP в Excel и, возможно, даже написать код в Excel для поиска на другом листе. Однако из Access вы можете просто связать два рабочих листа и запустить мастер поиска несопоставленных запросов; этот метод будет обсуждаться позже в этой главе.

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

  • Импортировать данные из Excel в новую или существующую таблицу

  • Связать рабочий лист Excel или именованный диапазон и обращаться с ним как с таблицей в Access

  • Экспорт таблицы Access для анализа с помощью Excel

  • Используйте функцию «Сохранить как» для экспорта таблицы или запроса

  • Сохранить отчет в формате Excel

Для выполнения этих задач требуется всего несколько щелчков мышью. После использования этих функций вы начнете видеть другие возможности для интеграции этих приложений. Кроме того, вы можете использовать Access для выполнения этих задач с файлами DBF, текстовыми файлами фиксированной ширины, текстовыми файлами с разделителями и таблицами источников данных ODBC. Наши примеры, однако, будут сосредоточены на Excel.

Excel обычно используется для создания рабочей книги, которая выполняет ряд вычислений и возвращает набор результатов для анализа. Независимо от того, думаете вы об этом таким образом или нет, это сводится к основным функциям Excel. Удобство сохранения данных в Excel заключается в том, что набор результатов изменяется по мере внесения изменений в данные. Однако это также неудобная вещь, связанная с оставлением данных в Excel. Будут времена, когда вы захотите сделать моментальный снимок данных результатов и не захотите, чтобы они были изменены новыми данными. Рассмотрим входные параметры на рис. 4-1 и результирующие данные на рис. 4-2.

Рисунок 4-1. Рабочий лист Excel, предоставляющий входные данные для расчетов на странице амортизации

Рисунок 4-2. Данные результатов, которые изменяются при изменении входных данных на рабочем листе LoanInformation

загрузите эти данные в новую таблицу в Access, и вы не хотите, чтобы они менялись. В Access создайте пустое приложение базы данных, зайдя в Access и выбрав «Файл» — «Создать». В зависимости от версии Access вы получите либо новую пустую базу данных, либо меню, как показано на рис. 4-3. Если вы видите меню на Рисунке 4-3, щелкните Пустая база данных, и откроется диалоговое окно, которое вы видите на Рисунке 4-4. Затем дайте базе данных имя.

Рисунок 4-3. Меню новой базы данных, которое появляется в Microsoft Access 2003, когда вы выбираете Файл — Создать

Рисунок 4-4. Диалоговое окно, которое появляется, когда вы хотите создать новую базу данных, аналогично диалоговому окну «Сохранить как», которое вы видите в других приложениях Office

Совет

В отличие от Excel, Access постоянно сохраняет ваш прогресс по мере обновления данных. Это полезно в том смысле, что вы не работаете все утро и теряете работу из-за отключения электроэнергии или отказа сетевого диска. Но это также означает, что вы не можете выполнить работу, а затем вернуться к точке вашего последнего сохранения. В результате в Excel вы можете работать с книгой Book1, которую не нужно сохранять, а в Access нужно создать и сохранить файл.

После этого у вас будет пустая база данных, как показано на рис. 4-5.

Рисунок 4-5. Совершенно новая пустая база данных в Microsoft Access 2003, показывающая вкладку «Таблицы» базы данных

Теперь вы готовы импортировать данные в это новое приложение базы данных. Для этого щелкните правой кнопкой мыши пробел, когда у вас выбрана вкладка «Таблицы», и вы увидите меню, показанное на рис. 4-6. В этом меню выберите Импорт. Access открывает диалоговое окно File, подобное показанному на рис. 4-7. В поле «Тип файлов» выберите Microsoft Excel, а затем нужный файл. Access вызывает мастер импорта электронных таблиц.
показано на рис. 4-8. Вы заметите, что можете использовать целые рабочие листы или именованные диапазоны. Использование именованных диапазонов очень полезно, если у вас есть данные, напоминающие таблицы, на одном листе. Однако обычно я стараюсь хранить данные из таблиц на отдельном листе. В этом примере необходимые данные находятся на листе под названием «Амортизация», поэтому, чтобы импортировать их, щелкните его, а затем «Далее».

Рис. 4-6. Контекстный диалог с Импорт

Access открывает экран, показанный на Рис. 4-9. В этом случае установите флажок, указывающий, что первая строка содержит заголовки столбцов, и нажмите «Далее». (Если у вас есть заголовки и выполните

Рисунок 4-7. В диалоговом окне импорта файла

не установите этот флажок, вы рискуете ошибиться в типах данных.) Это вызывает следующий шаг в мастере, показанный на Рисунок 4-10, где вы выбираете, хотите ли вы импортировать данные в новую таблицу или в существующую таблицу. Поскольку это пустая база данных, выберите «В новой таблице» и нажмите «Далее». Следующий шаг мастера, показанный на рис. 4-11, позволяет выбрать столбцы и либо изменить имя, либо не импортировать его.

Совет

В некоторых случаях вы можете изменить тип данных, но во многих случаях это невозможно. Для этого примера примите значения по умолчанию и нажмите «Далее». Вы могли заметить, что на листе Excel не было пробелов в заголовках столбцов; вместо них использовались символы подчеркивания. Хотя в этом нет необходимости, это значительно упрощает написание запросов. Если у вас есть пробелы в именах полей в таблице Access, вам нужно будет помещать скобки — [Имя таблицы] — в свой запрос, когда вы ссылаетесь на поле в вычислениях и т. д. Поэтому, если вы были вынуждены импортировать электронную таблицу Excel, не имеющую подходящих имен полей в качестве таблицы, вы можете отредактировать их на этом шаге.

Следующий шаг мастера позволяет либо выбрать поле, являющееся первичным ключом, либо выбрать вариант отсутствия первичного ключа. Обычно я позволяю Access добавить первичный ключ. Однако, поскольку в этом примере у вас не будет двух платежей с одинаковым номером, вы можете выбрать «Платеж» в качестве первичного ключа, как показано на рис. 4-12. Последним шагом мастера, показанным на рис. 4-13, является присвоение таблице имени. По умолчанию для таблицы будет использоваться имя рабочего листа или именованный диапазон. Я предпочитаю размещать идентификатор «tbl_» перед именами таблиц, «qry_» перед именами запросов, «frm_» перед именами форм и т. д. Поэтому я предлагаю назвать эту таблицу tbl_Amortization.

Хотя может показаться, что шагов много, мастер делает это очень быстро, особенно если принять значения по умолчанию. Кроме того, вы обнаружите, что это долгожданное изменение, если вам когда-либо приходилось вводить информацию, уже содержащуюся в электронной таблице, в базу данных.

Рисунок 4-8. Первый шаг мастера импорта электронных таблиц, когда вы можете выбрать импорт рабочих листов или именованных диапазонов

Рисунок 4-9. Второй шаг процесса импорта, когда вы можете выбрать, будет ли первая строка содержит заголовки столбцов

Рисунок 4-10. Третий шаг, когда вы выбираете, хотите ли вы импортировать электронную таблицу в новую таблицу или в существующую таблицу

Рисунок 4-11. Шаг, когда вы можете изменить имена полей и выбрать, хотите ли вы чтобы пропустить импорт определенного поля

Рисунок 4-12. Шаг, когда вы выбираете, какое поле, если таковое имеется, использовать в качестве первичного ключа таблицы

Рисунок 4-13. Последний шаг в мастере, когда вы выбираете имя таблицы и можете выбрать один из двух вариантов доступа, чтобы помочь вам и/или проанализировать вашу новую таблицу

Еще одно преимущество импорта данных в новую базу данных заключается в том, что вам не нужно выполнять изнурительный процесс создания таблицы с помощью представления «Дизайн». Access использует информацию, которую вы загружаете, чтобы определить, должно ли поле быть текстовым, целочисленным, двойным, денежным и т. д. Эта функция может значительно сэкономить время, если у вас много полей. Иногда, когда я создаю новую базу данных, я делаю макет нескольких записей в Excel и импортирую электронную таблицу только для того, чтобы сэкономить время на создании и присвоении имен таблицам. Кроме того, если вы работаете с новичками в области баз данных, им часто удобнее работать в Excel. Если вам нужно получить данные от кого-то, кто не знаком с Access, вы можете дать им рабочий лист Excel и попросить их ввести данные с помощью пользовательского интерфейса Excel, где им удобно. Затем вы можете взять электронную таблицу и импортировать данные в базу данных Access.

Как вы видели на рис. 4-10, вы можете импортировать данные в существующую таблицу. Однако, если электронная таблица не предназначена для работы с существующей базой данных, вы часто будете работать с таблицами базы данных, которые могут не иметь одинаковых имен полей (столбцов) и типов данных. Например, центр затрат в Excel может быть числом без начальных нулей.
, в то время как тот же столбец в Access может быть текстовым и содержать начальные нули.

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

Еще один простой способ поместить данные из Excel в Access — использовать команду «Копировать» в Excel, а затем, когда таблица открыта в Access, использовать команду «Правка» — «Вставить и добавить» из Access. (Если вы попытаетесь использовать команду вставки, Access выдаст сообщение об ошибке.) Возможно, вы обнаружите, что команда «Вставить и добавить» по-прежнему выдает ошибки. Однако Access создает таблицу ошибок вставки, которую вы можете просмотреть.

Вам следует внимательно просмотреть ошибки, чтобы убедиться, что ваши таблицы не являются слишком ограничивающими. Например, вы можете найти поле в базе данных, указанное как обязательное, когда есть случаи, когда оно может быть пустым. Кроме того, вы также хотите определить, что важнее: наличие некоторых данных для всех строк или наличие всех данных для некоторых строк. Меня просили поработать над многими проектами, где целостность данных была проблемой, и такие вещи, как отсутствующие записи о клиентах, отсутствующие записи о счетах и ​​т. д., были проблемами. Часто возникали проблемы с данными, из-за которых записи не загружались. Хотя эти проблемы также можно было бы обнаружить, просматривая журнал, иногда проще ослабить ограничения, чтобы вы могли загрузить имеющиеся данные, а затем использовать отчеты об исключениях, чтобы выяснить, где вам нужно заполнить пробелы в данных.

Вы также можете столкнуться с непредвиденными логическими ошибками при импорте или вставке данных из Excel в Access. В некоторых случаях Access выполняет импорт, не замечая ошибки. Это особенно большая проблема, когда вы непреднамеренно загружаете число с десятичными знаками (двойными) в поле Integer. При импорте данных следует искать следующие распространенные ошибки:

.
Импорт чисел типа double в поле с размером поля integer

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

Текст на листе Excel содержит больше символов, чем позволяет размер поля

Эта проблема важна, потому что вы можете потерять данные. Я много раз сталкивался с подобными проблемами, особенно с полями почтового адреса.

Пробелы в конце текста

Эта проблема часто возникает, когда файл Excel был создан как выдержка из мейнфрейма или другой базы данных. Некоторые приложения базы данных будут записывать пробелы в конце каждого поля, пока оно не достигнет размера поля. Это вызовет проблемы, если вы попытаетесь объединить это поле с другим. Использование функции TRIM Trim([FieldName]) в запросе на обновление позволяет быстро справиться с этой задачей.

Дублирование первичных ключей

Иногда эта ошибка сообщает о наличии проблем с данными, но в других случаях вы обнаружите, что то, что вы считали первичным ключом, на самом деле им не было.