How to run SQL queries on local text files
TLDR: The Ado SQL Library allows for powerful SQL queries on databases and text files, simplifying tasks such as deduplication, filtering, accounting, and sub queries.
You can grab the v1 ADO library here: https://the-automator.com/ADOSQL
1. 00:00 💻 Ado SQL Library allows for powerful SQL queries on databases and text files, making tasks such as deduplication, filtering, accounting, and sub queries customizable and efficient.
1.1 Ado, the active data X objects functionality built into Windows since 1997, allows for powerful SQL queries on databases and text files for tasks such as deduplication, filtering, accounting, and sub queries.
1.2 The Ado SQL Library is a powerful tool for working with CSV files and building SQL strings to extract data in a customizable way.
2. 02:37 📚 Using a library can simplify looping through record sets, but it's not necessary if you know how to connect to the com object directly; batch lines require a connection string and schema.ini file.
2.1 The library only hides the specific part of looping through the record set, so if you know how to do that, you can connect to the com object directly without needing the library.
2.2 To set up batch lines, a connection string with driver properties is needed, and a schema.ini file must be created to specify the file format if it is not comma delimited.
3. 05:14 💻 Use SQL to count lines in a file by specifying the database name, setting up a connection string, calling the function with the SQL statement and connection string, and displaying the result with the output window library.
3.1 To read a file and count its lines using SQL, specify the database name (which is usually the file name), set up a connection string, call the function with the SQL statement and connection string, and use the output window library to display the result.
3.2 The speaker demonstrated how to select specific data from a large CSV file using a simple SQL statement instead of having to use loops.
4. 07:39 💻 Adjusting SQL order and using smaller files for demos.
4.1 The speaker discusses adjusting the order in SQL and switching to a smaller file for demonstration purposes.
5. 08:45 📊 The speaker is struggling with a library that reads the last column differently when trying to match values in a dataset.
5.1 The speaker is trying to match specific values in a dataset but is having trouble with the library reading the last column differently.
5.2 Create strings based on certain criteria, but be cautious of library quirks.
6. 12:03 💻 Using aliases and the query function can simplify and speed up data extraction from CSV/TSV files, but creating a database file is not possible with this method.
6.1 Extracting specific data from a CSV or TSV file is made easier and faster with the use of aliases and the query function, eliminating the need for manual looping.
6.2 It is not possible to create a database file with the given method, which creates an overhead of reading CSV files and creating tables and structures.
7. 13:53 📊 ADO only allows for tab delimited or CSV file creation, while sqlite is better for advanced tasks like joining and updating records.
7.1 Creating a new database in ADL is not possible, as it only allows for the creation of tab delimited or CSV files.
7.2 Ado is powerful but limited in functionality, and for more advanced tasks like joining and updating records, it's better to switch to sqlite.
8. 15:48 💻 Use a flexible tool to extract data from large files.
8.1 Use a flexible tool to quickly filter and extract data from large files.
Watch video How to run SQL queries on local text files online without registration, duration hours minute second in high quality. This video was added by user AUTOHOTKEY Gurus 06 June 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,023 once and liked it 53 people.