Another stab from the past

[stanl]

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

Code (WINBATCH) Select Expand


qry = "SELECT * INTO [" :Table :"] FROM [TEXT;DATABASE=" :parent:";HDR=YES;FMT=Delimited].[":leaf:"];"



... schema.ini if text file detected as tab-delimited

Code Select Expand



[file.txt]
ColNameHeader=True
Format=TabDelimited


[td]

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.

[stanl]

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.

[td]

Certainly a bunch of interesting stuff results from a search with the term "isam".