Использование запросов для импорта данных из нескольких TXT файлов

[ВикторAK]

Здравствуйте! Помогите разобраться.
Казалось бы, не сложная задача, но не могу победить ее с помощью Либры.

Есть несколько ТХТ файлов, содержимое которых периодически пополняется.
В MS Excel задуманное делается просто - получить даные из файла (рис.1) - открывается редактор power query (рис.2) - закрываем/сохраняем и всё.
Если потом открыть сохраненный xlsx файл и нажать Обновить всё (рис.3) то со всех подключенных ТХТ файлов подгружаются данные.

Как, и можно ли в принципе, подобное реализовать в Calc'e?

[eeigor]

Подключите ваши файлы через Base и импортируйте в Calc в диапазоны базы данных. Обновление - небольшим макросом. Как-то так... @economist Вам лучше подскажет.

[ВикторAK]

Надеялся что смогу обойтись без Base и макросов Спасибо за наводку

[eeigor]

Да нет, это просто достаточно и надёжно. Ну, нет здесь Power Query и языка программирования "M".

Updated: Сравнение Excel и Calc в данном случае не уместно. Один Excel по объему тянет больше, чем весь LibreOffice.

[ВикторAK]

Подключите ваши файлы через Base

Уже на данном этапе возникает заминка.
Может не правильный запрос в гугле задаю, но получается только через Calc открыть ТХТ файл, потом скопировать его содержимое и вставить в Base...
Через диалог "Мастер источников даных" добавил папку с файлами ТХТ, но далее можно работать только с одним файлом...
Подскажите куда копать дальше, чувствую себя слепым котенком, гайды с созданием таблиц с учениками или покупателями не помогают.

[eeigor]

Текстовые файлы (csv) обновляются, как вы сказали, из внешней программы. Base (файл data.odb) подключен к папке с этими файлами. Таблицы Base методом Drag&Drop я перенес в файл Calc (файл data.ods), в результате чего были созданы два диапазона базы данных с именами "Import1" и "Import2". Если исходные данные изменились, то их можно обновить макросом ниже. Макрос можно подвесить на открытие файла.

Код Выделить Развернуть

Sub RefreshAllDBRanges
Dim oDBRanges As Object, oDBRange As Object

oDBRanges = ThisComponent.DatabaseRanges
For Each oDBRange In oDBRanges
oDBRange.refresh
Next
End Sub

UPD:
Base подключен к папке с файлами через относительный путь, работает. А вот файл Calc data.ods - через абсолютный путь. Эту проблемку надо решить, чтобы не проводить импорт заново.

UPD:
См. ниже: Edit Database Link

[eeigor]

Я не очень разбираюсь в Base. Вам лучше подскажет @economist.
Ctrl+Shift+F4  Shows or Hides the Database explorer

[ВикторAK]

Спасибо, буду пробовать по вашему примеру

[eeigor]

Выполните макрос "ImportDatabaseTables". Будет создан новый файл ODS, и каждая таблица в источнике данных (зд. под именем "data", см. скриншот, а сам источник связан с конкретной базой данных - зд. "data.odb") будет импортирована на отдельный лист. Источник данных надо предварительно зарегистрировать.

Код Выделить Развернуть

REM  *****  BASIC  *****

Rem Replace with your actual data source name.
Const REGISTERED_DATABASE$ = "data"  'registered name of database link

Sub ImportDatabaseTables()
Dim oDBCtx As Object, oDataSource As Object, oConnection As Object
Dim aTableNames$()

oDBCtx = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = oDBCtx.getByName(REGISTERED_DATABASE)
oConnection = oDataSource.getConnection("", "")
' NOTE: .getConnection("user", "password") for a real database log-in.
aTableNames() = oConnection.Tables.ElementNames

Dim oDoc As Object, oSheets As Object, oDBRanges As Object, oDBRange As Object
Dim oCellRange As Object, oAddr As Object  'New com.sun.star.table.CellRangeAddress
Dim sTableName$, sDBRangeName$

oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_default", 0, Array())
oSheets = oDoc.Sheets
oDBRanges = oDoc.DatabaseRanges
' Add additional sheets as needed according to the number of tables in oDataSource.
For i = oSheets.Count To UBound(aTableNames())
oSheets.insertNewByName("Sheet" & i + 1, i + 1)
Next

Dim aDescriptor(3) As New com.sun.star.beans.PropertyValue  'import descriptor
' Import source tables.
For i = 0 To UBound(aTableNames())  'oSheets.Count - 1
sTableName = aTableNames(i)
sDBRangeName = "Import" & i + 1
oSheets(i).Name = sTableName

