База на основании XLS-файла

Автор adi_den2013, 6 сентября 2017, 15:41

0 Пользователи и 1 гость просматривают эту тему.

adi_den2013

Всем привет! Поделитесь опытом, кто в теме  :)

Есть выгрузка из "Паруса" - XLS-файл. На этом файле создала БД (буду дальше делать формы и  таблицы). Всё получилось см. скрины. Нет ли подводных камней при такой организации работы?



ПС. Open Office 4.1.3
Яна (in real)

economist

#1
Камней нет, но камешки - могут быть, зависит от сценария использования ODB-базы:

1) в XLS/ODS очень желателен именованный диапазон, который будет автоматически являться "таблицей". Парус его сам не создаст. Если диапазона нет - желательно доработать форму отчета в Парусе так, чтобы первая строка - были заголовки столбцов, а сама таблица - плоская. Следует помнить что OpenOffice|LibreOffice Base "просматривает" первые 10 строк таблицы и по ней определяет тип (целое число, двойной точности, строка или дата). Если значений никаких до 11-й строки нет - есть вероятность что тип столбца будет определен неверно. Если хочется делать SQL-"соединения" таблиц (JOIN и тп) с разных листов - несовпадение типов может привести к тому что часть запросов внезапно перестанут давать верный результат. Строить систему отчетов для шефа я бы на таком инструментарии не стал бы.    

2) XLS/ODS "открывается" в Base режиме в ReadOnly. То есть вносить правки в строки таблицы (менять ячейки), создавать новые табл. и представления - не получится. Это сильно ограничивает возможности БД (и будет вас тормозить в развитии!). XLS/ODS-файл придется перезаписывать каждое, скажем, утро - целиком, для "обновления" и при этом сам ODB-файл базы должен быть закрыт, иначе перезапись не удастся. При работе по сети нескольких людей это может повлечь трудности орг. характера.    

3) ODB с линковкой к XLS/ODS - уместен для простых циркулярных рассылок, как ReadOnly-база, на посмотреть, не более. Вы столкнетесь с тем что часть агрегирующих и просто SQL-функций (SUM, COUNT, AVERAGE итп) - при группировках GROUP BY работать не будет, а документация будет врать об обратном. Зато будут работать те функции, которые даже не упомянуты. По себе знаю как это неприятно.

4) Впрочем, можно к XLS-файлу подключиться в BASE другим способом - через ODBC ISAM-"прослойку". Но придется запустить Пуск-Панель упра...-Администри..-Источники данных ODBC. Здесь все то же самое (те же проблемы), но чуть поменьше и наполовину решаемые, особенно если данные из XLS понадобятся в MSO Excel, Word, OpenOffice|LibreOffice Writer, Calc на многих компьютерах сети одновременно. Но это путь тоже полон грядущих открытий и разочарований.

Если хочется брать нормально данные и работать со всей мощью языка SQL - придется отказаться от "движка" BASE с названием HSQLDB (и нэйтивных, "родных", т.е. OLO-шных коннекторов к файлам ODS/XLS, которыми вы уже воспользовались) - в пользу простых "промышленных" СУБД SQLite через ODBC (100% лучшее решение, вообще говоря - сказка!) или СУБД FireBird Embedded (встроен в LibreOffice Base, статус - экспериментальный, но почти отличный). Это два самых простых варианта. Еще есть посложнее - другие СУБД проф-уровня, но уже требующие сетевого администрирования сервера базы данных: MySQL, PostgreSQL. От "закрытых" форматов СУБД типа Access - отговорю, хоть и делал на них коммерческий продукт.


Как вы поняли - "оболочка" данных во всех этих случаях останется та же - OpenOffice|LibreOffice Base. И она по-своему прекрасна - подсветка синтаксиса, размер шрифта, фильтры таблицы - все сделано на твердую "4+". Сами же данные (буковки в таблицах) - могут попадать из "Паруса" в СУБД как угодно.

Лучше всего обмениваться через TXT-файлы, с разделителем колонок - табуляцией. У этого "символа" есть одно уникальное свойство: вручную его, с клавы, ввести невозможно. Насколько я знаю - любую таблицу, отчет итп из 1С, Паруса, Галактики, Турбо-бух, ДебетПлюса итп - можно сохранить именно в такой в TXT формат.

