Перенос данных из excel в sqlite

Обновлено: 06.01.2025

Лично я большой приверженец использования электронных таблиц, однако возник такой момент, когда данных стало слишком много, и работа с многомегабайтными файлами стала трудоёмкой. Много времени уходит на копирование и шаблонную модификацию рядов цифр.

Типичный "алгоритм" работы со статистикой может выглядеть так: открытие 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 элементарна и требует лишь незначительных усилий, в частности добавления лишь двух строк в каждый файл, работающий с данными, в остальном мало отличаясь от стандартных функций загрузки данных.

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

Зачем мы это делаем? - Все очень просто чтоб было легко все парсить :) Если открыть наш новый *.csv файл через блокнот то увидим, что данные каждого столбца в Excel разделены точкой с запятой. Когда мы открыли в блокноте файл сохраните его как *.txt только, желательно, с utf-8 кодировкой, чтоб не было проблем с украинскими и русскими символами.
Далее перейдем непосредственно к программированию. Я делал все это для Windows 8/RT метро приложений, но для обычных десктопных, помоем, нужно изменить только чтения из файла. И так вот наш код:

Далее для записи в БД, все как обычно, создаем модель данных, подключаем БД и добавляем в нее элементы row[i].

Недавно мне нужно было работать с одной статической БД, которая находилась, как не странно, в Excel файле. Простой файл в котором есть три колонки (допустим A,B,C) и примерно две тысячи записей.

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.

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.

Импортировать Excel в базу данных SQL Server с помощью Python

Обязательно установите xlrd

Поместите файл листа Excel по тому же пути, что и файл .py

Содержимое таблицы SC показано на рисунке.

обращать внимание

① "SC.xlsx" - это имя файла excel, и убедитесь, что файл таблицы excel помещен по тому же пути, что и файл .py, в противном случае будет сообщено об ошибке

②sheet - это имя таблицы в Excel, обычно по умолчанию Sheet1

③ Эта операция по удалению таблицы отправки предназначена для обеспечения того, чтобы каждый раз при импорте таблицы Excel содержимое таблицы отправки в базе данных было точно таким же, как содержимое импортируемой таблицы Excel каждый раз, и оно было таким же, как в предыдущей таблице отправки. Неактуально

Дай каштан,Запускаем программу два раза подряд, С двумя вышеупомянутыми строками впервые. Без второго раза,Запустить программу в последний разРезультаты выполнения следующие:

, а также следующие две строки кода

В таблице отправки результат после второго выполнения программы не будет "накопление”
Конечно, если вы импортируете его только один раз, Delete не имеет смысла, вы можете добавить его или нет.
④ Иногда в первой строке таблицы Excel указывается, что заголовок не является данными, но вы не хотите импортировать содержимое заголовка в базу данных в виде данных, например:

Решение - начать цикл for с 1 вместо 0:

Если первая строка Excel - это данные, которые необходимо импортировать в базу данных, просто позвольте циклу for начинаться с 0

Читайте также: