0

I have a bunch of CSVs that get updated locally on my computer every few days. I want to refresh them in SQLite Studio but I can't find out where to actually refresh. Is there an option to do this? The only way i've been able to refresh is to fully delete the table, and then re-import it under the same name (so the query still works). All of the CSVs and Sqlite Studio are local on my computer I am not running anything remote.

eluth
  • 69
  • 2
  • 13

1 Answers1

1

CSV file is not linked in any way with SQLiteStudio. Once you import data to table, it is in table, not in CSV file. If you want to refresh contents of table with data from CSV files, then you need to do exactly what you already do, that is re-import.

An useful tool to make this repeatable task less clumsy is import() SQL function built in SQLiteStudio. You can easily delete old data and re-import new one in single execution:

delete from your_table;
select import('path/to/file.csv', 'CSV', 'your_table', 'UTF-8');

Of course you need to adjust your parameters. Also there can be 5th (optional) parameter specifying importing options, just like in Import Dialog. Quoting from User Manual (https://github.com/pawelsalawa/sqlitestudio/wiki/User_Manual#built-in-sql-functions):

charsets() Returns list of charsets supported by SQLiteStudio (to be used for example in arguments for import() function)

import_formats() Returns list of importing formats supported by SQLiteStudio (depends on import plugins being loaded)

import_options(format) Returns list of currently used importing settings for certain format (the format must be one of formats returned from import_formats()). Each setting in a separate line. Each line is a setting_name=setting_value

import(file, format, table, charset, options) Executes importing process using file for input, format for choosing import plugin (must be one of values returned from import_formats()). The import is done into the table. If table does not exists, it will be created. The charset is optional and must be one of values returned from charsets() (for example 'UTF-8'). It defaults to UTF-8. The options is optional and has to be in the same format as returned from import_options() (which is one option per line, each line is option_name=value), although it's okay to provide only a subset of options - then the rest of settings will remain.

Googie
  • 5,742
  • 2
  • 19
  • 31
  • thanks - let me try this out. I find it crazy that there isn't just an option to refresh the CSV data considering it already knows the exact path where the data is stored. It would essentially be the same thing as what you posted above – eluth Apr 11 '20 at 18:17
  • This doesn't work for me. When I try: select import('C:\Users\evanl\Desktop\Stock\stock_data.csv', 'CSV', 'test_table', 'UTF-8'); I get the error: [12:03:11] Error while executing SQL query on database 'master_database': wrong number of arguments to function import() – eluth Apr 11 '20 at 19:03
  • 1
    What if you do `select import('C:\Users\evanl\Desktop\Stock\stock_data.csv', 'CSV', 'test_table', 'UTF-8', '');` instead? – Googie Apr 12 '20 at 19:23
  • This is working but one question - is there a way to speed this up? manually refreshing a table takes ~30 seconds total, but this import line is taking almost an hour (91mb file) – eluth Apr 21 '20 at 05:01
  • I'm not sure if that's the reason, but you may try with transaction: `begin; select import('C:\Users\evanl\Desktop\Stock\stock_data.csv', 'CSV', 'test_table', 'UTF-8', ''); commit;`. I don't remember if transaction is being used internally or not. (certainly it is in the UI dialog and it should use the same routing, but I' mnot 100% sure). – Googie Apr 22 '20 at 06:49