Перенос данных из excel в sqlite
ESF Database Migration Toolkit is a powerful and high performance toolkit that can migrate data across various database formats, such as MS Excel, SQLite, etc.
This article describes how to quickly migrate data from MS Excel to SQLite by ESF Database Migration Toolkit! Help you complete complex database migration tasks and save a lot of time!
Software Required:
DMToolkit_x64.zip (68.4 MiB) | 64-bit Windows application for ESF Database Migration Toolkit 10.3.19 (2022-04-27). (md5: 25e64aa463463d9ed3ee7034c49a9162) |
DMToolkit_win32.zip (64.9 MiB) | 32-bit Windows application for ESF Database Migration Toolkit 10.3.19 (2022-04-27). (md5: 1cf3c81430c3a8d40fca865003405cce) |
System Supported:
- Windows 7 or higher.
- MS Excel 97 or higher.
- SQLite 2 or higher.
Introduction:
1. In "Choose a Data Source" dialog, Choose "Microsoft Excel(*.xls;*.xlsx)";
2. In "Choose a Destination" dialog, Choose "SQLite";
3. In "Select source Tables(s) & View(s)" dialog;
- Select the tables/views which will be migrated.
- Click ". " to set the table options or remap the table structure.
- You can set the data transfer method (Overwrite Table/Empty Data/Append Data/Skip Table) or filter the data before transferring.
- Choose "Field Mapping" option, you can redefine the fields in the destination table, e.g.: field name, data type, default value, comment and also.
4. In "Execution" Dialog;
- Click "Submit" to begin the migration, this toolkit will help you quickly migrate data from MS Excel to SQLite without intervention.
- Click "Browse Log" to visit the full migration log.
- Click "Save as job" to save the migration settings to a job file, so you can "Load Job" to quickly reload the migration job in the future or run the migration job via command-prompt. Run "esf-cmd --help" in command-prompt to get the full command parameters.
5. Finished!
The program will complete the migration as quickly as possible and generate a migration report to you. All you have to do is sit and wait. If you have any questions or suggestions, please feel free to contact us.
This is a Light weight Library to Convert SQLite Database to Excel and Convert Excel to SQLite.
The sample app in the repository is available on Google Play:
- Added the ability to provide a custom formatter to the export.
- Added the ability to exclude columns from the export.
- Added pretty name mapping to export
- Added support to add new column from excel while importing, if the column is not exists.
- Added Functionality to Import Excel into SQLite Database.
- Added Functionality to Export Blob into Image.
- Added Functionality to Export List of tables specified.
How to Download
add the following library in your app level gradle file
The steps to use this Library
Export SQLite to Excel
This line is used to save the exported file in default location.
This line is used to save the exported file in used preferred location.
This code snippet is used to Export a single table in a database to Excel Sheet
This following code snippet is used to Export a list of table in a database to Excel Sheet
This code snippet is used to Export a every table in a database to Excel Sheet
This code snippet shows how to exclude columns from the export (the resulting export file may not be able to be imported)
This code snippet shows how to pretty names (either sheet names or column names (the resulting export file may not be able to be imported)
This code snippet shows how to format the value for a column on export (if you want to convert ID's or whatnot to be better displayed)
Import Excel into Database
The following snippet is used to initialize the library for Importing Excel
Devart Excel Add-in for SQLite allows you to connect Microsoft Excel to an SQLite database, quickly and easily load data from an SQLite database to Excel, instantly refresh data in an Excel workbook from the database, edit these data, and save them back to the database. It enables you to work with SQLite data like with usual Excel worksheets, easily perform data cleansing and de-duplication, and apply all the Excel's powerful data processing and analysis capabilities to these data.
Connect to SQLite Directly from Excel
To connect Excel to an SQLite database you just need to specify the path to the database file.
Devart Excel Add-in for SQLite supports SEE, CEROD, SQLCipher, and SQLiteCrypt encrypted databases provided that you have the corresponding encryption extensions themselves.
Connection information may optionally be stored in the Excel file, or you may reenter it whenever necessary.
Use Query Builder or SQL to Import Data You Need
Visual Query Builder allows you to choose a table to import data from, select the columns from this table and related tables, and set filter conditions and data ordering options visually. If you need to select data more precisely, you can use SQL to select data you need.
Before finally importing data you can preview data and verify the query in a Data Preview window.
Instantly Refresh Data Whenever You Need
It's very convenient to always have fresh data from the database right in your workbook. Imagine, you have performed data analysis in your Excel workbook, built some charts for your SQLite data, and you can re-read data from the source and update all this to the current state by just clicking a button on the ribbon whenever you need.
Edit SQLite Data Like Usual Excel Spreadsheets
To edit SQLite data in Excel you need just to click the Edit Mode button on the ribbon before editing, and then you can edit data just like you usually do it - add or delete rows, modify cell values, etc. All the changes are highlighted so you can easily see which data is changed.
After you finish editing, simply click Commit to write changes to the database or Rollback to cancel changes.
Instant Data Validation
Devart Excel Add-in for SQLite instantly validates entered data and highlights errors, such as incorrect dates, duplicate primary key values, etc.
Order all 23 Devart Excel Add-ins in one Universal Pack and get a fantastic discount! If you need add-ins only for cloud applications, you may order Devart Excel Add-in Cloud Pack. For databases, there is Devart Excel Add-in Database Pack available.
В этой заметке я делюсь своим опытом переноса данных скрупулёзно собранной российской экономической региональной статистики из формата электронной таблицы OpenOffice/LibreOffice Calc (ods) в базу данных SQL на основе SQLite для дальнейшего использования в R. Думаю, это может быть полезно тем, кто занимается обработкой и анализом объёмных экономических или других статистических данных сколь-либо серьёзным, хотя и не индустриальным, образом. Всем остальным эта заметка будет неинтересна, так как является обыкновенным how-to на специфичную тему. Честно говоря, я не обнаружил, предоставляет ли прямой доступ Госкомстат к своей базе данных (не на основе убогого web-интерфейса). По всей видимости нет, что я считаю весьма неприятным недоразумением с их стороны.
Почему SQL?
Лично я большой приверженец использования электронных таблиц, однако возник такой момент, когда данных стало слишком много, и работа с многомегабайтными файлами стала трудоёмкой. Много времени уходит на копирование и шаблонную модификацию рядов цифр.
Типичный "алгоритм" работы со статистикой может выглядеть так: открытие OpenOffice, пролистывание листов, поиск нужной колонки, копирование, вставка, сохранение в csv, импорт в R. Когда приходится делать это десятки раз, начинаешь задумываться об автоматизации обращения к данным.
Пожалуй, современные электронные таблицы оптимальны для передачи данных (хотя рабоче-крестьянский формат csv предпочтительнее), но не для хранения и сложной обработки.
Причём наиболее серьёзная проблема - это необходимость ручного отслеживания обновления данных. В ходе эконометрического анализа могут создаваться десятки небольших частных исследований, для каждого из которых требуется репликация аналогичного набора данных. После (или вследствие) проведения таких исследований данные могут быть обновлены или скорректированы. Могут появиться свежие данные за самый последний отчётный период. Естественно, что возникнет желание пересчитать предыдущие результаты. В случае работы с csv это потребует обновления каждого файла в каждом мини-проекте, что может быстро стать кошмаром. Гораздо разумнее хранить один экземпляр данных в одном централизованном месте, откуда они могут быть запрошены разными инструментами и задачами.
Другие форматы?
Данные можно хранить в Rdata (внутренний формат R), однако это не так удобно, если требуется использование в других средствах или редактирование.
Несколько плюсов и минусов от перехода на SQL
- По сравнению с OpenOffice мнгновенное получение данных
- Устранение хаоса с множественными копиями данных, часть из которых возможно устарела
- Стандартный формат. Изначально я данные хранил в Lotus 1-2-3, конверсия в Calc доставила немало проблем и нет гарантии, что не придётся переходить на другую платформу. С SQL таких проблем, хочется надеяться, нет и не будет
- Возможность нормальной организации дополнительной информации (примечаний, ссылок и т.п.) к данным в строго определённом месте
- Управляемый экспорт по сложным признакам. Ранее для работы с таблицами мне приходилось писать множество одноразовых скриптов на Rexx, чтобы к примеру извлечь каждую пятую строку. Такие запросы лучше выполнять стандартно с помощью обращений SQL
- Сравнительная лёгкость добавления данных и изменения их структуры. Например, в Calc для табличной организации я выбрал отправным годом 1995 для всех таблиц. Однако позднее мне понадобилось добавить несколько серий с 1990 г. Это было весьма неудобно.
- При работе с таблицами данные лучше представляются визуально. Например, можно использовать цветовую раскраску колонок и строк, пусть и примитивную как у меня. Можно непосредственно ``видеть'' цифры
- Требуется изучение SQL и других инструментов. Однако у меня это заняло не так уж много времени (с SQL я фактически сталкиваюсь впервые)
- Невозможность автоматического пересчёта части данных (пожалуй, самая мощная функциональность электронных таблиц). Например, ряд статистики у меня является производным - вычисленным на основе других доступных данных. При обновлении последних автоматически обновлялись первые. Теперь это придётся делать вручную.
Создание базы данных
Этап 0. Перевод данных в csv.
Для начала следует сохранить все данные в csv для дальнейшего импорта. Следует удалить неинформативные (пустые) колонки и незначащие строки. К сожалению, импорт csv в SQLite сделан примитивно, поэтому придётся совершить ряд промежуточных манипуляций.
Для удобства я использовал две верхние строки для указания краткого обозначения каждой переменной, которое будет использоваться в R, и для более подробного необязательного примечания, которое можно будет получить отдельной таблицей. Остальные строки - данные.
Не рекомендуется использовать зарезервированные слова для названий колонок. Например, таким словом у меня было index. Также возникли проблемы из-за названий, содержащих точку ("."). Проще всего их переименовать, но в крайнем случае можно использовать обрамление имени прямоугольными скобками, например [index] или двойными кавычками. Ещё нежелательно допускать кавычки и запятые где-либо в значениях (в тексте примечаний, например), так как это осложняет последующий импорт.
Исходные данные после копирования из основной таблицы выглядят примерно вот так:
Этап 1. Создание базы данных
Изначально данные сгруппированы по страницам, поэтому вполне естественно вставить их в виде таблиц. Для создания таблиц нам потребуется заполучить имена колонок из второй строки импортируемого csv файла. Делается это просто
Получив список переменных, создаём таблицу
Вместо переменных нужно вставить полученный ранее список.
Однако создать таблицу мало - нужно ещё загрузить в неё данные.
Этап 2. Импорт данных
Осуществляется стандартными средствами SQLite, нужно только указать разделитель, использованный в файле csv:
Если в полях данных встречается запятая, то скорее всего этот способ не пройдёт. Тогда следует все записи в csv файле обрамить кавычками, а в качестве разделителя использовать "," .
Вот кусок небольшого bash скрипта, который осуществляет импорт одного файла:
Посмотреть на результат можно с помощью SQLite Browser:
Совсем как обычная электронная таблица. К ней также можно настроить доступ через ODBC с помощью ODBCConfig (и подключить к OpenOffice) - см. ниже.
Работа с SQL запросами
На данном этапе полезно данные "почистить". Делается это всё обычными SQL запросами. Это касается в первую очередь отсутствующих наблюдений, корректная работа с которыми крайне важна. Разные программы используют разные способы обозначения отсутствующих наблюдений (например, Госкомстат проставляет точки в зависимости от настроения), что затрудняет обработку файлов. Лучший вариант в нашем случае - использование пустых полей. При необходимости как-то специально обозначить отсутствие наблюдений (если получатель данных не обрабатывает пустые значения корректно) эта функция целиком ложится на экспорт и может быть решена другими подручными средствами.
Для автоматизации этой задачи я попотел и выдал скрипт, автоматически генерирующий подобные запросы для всех таблиц и всех колонок. Вообще основная проблема при работе с SQL - это необходимость автоматической генерации запросов, которая решается с использованием других средств (хотя бы bash и coreutils).
Для региональных данных (как в нашем случае) индексация данных осуществляется по номеру региона. Каждая страница электронной таблицы ранее содержала пару колонок с полным названием региона (только для одного года, чтобы избежать clutter). Такое дублирование на самом деле излишне, поэтому лучше вынести название регионов, соответствующие номерам, в отдельную информационную таблицу, удалив прежнюю информацию.
Создаём таблицу, например
Используем уже имеющиеся данные из любой основной таблицы. Так как эта информация везде повторяется, достаточно запросить её из любой таблицы. Например, следующим запросом осуществляется вставка нужной информации
К сожалению, SQLite не позволяет удалить переменные из таблицы. Для этого приходится их пересоздавать, опуская ненужные колонки. Эту задачу я тоже предпочёл автоматизировать, хотя и потратил какое-то время.
Теперь создадим таблицу с описанием всех переменных и примечаний. Такую таблицу я тоже сгенерировал автоматически на основе информации, которую заранее разместил в csv (в первой строке, если помните). После того, как таблица готова, информацию можно запросить по
В общей сложности 225 переменных, которые охватывают период с 1995 по 2006 гг (примерно, на деле намного меньше).
Следующий запрос к sqlite3 получает список всех таблиц
Интересно, что занимаемое данными место на диске заметно уменьшилось (1 Мб против 2.6 Мб в ods и 15 Мб в xls).
Обращение к данным из R
Для чего всё это затевалось? А собственно для возможности загружать данные прямо в R!
Для доступа к данным используется пакет RSQLite для бэкенда SQLite и DBI для интерфейса, т.е. непосредственного обращения к данным.
Для начала необходимо создать драйвер
Затем подключиться к базе данных, идентифицируемой по имени файла
Всё! Так просто! Можно работать с данными, отправляя SQL запросы. Список таблиц получается вызовом
Список переменных в таблице запрашивается по
Загрузить таблицу целиком (если нужно):
Запросы SQL отправляются с помощью
Результатом запроса является data.frame. Собственно ради этого всё и затевалось.
Завершение работы (важно):
Пример использования данных в R
Теперь посмотрим, как это всё работает. Учитывая повторяющиеся инструкции для загрузки данных, есть смысл разместить их в отдельном файле, который можно разместить в корневой директории рабочих проектов.
Вот возможное содержание такого файла, в целом повторяющее инструкции выше
и файла отключения от базы данных
Вот пример построения графика отношения капитала к выпуску в российских регионах (официальные данные)
Как видим, работа с SQL элементарна и требует лишь незначительных усилий, в частности добавления лишь двух строк в каждый файл, работающий с данными, в остальном мало отличаясь от стандартных функций загрузки данных.
Литература
Connecting to SQLite from Microsoft Excel using ODBC Driver for SQLite
You can use Microsoft Excel to access data from a SQLite database using ODBC connector. With ODBC Driver, you can import the data directly into an Excel Spreadsheet and present it as a table. Make sure that you use matching Excel and ODBC Driver, e.g. if you have installed a 64-bit ODBC Drive, you will need to use the 64-bit version of Excel.
When working with Microsoft Excel, there are different ways of retrieving data from various data sources using our ODBC drivers:
Connecting Excel to SQLite with Get & Transform (Power Query)
You can use Get & Transform (Power Query) to connect to SQLite from Excel with ODBC. This method assumes that you've installed an ODBC driver for SQLite.
-
Click the Data in Excel, then expand the Get Data drop-down list. Click From Other Sources > From ODBC.
If your database is not password-protected or you've already specified your credentials in the ODBC data source settings, select Default or Custom and press Connect
The data from the table will be a displayed in an Excel spreadsheet where you can further work with it.
Connecting Excel to SQLite with Data Connection Wizard (Legacy Wizard)
You can use this option to connect to OLE DB or ODBC external data source that has already been defined.
-
In Excel, go to the Data tab. Click From Other Sources, and then click From Data Connection Wizard.
In the opened dialog, select ODBC DSN and click Next to continue.
Now select a data source you want to connect to, and click Next.
To connect to the table containing the required data, select its name and click Next to enter and save information about your new file or click Finish.
In the Import data dialog, you can select the way your data will be viewed in Excel and the place where to put it in the worksheet, and click OK.
The required data is now displayed in the existing Excel worksheet.
Connecting Excel to SQLite with the Query Wizard
You can use this option to create a simple query for retrieving data from SQLite to Excel via ODBC driver.
-
Open Excel, in the main menu, click the Data tab.
Click the From Other Sources dropdown menu, and then click From Microsoft Query.
In the appeared dialog, you can choose the data source you want to connect to.
After a successful connection, you can select the data you want to be displayed in Excel and click Next.
The next two steps allow filtering and sorting the data. Click Next to skip these procedures.
If you plan to further use the query, you can save it by clicking the Save button on the right.
Select Return Data To Microsoft Excel and click Finish.
In the Import data dialog, you can select the way your data will be viewed in Excel and the place where to put it in the worksheet, and click OK.
The required data is successfully imported to Excel.
Connecting Excel to SQLite with Microsoft Query
You can use this option to create a more complex query for retrieving SQLite data to Excel via ODBC driver.
In the appeared ribbon, click From Other Sources, and then click From Microsoft Query.
In the next dialog, choose the data source you want to connect to (e.g., using data source name - Devart ODBC SQLite). Uncheck Use the Query Wizard to Create/Edit Queries and click OK.
Now you can select the tables you want to add to your query. When you finish, just click the Add button.
In the graphical editor, you can filter rows or columns of data, sort data, join multiple tables, create a parameter query, etc.
Connecting Excel to SQLite with PowerPivot
You can use PowerPivot - an Excel add-in to perform data analysis and create complex data models. To load the required data, do the following:
-
In Excel, click the PowerPivot tab, then click Manage to go to the PowerPivot window.
In the opened window, click From Other Sources.
When the Table Import Wizard opens, select Others (OLEDB/ODBC) and click Next.
In the Specify a Connection String window, click the Build button.
In the Data Link Properties dialog, specify the data source you want to connect (e.g., using data source name - Devart ODBC SQLite), and then click Next.
Now you should choose how to import the data (either select a table from the list or write a query to specify the data to be imported).
When the Import operation succeeded, click the Close button. The retrieved data is inserted in the active worksheet.
Читайте также: