Lately facing having to work with text files exported from servers/sharepoints/db's with variable delimiters [, tab , ; , ^ ] or exported as json. Was thinking could still be done with ADO and digging into my archives found scripts from 2002 with the SQL syntax [that was difficult to find with searches]. The only issue was to create a schema.ini file for delimiter. Then for each file to process obtain the
- folder file was in - the parent
- file name - the leaf
- create the schema.ini
based on that can issue SQL, based on ADODB connection
qry = "SELECT * INTO [" :Table :"] FROM [TEXT;DATABASE=" :parent:";HDR=YES;FMT=Delimited].[":leaf:"];"
... schema.ini if text file detected as tab-delimited
[file.txt]
ColNameHeader=True
Format=TabDelimited
Nice. Might have to add that to the Tech Database if it isn't already there. Could ease syntax searching for others. I am sure there are "modern" ways to perform the task, but bright-shiny-and-new doesn't necessarily equate to better.
Quote from: td on October 20, 2023, 03:10:29 PM
Nice. Might have to add that to the Tech Database if it isn't already there. Could ease syntax searching for others. I am sure there are "modern" ways to perform the task, but bright-shiny-and-new doesn't necessarily equate to better.
You can search for 'isam' in Tech Database... a couple of good examples from around 10 years ago. All the new Power Query, Power BI, Tableau is great for working with data in a variety of formats, but ADO is still very fast and efficient.
Certainly a bunch of interesting stuff results from a search with the term "isam".