Dealing with lots of data

Nutty · Jul 12, 2016

If you're reasonable comfortable with Microsoft Excel, and you've got a lot of table data to work with, and you want to be able to go back to it, make edits (with the ability to sort and filter and search by field), here's the trick I started using so I could work with my data in Excel, and easily export it to a SQL table. (Note, your existing project could be adapted to this technique very quickly!)

Many SQLite browsers allow you to export table data to CSV (a text file with Comma-Separated Values). If you're not already using one with that facility, I know that SQLiteStudio and SQLite Manager for Firefox work. [Don't get excited when you see XML as an export option; that's not going to get you workable data.]

Open Civ5DebugDatabase.db in your SQLite browser app, and export your table(s) to CSV.
Open a CSV in Excel, and do whatever you need to do, adding your new rows, making all your edits as necessary.
Then on a different sheet in the same workbook, fill down however many rows your source data has with the word "SELECT".
Skip however many columns your source data has, and in the following column, fill down with the words "UNION ALL".
Now fill down and/or across as required, putting:
Code:
```
CONCATENATE('Sheet1'!A1,",")
```
for the integer values, to simply add a comma after the field, and
Code:
```
CONCATENATE("'",'Sheet1'!A1,"',")
```
for the string values, to add a single quote mark before and a single quote mark then a comma after.
Add a row at the top for your column names. Precede it with a "--".
Add a row below that for an "INSERT OR REPLACE INTO..." (or your DELETE followed by an INSERT, as necessary).
At the very end, add a semicolon.
Hopefully you're not like me with more than 500 rows to insert (I have 1738 and counting!). If so you'll need a semicolon and another INSERT line every 500 rows.
Now, autosize all the columns to your data, then add a little extra, say +3.0 or so to each column width (this is important, as otherwise it's liable to truncate your fields!)
First save the workbook as a traditional .xlsx file.
Then save the current sheet as a "Formatted Text (Space delimited) (*.prn)" file.
Rename the file to .sql (even if you give it the extension, it'll add .prn to the end).
Open your .sql file in a text editor, copy the contents, and paste it into the Execute box of your SQLite browser app to make sure your code is good.

In the end, this will give you a very nicely laid out text file organized into neat columns, something like:

Code:

--         StrCol                     IntCol StrCol2           StrCol3       StrCol4             IntCol2
INSERT INTO TableName(Col1,Col2,Col3,Col4,Col5,Col6)

SELECT     'STRING1',                 0,     '_STRING1',       '_STRING1',   '',                 0   UNION ALL
SELECT     'STR2',                    0,     '_STR2',          '_STR2',      '',                 1   UNION ALL
SELECT     'LONGSTRING',              0,     '_LONGSTRING',    '_LONGSTRING','',                 0   UNION ALL
SELECT     'STRING3',                 0,     '_STRING3',       '_STRING3',   '',                 1   UNION ALL;

Then you can go back to your first sheet when you need to do edits, and the second sheet will update (as long as you have given yourself enough rows on the export sheet).

Eventually I went crazy and taught myself VBA and extended my automatic data manipulation and made everything a one-click process. But that was a rabbit hole I would not recommend to everybody...

Good luck!

EDIT: Apparently there's a hard 240 character row limit which I've just discovered, after which point the next "page" of rows will end up at the bottom. Depending on your purposes, this might mean this isn't a good solution for you, though note you can create subtables and JOIN them with SQL (not to mention abbreviate your column names).

Dealing with lots of data

Nutty

Deity

Similar threads