Импорт данных из CSV файла
Формат CSV удобен для импорта данных из внешних систем по нескольким причинам. Во-первых, его легко формировать программно без использования дополнительных библиотек. Во-вторых, сформированные данные можно просмотреть в табличном виде, исправить и дополнить. Как крайний случай, данные для импорта могут вообще готовиться вручную в программе CSVed -- бесплатном редакторе CSV файлов.
Универсальный алгоритм импорта выглядит следующим образом:
- Данные считываются из файла построчно.
- Каждая строка разбирается, из нее выделяются значения полей и помещаются в массив. В процессе разбора проверяется:
- Соответствие общего количества полей заданной спецификации.
- Соответствие значения каждого поля заданному типу и размеру.
- Соответствие данных в строке заданным правилам.
- В случае нарушения указанных выше условий процедура импорта прекращается с информированием пользователя.
- В случае успеха разбора строки данные помещаются в базу данных.
- Весь процесс осуществляется на одной транзакции, которая подтверждается только если все строки из исходного файла успешно помещены в базу данных.
Формат CSV файла
Для данных действуют следующие правила:
- В качестве разделителя полей используется символ ";"
- Для вещественных чисел используется "." в качестве разделителя целой и дробной части.
- Для отрицательных чисел пробел между знаком "-" и самим числом не допускается.
- Дата передаётся в формате "дд.мм.гггг".
Если последнее поле в строке содержит пустое значение или пробел (пробелы), то его обязательно следует закрыть точкой с запятой. В противном случае данное поле не будет учтено и в процессе импорта возникнет сообщение об ошибке. Пример, в котором возникнет ошибка:
A;B;C D;E;
Во избежание разночтений, рекомендуется всегда после последнего поля ставить точку с запятой. Соответствующим образом скорректируем приведенный пример:
A;B;C; D;E;;
Вспомогательные функции импорта
Центральной функцией является I2W_Parse, которая разбирает переданную строку и помещает значения полей в массив. Функция возвращает код успеха, ошибки или номер поля, тип данных которого не соответствует спецификации. Спецификация передается массивом, где параметры каждого поля задаются массивом из четырех элементов: имя, тип данных, размер и признак обязательного заполнения. Т.е. структура спецификации -- это массив массивов. Преобразование данных полей из строк к нужному типу осуществляется функциями: I2W_Str2Date, I2W_Str2Num, I2W_Str2Int, I2W_Str2Cur.
Так как строковое поле может быть обрамлено в двойные кавычки и содержать в себе символ разделитель (в нашем случае ";"), для разбиения исходной строки из файла мы используем свою функцию I2W_Split вместо стандартной Split.
Для обращения к полям по имени, а не индексу в массиве значений, используется функция I2W_FieldByName.
Вспомогательная процедура I2W_Log используется для записи информации о ходе процесса в лог системы.
Функция I2W_GetID находит в базе данных объект по заданному значению поля и возвращает его идентификатор. Поиск по строковым полям осуществляется без учета регистра символов, концевых и начальных пробелов. Если объект не найден, то он создается и его поля инициализируются переданными значениями.
Подробные комментарии для каждой функции приведены непосредственно в исходном коде:
'#include TCREATOR Option Explicit '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' На вход передается строка с датой в формате дд.мм.гггг '' Сконвертированная дата помещается в переменную D '' Функция возвращает True если конвертация прошла успешно Function I2W_Str2Date(S, ByRef D) S = Trim(S) If S = "" Then D = Null I2W_Str2Date = True Else I2W_Str2Date = False Dim A A = Split(S, ".") If UBound(A) = 2 Then If IsNumeric(A(0)) And IsNumeric(A(1)) And IsNumeric(A(2)) Then If ((A(0) >= 1) And (A(0) <= 31)) And _ ((A(1) >= 1) And (A(1) <= 12)) And _ ((A(2) >= 1900) And (A(2) <= 2999)) Then D = DateSerial(A(2), A(1), A(0)) I2W_Str2Date = True End If End If End If End If End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' На вход передается строка с вещественным числом '' В качестве десятичного разделителя используется точка. '' Сконвертированное значение помещается в переменную N. '' Функция возвращает True если конвертация прошла успешно Function I2W_Str2Num(S, ByRef N) S = Trim(S) If S = "" Then N = Null I2W_Str2Num = True Else Dim P If InStr(S, ".") > 0 Then P = Len(S) - InStr(S, ".") S = Left(S, Len(S) - P - 1) & Right(S, P) Else P = 0 End If If IsNumeric(S) Then N = CDbl(S) / (10 ^ P) I2W_Str2Num = True Else I2W_Str2Num = False End If End If End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' На вход передается строка с числом '' В качестве десятичного разделителя используется точка. '' Сконвертированное значение приводится к типу Currency и '' помещается в переменную N. '' Функция возвращает True если конвертация прошла успешно Function I2W_Str2Cur(S, ByRef N) If I2W_Str2Num(S, N) Then if not IsNull(N) then N = CCur(N) end if I2W_Str2Cur = True Else I2W_Str2Cur = False End If End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' На вход передается строка с целым числом '' Сконвертированное значение помещается в переменную I. '' Функция возвращает True если конвертация прошла успешно Function I2W_Str2Int(S, ByRef I) S = Trim(S) If S = "" Then I = Null I2W_Str2Int = True Else I2W_Str2Int = False If IsNumeric(S) Then If Int(S) = CDbl(S) Then I = CLng(S) I2W_Str2Int = True End If End If End If End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' На вход передается строка в CSV формате и символ разделитель. '' На выходе -- массив значений Function I2W_Split(S, DelimChar) Const QuoteChar = """" ReDim Result(0) Dim I, B, E, CurrChar, InQuotes I = 0 B = 1 E = 1 InQuotes = False Result(0) = "" S = Trim(S) While E <= Len(S) CurrChar = Mid(S, E, 1) If CurrChar = QuoteChar Then If Mid(S, E + 1, 1) = QuoteChar Then E = E + 1 ElseIf Mid(S, E + 1, 1) = DelimChar Then If InQuotes Then CurrChar = "" InQuotes = False End If ElseIf E = B Then InQuotes = True CurrChar = "" End If ElseIf (CurrChar = DelimChar) And (Not InQuotes) Then I = I + 1 ReDim Preserve Result(I) Result(I) = "" B = E + 1 CurrChar = "" End If Result(I) = Result(I) & CurrChar E = E + 1 WEnd If (UBound(Result) > 0) And (Result(UBound(Result)) = "") Then ReDim Preserve Result(UBound(Result) - 1) End If I2W_Split = Result End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' На вход передается строка в CSV формате и массив FieldDef '' с описанием полей. Каждый элемент массива -- это массив '' из4-х элементов: '' 1 -- имя поля (справочно, в самой функции не используется) '' 2 -- тип поля, "S" -- строка, "D" -- дата, '' "F" -- вещественное число, "C" -- денежный тип, '' "I" -- целое число. '' 3 -- максимальная длина строки. Для остальных типов '' не используется. '' 4 -- булевский признак обязательного поля. '' Функция возвращает значения: '' -99 -- если строка разобрана успешно и значения помещены в '' массив ArrOutput '' -1 -- количество значений в строке не совпадает с количеством '' элементов в массиве ArrOutput '' положительное число -- номер поля для которого выявлено '' несоответствие типов данных, или длина для строковых значений '' превышает максимальную заданную длину, или указан признак '' обязательного поля а в исходной CSV строке для этого поля '' передана пустая строка. Function I2W_Parse(S, ByRef FieldDef, ByRef ArrOutput) Dim I2W_Parse_Success, I2W_Parse_InvalidFieldCount I2W_Parse_Success = -99 I2W_Parse_InvalidFieldCount = -1 Dim ArrInput, I, V, F ArrInput = I2W_Split(S, ";") If UBound(ArrInput) <> UBound(FieldDef) Then MsgBox UBound(ArrInput) I2W_Parse = I2W_Parse_InvalidFieldCount Exit Function End If ReDim ArrOutput(UBound(FieldDef)) For I = 0 To UBound(ArrInput) F = False Select Case FieldDef(I)(1) Case "S" V = Trim(ArrInput(I)) If Len(V) > FieldDef(I)(2) Then V = Left(V, FieldDef(I)(2)) End If F = Not (FieldDef(I)(3) And (V = "")) Case "I" F = I2W_Str2Int(ArrInput(I), V) Case "F" F = I2W_Str2Num(ArrInput(I), V) Case "C" F = I2W_Str2Cur(ArrInput(I), V) Case "D" F = I2W_Str2Date(ArrInput(I), V) End Select If F Then ArrOutput(I) = V Else I2W_Parse = I Exit Function End If Next I2W_Parse = I2W_Parse_Success End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' Функция возвращает значение поля. '' На вход передается имя поля, массив массивов со спецификациями '' полей и массив со значениями полей. '' Если поле с указанным именем не найдено возвращается Empty. Function I2W_FieldByName(AName, ByRef AnArray, ByRef AValues) Dim I For I = 0 To UBound(AnArray) If AnArray(I)(0) = AName Then I2W_FieldByName = AValues(I) Exit Function End If Next I2W_FieldByName = Empty End Function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' Процедура записывает информацию в лог платформы Гедымин. '' Запись осуществляется на переданной транзакции. Sub I2W_Log(ByRef ATransaction, AClassName, AnID, AData) Dim Creator, q, Tr Set Creator = New TCreator Set q = Creator.GetObject(nil, "TIBSQL", "") If Assigned(ATransaction) Then q.Transaction = ATransaction Else Set Tr = Creator.GetObject(nil, "TIBTransaction", "") Tr.DefaultDatabase = IBLogin.Database Tr.StartTransaction q.Transaction = Tr End If q.SQL.Text = "INSERT INTO gd_journal (source, objectid, data) " &_ "VALUES (:s, :id, :d)" q.ParamByName("s").AsString = AClassName q.ParamByName("id").AsInteger = AnID q.ParamByName("d").AsString = AData q.ExecQuery If Not Assigned(ATransaction) Then Tr.Commit End If End Sub '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '' Функция ищет объект в базе данных по заданному значению поля. '' Если объект не найден, то он создается. '' На вход передается транзакция, имя класса и подтип объекта, '' имя поля для поиска и значение для поиска. '' Два массива (AFields и AValues) содержат имена полей и значения '' полей, которые будут использованы при создании нового объекта. '' Функция возвращает ИД найденного или созданного объекта. '' Через параметр Found возвращается булевский признак: '' True -- объект был найден '' False -- объект был создан Function I2W_GetID(ByRef ATransaction, AClassName, ASubType, ASearchField, _ ASearchValue, AFields, AValues, NeedUpdate, ByRef AFound) I2W_GetID = Null AFound = False If IsNull(ASearchValue) Then Exit Function If ASearchValue = "" Then Exit Function Dim Creator, Obj, I Set Creator = New TCreator Set Obj = Creator.GetObject(nil, AClassName, "") Set Obj.ReadTransaction = ATransaction Set Obj.Transaction = ATransaction Obj.SubType = ASubType If IsDate(ASearchValue) Then Obj.ExtraConditions.Add(ASearchField & "=:SearchValue") Obj.ParamByName("SearchValue").AsDateTime = ASearchValue ElseIf IsNumeric(ASearchValue) Then Obj.ExtraConditions.Add(ASearchField & "=:SearchValue") Obj.ParamByName("SearchValue").Value = ASearchValue Else If UCase(ASearchField) = "NAME" Then ASearchField = "z." & ASearchField End If If UCase(ASearchField) = "USR$NAME" Then ASearchField = "z." & ASearchField End If Obj.ExtraConditions.Add("UPPER(" & ASearchField & ")=:SearchValue") Obj.ParamByName("SearchValue").AsString = UCase(ASearchValue) End If Obj.Open If Obj.EOF Then Obj.Insert For I = 0 To UBound(AFields) If Err.Number <> 0 Then Exit For Obj.FieldByName(AFields(I)).Value = AValues(I) Next If Err.Number = 0 Then Obj.Post I2W_GetID = Obj.ID I2W_Log ATransaction, AClassName, Obj.ID, "Импорт: Создан объект." Else Obj.Cancel End If Else I2W_GetID = Obj.ID if NeedUpdate then Obj.Edit For I = 0 To UBound(AFields) If Err.Number <> 0 Then Exit For Obj.FieldByName(AFields(I)).Value = AValues(I) Next If Err.Number = 0 Then Obj.Post I2W_GetID = Obj.ID I2W_Log ATransaction, AClassName, Obj.ID, "Импорт: Изменен объект." Else Obj.Cancel End If else I2W_Log ATransaction, AClassName, Obj.ID, "Импорт: Найден объект." end if AFound = True End If End Function