Подготовка файла Excel к нормализации

Автор: | 01.06.2020

Для  создания нормализованной базы данных 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. Я лично предпочитаю по привычке поля на латинице, звучащие по русски.