Delphi. Перенос данных из MS Excel в базу MySQL. Динамические массивы.

02.09.2016

Работая с такими БД как MSSQL 2005 и Oracle 9.2 не возникает проблем с копированием данных из Excel.
Достаточно открыть таблицу для редактирования (для MSSQL использовал родной SQL Server Management Studio, а для Oracle — PL\SQL Developer) и простым copy-paste перенести данные из экселя, исключением было лишь то, что для вставки в MSSQL нужно было предварительно добавить в начало Excel-таблицы два пустых столбца…
MySQL-же ведет себя по-другому..
Возможно что есть редакторы позволяющие так-же copy-paste перенести данные, но пользуясь  dbForge Studio for MySQL выход нашелся только экспортом таблицы из БД, наполнение ее недостающими данными и импортом обратно…Согласитесь — не удобно..
Это подвигло меня на написание утилиты, позволяющей «на лету» переносить данные из MS Excel в БД MySQL…

Дабы сделать ее универсальной, независящей от структуры базы, применена следующая технология:
Первая строка excel-страницы (шапка таблицы) должна представлять собой наименования полей в таблице базы данных, которую наполняем.
Все что ниже — соответственно, значения этих полей. Так же тут не должно быть автоинкрементного поля, содержащегося в БД.
И еще… если в вашей таблице есть дробные значения — замените в них запятую на точку.

При выборе файла и далее страницы с данными, происходить подсчет строк и столбцов, считываются наименования полей из excel-файла. Имена полей помещаются в динамический массив. При обходе этого массива запрашиваются значения полей из excel-файла, сохраняя их в другой динамический массив.
Далее происходит вставка значений в БД. Она, к данному примеру, имеет такую структуру

Для работы программы необходимо заполнить поля для параметров соединения с БД, а файл Exel, из которого выгружаем данные — расположить в каталоге программы… Можно было сделать и независимое расположение эксель-файла , но мне просто было лень 🙂
Результатом работы будет наполнение БД:

Саму программу вы сможете скачать, если есть желание, по ссылке в конце странице ( Файл libmysql.dll необходим для соединения с MySQL.
Сжимать прогу не стал — закончился триал у ASPack  🙂 ), а пока рассмотрим некоторые операции более детально..

1. Создание и наполнение динамического массива

var
 poleArray : Array of String; // массив для хранения имен полей считываемых из эксель-файла
 znachArray : Array of String; // массив для хранения значений полей считываемых из эксель-файла
 //........
 // в примере приведенном в п.2 получено количество столбцов в переменную Columns
 for k:=1 to Columns do begin
 // объявляю длину одномерного массива в который помещаем имена полей
 SetLength(poleArray, Columns);
 // объявляю длину одномерного массива в который помещаем значения для полей
 SetLength(znachArray, Columns);
 // наполняю массив именами полей
 poleArray[k-1] := string(MyRange[1,k]);
 end;

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

 for j:=0 to Columns-1 do znachArray[j] := string(ZQuery1.Fields[j].Value);

Здесь подразумевается, что значения получаются запросом к эксель-таблице…
Теперь вернемся чуть назад и увидим как же получили переменную Columns (п.2) , и как проверили поля на типы (п.3)
2. Подсчет количества строк и столбцов в подключаемом exel-файле
Пропускаю процесс подключения файла, рассчитывая на то что вы умеете это делать  и просто приведу код.. ))

var
 x,Columns,rows,i,j:integer;
 tmp,str_znach,str,list,edittable,kat,pfile:string;
 MyRange,Cell_1,Cell_2,ExcelApp:OleVariant;
 //.....................
 rows := ExcelApp.Worksheets[x].UsedRange.Rows.Count-1;
 Columns := ExcelApp.Worksheets[x].UsedRange.Columns.Count;
 label10.Caption:='На листе '+inttostr(rows)+' строк';
 label11.Caption:='На листе '+inttostr(Columns)+' столбцов';
 Cell_1:=ExcelApp.Worksheets[x].Cells[1,1];
 Cell_2:=ExcelApp.Worksheets[x].Cells[1,Columns];
 MyRange:=ExcelApp.Worksheets[x].Range[Cell_1, Cell_2].value; 

Здесь переменная х — используется как номер листа…

3. Проверка значений полей на совместимые типы и преобразование.

 var
ti:integer;
td: TDate;
tr:real;
// ...........................
tmp:= string(ZQuery1.Fields[j].Value);
// проверка на дату
try
td:= strtodate(tmp);
tmp:=  FormatDateTime('yyyy.mm.dd',td) ;
znachArray[j] := tmp;
except
// проверка на INT
try
ti:= strtoint(tmp);
znachArray[j] := inttostr(ti);
except
// проверка на дробное
try
tr:= strtofloat(tmp);
znachArray[j] := floattostr(tr);
except
znachArray[j] := string(ZQuery1.Fields[j].Value);
end;
end;
end;

Скачать архив с программой можно по этой ссылке.

Комментарии

2 комментария к записи “Delphi. Перенос данных из MS Excel в базу MySQL. Динамические массивы.”
  1. Добрый день.
    Можно Вас попросить настроить перенос данных из exel в базу данных.
    Сам не силен, но если показать визуально, пойму.
    В настоящее время пишет
    Не введены или неверны настройки соединения с Mysql

    • MK:

      Вы сами ответили на вопрос: «Не введены или неверны настройки соединения с Mysql»
      Введите их…

Оставить комментарий