Для создания нормализованной базы данных Access сначала надо подготовить файл Excel, разделив отдельные атрибуты по отдельным столбцам.
Часто в одном столбце Excel находятся сразу несколько значений, разделённых пробелом (ФИО) или запятой, или точкой с запятой.
Разделитель в принципе может быть любой.
Пусть в первом столбце имеется такая информация – 10101000, Белгородская таможня.
Очевидно, что нам необходимо разделить эти данные, выделив отдельно номер и наименование. Разделитель у нас будет запятая.
В первом столбце Excel у нас окажется номер, а во 2-м столбце – наименование.
Пусть во 2-м столбце исходной таблицы мы имеем 308009 Г. БЕЛГОРОД, УЛ. ПОБЕДЫ, Д. 78А; (4722)27-16-44.
Очевидно, что эта ячейка содержит Адрес и Телефон. Разделителем является Точка с Запятой. Начальный столбец размещения информации будет 3-ий.
В общем случае безразлично, сколько значений находится в ячейке Excel, если есть способ каким-либо способом разделить данные в ячейке командами VBA.
Такая возможность есть.
Создадим массив Dim TextArrey().
Запомним в переменной а значение нужной ячейки командой a=cells(i,j), но лучше a=Trim(Cells(i,j)), где i и j переменные цикла по перебору строк и столбцов.
Команда для столбца 1 TextArray = Split(a, “,”) создаст массив из 2-х значений. Для столбца 2 аналогично можно использовать команду TextArray = Split(a, “;”),
Т.к. разделитель изменился.
Для выделения Фамилии, Имени и Отчества из ФИО разделителем будет пробел.
Иногда предварительно придется использовать и команду Replace. которая меняет одни символы на другие.
Эта команда особенно хороша в случае, если у Вас в ячейке имеются скрытые символы, например, переноса строки в ячейке. Этот перенос задаётся комбинацией клавиш ALT+ENTER. Другими способами убрать этот символ не получится. В VBA этот скрытый символ задается через CHR(10) или CYH(13). Можно проверить.
Чтобы программно продолжить разделение данных, можно использовать в цикле такой алгоритм
| For j = 1 To ColEnd |
| b = Trim(Cells(Row_Shapka, j)) ‘Запоминаем ячейку шапки |
| For i = Row1 To LastRow |
| Select Case j |
| Case 1 |
| Nomer = 1 ‘Начальный столбец преобразования колонки 1 для листа Норма1 |
| a = Trim(Cells(i, j)) ‘Запоминаем содержимое ячейки |
| TextArray = Split(a, “,”) ‘Разделяем по найденному символу в массив |
| For Counter = LBound(TextArray) To UBound(TextArray) ‘Перебираем элементы массива |
| a = Trim(TextArray(Counter)) |
| Sheets(“Норма1”).Cells(i, Nomer) = a ‘Переносим элемент массива на лист Норма1 |
| Call Stil(i, Nomer) ‘Применяем стиль к ячейке |
| Sheets(“Норма1”).Cells(Row_Shapka, Nomer) = b ‘Дублируем ячейку |
| Sheets(“Норма1”).Cells(Row_Shapka, Nomer).Interior.ColorIndex = 43 ‘Заливаем ячейку выбранным цветом |
| Nomer = Nomer + 1 ‘Увеличиваем номер столбца переноса данных |
| Next ‘ Конец переборки элементов массива |
Next
Next
Если продолжить разделение данных уже 2-го столбца, то Nomer=3, разделитель в команде Split – точка с запятой.
Норма 1 – это новый лист, на который переносятся старые данные после разделения данных ячейки столбца.
Шапка через переменную b дублируется на следующей столбец строки 2 шапки.
Подпрограмма создания стиля производит центрирование текста в ячейке и делает его жирным
Sub Stil(i, Nomer)
Sheets(“Норма1”).Cells(i, Nomer).HorizontalAlignment = xlCenter
Sheets(“Норма1”).Cells(i, Nomer).VerticalAlignment = xlCenter
Sheets(“Норма1”).Cells(i, Nomer).Font.Bold = True
End Sub
Если ФИО находится в одной ячейке с телефоном, то сначала разделяем ФИО и телефон, затем все данные копируем на новый лист Норма2 и для него создаем отдельный макрос, сначала вставляя пустые 3 столбца для хранения Фамилия, Имя и Отчество в отдельных столбцах. Если ФИО и номер телефона были в колонке 5 на листе Норма1, то вставляем 6,7 и 8 столбец.
Значение переменной Nomer будет равно 6, а значение столбца j – 5. Разделитель – пробел.
Файл Excel в формате 2003, содержащий макросы Raznoska1, Raznoska2, Sapka и вспомогательные макросы стиля и выделения. 2 последних макросы Private и не высвечиваются. Первый макрос делает разноску на лист Норма1, центрирует и расширяет поля. Второй макрос копирует данные с листа Норма1, вставляет колонки 6-8 после ФИО и разделяет ФИО по колонкам. Макрос Sapka копирует лист Норма2, удаляет строку 2 шапки и в первую строку добавляет наименования полей на русском языке для последующего их импорта в базу Access. Я лично предпочитаю по привычке поля на латинице, звучащие по русски.