Author Topic: Another stab from the past  (Read 449 times)

stanl

  • Pundit
  • *****
  • Posts: 1849
Another stab from the past
« on: October 20, 2023, 05:01:37 am »
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
qry = "SELECT * INTO [" :Table :"] FROM [TEXT;DATABASE=" :parent:";HDR=YES;FMT=Delimited].[":leaf:"];"
 


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


Code: [Select]

[file.txt]
ColNameHeader=True
Format=TabDelimited


td

  • Tech Support
  • *****
  • Posts: 4444
    • WinBatch
Re: Another stab from the past
« Reply #1 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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

  • Pundit
  • *****
  • Posts: 1849
Re: Another stab from the past
« Reply #2 on: October 21, 2023, 04:58:29 am »
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

  • Tech Support
  • *****
  • Posts: 4444
    • WinBatch
Re: Another stab from the past
« Reply #3 on: October 21, 2023, 09:02:49 am »
Certainly a bunch of interesting stuff results from a search with the term "isam".
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade