SQLite Forum

Noob Question
Login

Noob Question

(1) By anonymous on 2021-04-12 18:44:50 [link] [source]

Hi Everyone,

I have been doing some research for a few months now and i've been trying to migrate some public data regarding wildfires from Excel Tables to SQLite3. 

The issue I am having is that all the Excel files are set up differently. The tables are not always in the same positions or even the same pages. 

For a bit of background, the tables all represent a fire report for a weeks worth of time. I would also like to add a date column once the files are imported from Excel into SQLite3.

If anyone has any tips or advice for this project please let me know. My experience is limited and I have run out of ideas for the time being.

Thanks!

(2) By anonymous on 2021-04-12 19:00:44 in reply to 1 [link] [source]

Hello,

Sounds like a neat idea and exactly the use case for SQLite! You'll likely need to convert those spreadsheets into csv files and import with the sqlite3 shell.

You can read the cli documentation here. You can read about the date & time functions here. Start with some sample data or at least backup your data before modifying it, just in the event you need to revert things.

If you would rather use a GUI to import data into a sqlite3 database, I recommend sqlite-gui, which has no affiliation with the SQLite project. There are many GUI applications to use, so don't feel like my solution is the only one; it's just something I've grown fond of over the last few months.

(3) By anonymous on 2021-04-12 19:17:00 in reply to 2 [source]

Thanks for the information! So if I convert the files to CSV would I then be able to pluck the tables from Excel easier before entering them into SQLite?

(4) By anonymous on 2021-04-12 22:05:43 in reply to 3 [link] [source]

I'm assuming your tables are made from some kind of data in the Excel document. You will want to export/save as the 'raw' data as a csv, not some kind of chart/table/pivot table.

(5.1) By little-brother on 2021-04-12 22:13:48 edited from 5.0 in reply to 3 [link] [source]

Let us suppose, you have a bunch of xls-files in D:/xlsData folder.
You can try to import data within sqlite-gui in an one select without CSV-step.

select odbc_read('Driver={Microsoft Excel Driver (*.xls)};Dbq=D:\xlsData\' || line, 'select * from "Sheet1$"', line)
from exec('powershell Get-ChildItem -Path D:/xlsData -Name')

What happen here:

  • exec(command) returns list of files in folder. Test it:
    select line from exec('powershell Get-ChildItem -Path D:/xlsData<br> -Name')
    
  • odbc_read(DSN, query, target) fetchs data from sheet1 to table named as filename for each row.

Both exec and odbc_read are only sqlite-gui abilities. Check Wiki (link) for odbc and exec extensions.
If you need to fetch data from different sheets then you can create auxilary table aux with columns filename and sheet number

select odbc_read('Driver={Microsoft Excel Driver (*.xls)};Dbq=D:\xlsData\' || line, 'select * from "'|| aux.sheet || '$"', line)
from exec('powershell Get-ChildItem -Path D:/xlsData -Name') e inner join aux on e.line = aux.filename

P.S. For some data odbc_read can be broken. It has a lack of testing.
P.P.S. I'm a author of sqlite-gui :)

(6) By anonymous on 2021-04-12 23:00:43 in reply to 5.1 [link] [source]

Ok this is very useful information! I am downloading your gui from GitHub now to try.

I will try out your method and see what I can do :)

Thanks!

(7) By Simon Slavin (slavin) on 2021-04-13 14:24:33 in reply to 3 [link] [source]

I think you have data currently in Excel, and want your data in SQLite.

The CSV format is a widely-used format. Pretty-much all software that handles data in columns can read and write files in this format. So when moving data from one system to another, it's common to use CSV as a half-way-house.

The normal way to do this is to use the Save or Export functions in Excel to save the data to a .csv file in CSV format. Then to use the SQLite command line tool to import data from the .csv file into a SQLite database. You can find information on the SQLite site of this here:

https://sqlite.org/cli.html#importing_csv_files

If you're new to SQLite I recommend reading the entire page, since it is a good introduction to an incredibly useful tool for manipulating SQLite files. You won't understand it all, but it'll get you thinking right.

To add another column to an existing SQLite database, use the ALTER TABLE ADD COLUMN command described in section 4 of

https://www.sqlite.org/lang_altertable.html

(8) By anonymous on 2021-04-13 17:13:30 in reply to 7 [link] [source]

Great thank you Simon!

I will read through those links and gain a better understanding of what I am working with.

Getting close to the results I am looking for!

(9) By Ryan Smith (cuz) on 2021-04-15 09:24:42 in reply to 3 [link] [source]

By the way, if you use SQLitespeed you can simply select the data in Excel (or any other spreadsheet program) and copy it then paste into SQLitespeed by using the "Import from Clipboard" - one click, no mess no fuss.

The import page will also let you change column names, set column types/affinities etc. and set the Table name.

It's one of the base needs that system was created for.

PS: That button is one of three small import buttons to the left of the Query edit box.

(10) By ddevienne on 2021-04-15 09:38:21 in reply to 9 [link] [source]

Are you the author Ryan? Then maybe update the 18 month old SQLite? :)

New features in version 2.1.3: Support standard SQLite functionality and changes up to version 3.30.1

2019-10-10 (3.30.1)

(11) By Ryan Smith (cuz) on 2021-04-15 10:38:10 in reply to 10 [link] [source]

Hangs head in shame

Truth be told, the entire SQLitespeed project was made in Delphi which is a really expensive dev platform (i.e. not conducive to open sourcing), so I moved it to FPC which is a fantastic project, fully cross-platform, and also able to make it open source.

This lovely ideal started at the start of COVID and I thouhgt, great COVID will provide me the time to finally port it fully and complete the move to open source.

Reality was that I've worked more during COVID than ever before. Most of the conversion work is done though, but there's just a smidge more to do before it will fully compile and run, at which point a working open-source version 1 can be published, which will essentially do the same as the current (with a few upgrades maybe and definitely full support for latest SQLite), and hopefully then some others can join in the development (a few people have indicated interest and willingness).

Reason I am mentioning all this is that:

  1. It is why there are no official system updates past the one you've seen, and
  2. I do believe I've updated the actual installer to include up to 3.35 sqlite3 DLL, but did not change the web page. I will get right on it, and while at it, update it to the very latest SQLite.
  3. If anyone interested is reading this - You can just drop in the newest DLL manually next to the exe and it will work fine.

Will let y'all know progress on that open sourcing.