Чем его "засосать" в СУБД проф-уровня, чтобы в BASE писать запросы? - вариантов много: макрос/скрипт в Base на StarBasic или "снаружи" -  VBS, VBA, BAT, Python, PowerShell, Bash, консольные  утилиты типа sqlite3.exe итп.

Мне нравится Python, потому что он в 5-10 раз быстрее всех остальных способов и даже тех консольных утилит которые написаны на быстрющем языке C. И на нем программируется со скорость в 4-5 раз быстрее, чем на других языках. 

Спрашивайте что непонятно, ваше желание изучать и использовать BASE очень похвально.      
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

adi_den2013

Цитата: economist от  6 сентября 2017, 13:23Спрашивайте что непонятно, ваше желание изучать и использовать BASE очень похвально.

Спасибо за развёрнутый ответ. Информации много - буду "переваривать".

Странно, но выгрузку из Паруса в TXT-формат я не видела... Есть экспорт в XML, но он какой-то специфический. Я пробовала программы перекодировки и во всех идёт ошибка нарушения целостности базы.
Яна (in real)

economist

Выгрузку в XML люди придумали для компьютеров, чтобы компьютерам было легче, а людям - труднее.
"Паруса" у меня нет, но уверен что при видимой таблице "Файл-Сохранить как-<список форматов>" - даст на выбор не только XLS-формат. Перечислите что там есть рабочего. XLS/XML/XHTML/HTML - неудобные форматы. Часто "экспорт/выгрузка" запрятаны в другом месте, спросите коллег, айтишников.     
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

adi_den2013

Спрашивать могу только лишь у себя) Обращаться к "Парусникам" себе дороже, в смысле предприятию. Помощь к программе вещает нижеследующее (см. скрины)


Отчеты в шаблонах XLS. Но база VisualFoxpro, так что буду "ваять" выгрузку в CSV с разделителями мимо кассы Паруса.
Яна (in real)

economist

Если Парус <=7 - то база в DBF-файлах, это просто прекрасно!

Разыщите в Сети схему базы данных (в каких таблицах что хранится). OpenOffice|LibreOffice Base умеет подключаться к папке с DBF-ами, для этого их можно тупо скопировать (нужные), даже если они открыты ядром программы. 
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

adi_den2013

Цитата: economist от  7 сентября 2017, 14:19OpenOffice|LibreOffice Base умеет подключаться к папке с DBF-ами

Да Парус версии 7.4
Буквально полчаса назад меня тоже осенило: зачем кордебалет с выгрузкой, когда есть ODBC к dbf-никам.  Зашла отписаться в теме, а тут уже всё написано  :)

Такое вот чУдное совпадение  ;)
Яна (in real)

economist

И все же, пятнично-догонячечно: 
1) Файл XML должен корректно открываться в MS Excel и O/LO Calc, с нажатием на кнопку "Создать схему" и игнором ошибок. В аду авторам стандарта XML отвели участок сковородки с тегами по краям.

2) DBF-ы тоже имеют свои минусы. OpenOffice|LibreOffice подключается к ним с возможностью правки RW, нужно действовать осторожно. Скорее всего к работающей базе Паруса, когда кто-то подключен - законнектиться не удастся и наоборот. Если есть возможности написать выгрузку в CSV - делайте! Это свобода ото всех оков. Только разделитель - Tab и ничто другое! У мя большой в прошлом опыт по связыванию, смогу, есичо, подсказать.

3) к DBF-ам есть и ODBC в 3-х вариантах, и нэтивный, из Base, доступ. Пробуйте все, очень много нюансов. В ODBC лучше всего работал драйвер DBASEIII
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

adi_den2013

Сэнкс :beer:! Буду делать выгрузку в CSV.  По крайней мере можно конкретно указать желаемые поля, а не тянуть всё (там много мне ненужного).
Яна (in real)

economist

#9
Как выгрузите файл в CSV (TSV - tab separated values) - создайте ODB в формате HSQLDB. А в ней создаете вручную таблицы (обязательно с первичным ключом!!!). А затем - делаете "линковку" этих таблиц к CSV-файлам, выполняя через Сервис...-SQL-Выполнить... запрос вроде такого:

SET TABLE "Таблица" SOURCE "Таблица.txt;ignore_first=true;fs=\t;encoding=OEM"

Пока это самый короткий путь текстового файла в SQL-базу данных. Таблицы будут отзывчивые, работают на запись изменений, кириллица и пробелы в именах полей, именах таблиц и значениях - всё  это работает хорошо, возможна многопользовательская работа, если создать пользователей (и первичные ключи, естественно, во всех таблицах!)

По ощущениям и скорости работы это будет "почти" Access, слинкованный с TXT - очень популярная схема в 2000-ные. Даже индексы можно создать. Кстати, "есливинда" - можете попробовать в BASE движок не HSQLDB, а встроенный или через ODBC - JET/ACE, который и есть на самом деле "движок" Access, который есть на любой Windows (сам Access и вообще Microsoft Office не нужен).  

Но есть и минусы у HSQLDB, о которых "невозможно молчать":
HSQLDB написана на JAVA, быстра, но при подключении к папке с TXT, импорте TXT или связывании ее с TXT - функции SUM, GROUP BY, Upper итд. как-то странно работают с кириллицей (в полях и значениях), функции перед выражением FROM не поддерживаются, трэба яная группировка всех полей (запросы с группировкой - многословнее чем в других СУБД на 30-40%). Есть нечастые глюки и непредсказуемость в работе, зависания, невозможен общий доступ к базе по сети в тот момент, когда у кого-то открыта таблица или выполнен запрос и его результат отражается на экране, запуск в "серверном" режиме непрост и нестабилен (но но Форум раскрыт). Диагностические сообщения HSQLDB могут поставить в тупик кого угодно. Ошибки при выполнении запроса выдаются частенько "кракозяблами".

Но есть такая волшебная СУБД SQLite - в BASE (вместо HSQL и линковки с CSV) - лишенная всех недостатков вообще. Она будет лучше и быстрее всего, что только может быть в природе, даже при сетевом файловом доступе 3-5 человек.

Если пользователей будет больше или слабая сетка (100 Мбит/сек) - то желательно посматривать в сторону тяжелых серверных СУБД (FireBird, MySQL, PostgreSQL). У тех ограничений нет никаких.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

adi_den2013

Цитата: economist от  8 сентября 2017, 08:36возможна многопользовательская работа, если создать пользователей

Можно об этом немножко поподробнее  :roll:

Я занималась этим немного вначале года, но потом необходимость в задаче отпала и всё застопорилось. Вот моя ветка обсуждения http://forumooo.ru/index.php/topic,6210.0.html
Яна (in real)

adi_den2013

Гммм...

Что-то не видит команда мою таблицу  ???
Яна (in real)

economist

#12
При линковке BASE пытается "прочесть" весь текст, и все должно быть гладко!

1) Сама таблица д.б. типа TEXT, то есть создана SQL-конструкцией, а не конструктором:  
CREATE TEXT TABLE <tablename> (<column definition> [<constraint definition>])

2) fs=\t - означает что разделитель табуляция. В person.txt именно так?

3) Кодировка файла? WIN=1251. DOS=OEM=866. UTF-8=65001

4) Мурзилка: http://hsqldb.org/doc/1.8/guide/guide.html#texttables-chapter
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

adi_den2013

Спасибо!


Создала таблицу SQL-конструкцией. Теперь при связи ругается по-другому: java.lang.IllegalArgumentException: null in statement . Где же тут нулевое значение  ???

На скрине Image006- тестовая запись, Image007- структура таблицы и собсно сама ошибка Image008.
Яна (in real)

mikekaganski

Предположения (не имею опыта, сорри если не в тему):
1. Нет ли пустой второй строки, которая могла бы быть интерпретирована как имеющая пустое значение ключевого поля (см. Scope and Reassignment - "At this point any violation of NOT NULL, UNIQUE or PRIMARY KEY constrainst are caught and the assignment is aborted")?
2. Нет ли тут проблемы, что в файле нет строки с описанием столбцов (м.б. implementation-specific requirement)?
С уважением,
Михаил Каганский