oAddr = createUnoStruct("com.sun.star.table.CellRangeAddress")
oAddr.Sheet = i
If Not oDBRanges.hasByName(sDBRangeName) Then
oDBRanges.addNewByName(sDBRangeName, oAddr)
End If
Rem oDBRange = oDBRanges(sDBRangeName)  '#Err!
oDBRange = oDBRanges.getByName(sDBRangeName)  'oDBRanges(i)
With oDBRange
' .ContainsHeader = True  'Contains column labels (default is True)
' .TotalsRow = False  'Contains totals row (default is False)
.MoveCells = True  'Insert or delete cells (default is False)
.KeepFormats = True 'Keep formatting (default is False)
' .StripData  = False  'Don't save imported data (default is False)
End With
oCellRange = oDBRange.ReferredCells

' Set the import descriptor properties.
aDescriptor(0).Name = "DatabaseName"  'registered name of database link
aDescriptor(0).Value = REGISTERED_DATABASE
aDescriptor(1).Name = "SourceType"
aDescriptor(1).Value = com.sun.star.sheet.DataImportMode.TABLE  '2=TABLE
' Set the source import descriptor table.
aDescriptor(2).Name = "SourceObject"
aDescriptor(2).Value = sTableName
'aDescriptor(3).Name = "IsNative"  'specifies whether the SQL statement is given...
'aDescriptor(3).Value = False      '...directly to the database or is parsed before

oCellRange.doImport(aDescriptor)

' Unlink the database range if you like.
'oDBRanges.removeByName(sDBRangeName)
Next
oConnection.close()
End Sub


Процедура "RefreshAllDBRanges" обновит все диапазоны базы данных в электронной таблице (должна находиться в созданном файле с уже импортированными таблицами). Повесьте её на событие "Open Document", чтобы данные обновлялись при открытии автоматически.

Код Выделить Развернуть

Sub RefreshAllDBRanges()
''' Called from "Open Document" event.
'''
Dim oDBRangesEnum As Object, oDBRange As Object

oDBRangesEnum = ThisComponent.DatabaseRanges.createEnumeration()
Do While oDBRangesEnum.hasMoreElements()
oDBRange = oDBRangesEnum.nextElement()
oDBRange.refresh()
Loop

' For Each oDBRange In ThisComponent.DatabaseRanges
' oDBRange.refresh()
' Next
End Sub


UPD:
В ходе работы замечена ошибка при вызове метода getByName() неявным способом:
oDBRange = oDBRanges.getByName(sDBRangeName)  'работает верно
oDBRange = oDBRanges(sDBRangeName)  '#Err! Неверно получает ссылку на диапазон базы данных
BUG: при смене в цикле имени таблицы на следующую объект oDBRange по-прежнему указывает на первую таблицу.

Или:
oDBRange = oDBRanges.getByIndex(i)  'работает верно
oDBRange = oDBRanges(i)  'работает верно

[economist]

После языка M - Python+Pandas для самой быстрой из всех возможных загрузок кучки TXT в Calc - окажутся лучшим решением: https://forumooo.ru/index.php/topic,8696.0.html  Но лучшее обычно оставляют на потом.

А если "по классике", с TXT из папки через Base - по Ctrl+Shift+F4 в Calc вы увидите каждый файл в виде отдельной таблицы. Перетащите каждую (за серый прямогольник слева) на разные листы Calc - и вы получили самообновляемые диапазоны в листах, по числу файлов, без к-либо макросов. Обновление при открытии или ручное - настраивается.  

Для обновления (перезаписи поверх) TXT-шек из вашего прикладного ПО (условно 1С) по время активного подключения Calc к TXT - нужно чтобы файлы имели атрибут read only - тогда их перезапись Calc не заблокирует. Атрибуты можно присвоить простым bat-скриптом, или еще более простым python-скриптом. Питон "родной", из папки LO.  

UPD:

все  TXT д.б. в одной кодировке, с одним разделителем тысяч и дробной части, с одним форматом даты, с одним (с или без) заголовком столбцов в 1-й строке итд. Для 1С - это почти нереальная ситуация. Если у вас "разнобой" TXT-форматов  - можно попытать счастья с ISAM-драйвером и файлом описания схемы schema.ini - это просто и надежно работает под Windows. Вообще читать стопку TXT как базу данных под Windows можно 5-ю способами, и все из коробки. Другое дело что дальнейшая обработка данных - определяет то, чем лучше было бы делать импорт "разношерстных" данных. Но это по ситуации.