12/3/2023 0 Comments Sqlite command line script![]() That means you will forgo the opportunity to use the latest enhancements in SQLite. The ODBC drivers mentioned above are statically linked to a historical version of SQLite and you have no control over the timing of their update. Any cosmetic rendering such as number formatting or font changes also tend to stick.You might also have charts based on the data drawn from Excel these will refresh automatically as the data changes.This is easier to maintain than a series of individual text files. Subject to available memory, all the data you present to the user can be contained in one workbook and in different worksheets.The user is empowered - they can choose when to refresh the data.Your SQL is not exposed (in text files). ![]() Then, in Excel, the user clicks Data | Refresh All to refresh the data by re-reading the underlying data from SQLite. CARE! These links that follow will download the drivers For ODBC, you will need either the 32-bit driver or the 64-bit driver depending on whether you have the 32- or 64- bit version of Excel. To set up, pursue Data | Get Data | From Other Sources | From ODBC within Excel.ī. The user has to run the script afresh to get the underlying data (which might have changed).Īs you appear to be driving everything from Excel, you might consider refreshable workbooks.Ī.the user has a historical snapshot of the underlying data (which has some merits and can be useful for forensic analysis).PS: You can (should) use / rather than \ for path separators since / is safe(r) for SQLite and fully accepted in Windows.Ĭan't make it much easier for the casual user.Īs things stand, the extract or result of your SQL statement is static. Usually, you would simply insert a blank line in your file to overcome this in this case, you need to add semi-colon at the end of your SQL statement. In Excel you will need to AutoFit Column Width.īut have to hit return to get it to finish The above will start a new Excel instance and send the output directly to that instance (which should be installed on the computer). once command redirects output to the file e:/temp/A_sqlite.csv: this file will be overwritten if it exists without warning.Īdapt the contents of the files (the two blocks of code) to your circumstances and it should work else raise your queries here.Īlso, consider the option to send the output of your SQL statement directly to Excel (thereby giving your users even less to do!).once -x Second your SQLite lines of code in e:\temp\aa2.txt The left chevron, <, redirects input from e:\temp\aa2.txt to SQLite3.EXE That is, specify the fully qualified name of SQLIte3.EXE, in quotes (permits the path to have embedded spaces) followed by the fully qualified name of another file, e:\temp\aa2.txt in my example. That's why it does not work.įirst, your batch file (it should have extension BAT or CMD such that double click on it from within File Explorer will execute it). When executing the same set of lines/statements from a batch file, all the lines are echoed to the Command Prompt NOT the SQLite CLI. When pasting into the Command Prompt,your first line starts & focuses on the SQLite CLI the second and subsequent lines go to the CLI where they make sense. This works when I copy it and paste it into the command prompt. The less the user has to do - or even see - the better. Would be nice to simply also close the command line window as part of the process. ![]() No output was generated.Īny suggestions on how to change the batch file so it can simply be double-clicked by a user and the final csv output table appears. I've tried including the start cmd to open the command prompt and cd command to change the directory at the beginning of the batch file. quit to the end so the user would not have to hit the return key. Then I can run the above query, but have to hit return to get it to finish and create the csv file. I have to open the command prompt manually, then I either have to change the directory to the database location or include the entire path along with the db name when I execute sqlite. However, several problems I haven't been able to solve. To illustrate where I am at this point, here's a sample batch file: My plan is to develop a hyperlink index in, perhaps, Excel or Word, with links to maybe 20 different batch file queries. Trying to create some query batch files to make it easier for users to extract various data tables by simply clicking a link for the desired output.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |