Can Microsoft OLEDB Source be inputted directly from variable contents?

General Macro Scheduler discussion

Moderators: Dorian (MJT support), JRL

Post Reply
ocnuybear
Pro Scripter
Posts: 100
Joined: Sun Jul 15, 2018 5:14 pm

Can Microsoft OLEDB Source be inputted directly from variable contents?

Post by ocnuybear » Mon Aug 13, 2018 7:22 am

I'm using Microsoft.Jet.OLEDB in my macro and before loading data into this database I need to do some cleanup and get the headers into the first line, then save it as a file. Then this file is loaded into Microsoft.Jet.OLEDB.

Now my question is it takes time to load and save strings to files, so I want to bypass saving and loading the file and load the string value into the database's source. Is it possible?

In line 27 the Source is using data loaded from unsorted.csv, but I want to load %Body% from line 20 directly into Source to save a lot of time & unneeded file read/writing.

Code: Select all

    Let>n1=n1+1
    DeleteFile>C:\FTP\Zero Hour Calls\Unsorted.csv
    CSVFileToArray>Files_%n1%,arrCSV
    Let>Comma=,
    Let>Body=
    Let>x=7
    Let>Count=ARRCSV_COUNT-1
    WriteLn>C:\FTP\Zero Hour Calls\Unsorted.csv,nWLNRes,Name%Comma%ID%Comma%Time%CRLF%
    Let>y=Count-3
    While>x<Count
      Let>x=x+1
      Let>Time=ARRCSV_%x%_0
      Let>ID=ARRCSV_%x%_2
      //Add>ID,1
      Let>Name=ARRCSV_%x%_3
      //Let>Line=%Time%%Comma%%ID%%Comma%%Name%%CRLF%
      Let>Line=%Name%%Comma%%ID%%Comma%%Time%%CRLF%
      Let>Body=%Body%%Line%
    EndWhile
    WriteLn>C:\FTP\Zero Hour Calls\Unsorted.csv,nWLNRes,%Body%
    //new file to write to
    Let>new_file=C:\FTP\Zero Hour Calls\Sorted.csv
    IfFileExists>new_file
      DeleteFile>new_file
    Endif

    //Connect to OLEDB Database
    Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\FTP\Zero Hour Calls;Extended Properties='text;HDR=YES;FMT=Delimited'
    DBConnect>ConStr,dbH
    Let>SQL=select * from unsorted.csv ORDER BY Name,Time
    DBQuery>dbH,SQL,fields,num_recs,num_fields
    Let>Body=
    //If we have records, loop through records and fields, writing out to new file
    If>num_recs>0
      //first need to copy the header record ...
      ReadLn>C:\FTP\Zero Hour Calls\unsorted.csv,1,header_line
      WriteLn>new_file,wlnres,%header_line%%CRLF%
      Let>rec=0
      Repeat>rec
        Let>rec=rec+1
        Let>field=0
        Repeat>field
          Let>field=field+1
          Let>this_field=fields_%rec%_%field%
          //write the field out to the new file
          Let>Body=%Body%%this_field%
          //add the comma delimiter if needed
          If>field<num_fields
            Let>Body=%Body%%comma%
          Endif
        Until>field=num_fields
        //add CRLF at end of line
        Let>Body=%Body%%CRLF%
      Until>rec=num_recs
    Endif
    DBClose>dbH
Last edited by ocnuybear on Wed Aug 15, 2018 6:49 am, edited 2 times in total.

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Can Microsoft OLEDB Source be value from a string?

Post by Marcus Tettmar » Mon Aug 13, 2018 6:04 pm

I'm rather confused. It looks like you are creating a CSV file then using DBQuery to read that CSV file and then outputting all the results to a new file. Why not just copy the file? I guess I'm missing the bit that does something in between? What are you trying to do? What is different about the input and output files? Perhaps show us examples?
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

ocnuybear
Pro Scripter
Posts: 100
Joined: Sun Jul 15, 2018 5:14 pm

Re: Can Microsoft OLEDB Source be value from a string?

Post by ocnuybear » Tue Aug 14, 2018 5:46 am

I have a raw CSV file like this - it has about 3000+ rows:

Code: Select all

	
	
	
Save Time :2018-08-13 10:30:05	
	
	
Time		Names
13-08-18 5:00	Name7
13-08-18 5:00	Name5
13-08-18 5:00	Name3
13-08-18 5:00	Name4
13-08-18 5:00	Name2
13-08-18 5:00	Name6
13-08-18 5:00	Name1
13-08-18 5:00	Name8
13-08-18 5:00	Name10
13-08-18 5:00	Name9
13-08-18 5:00	Name11
I need to sort this firstly by Names, and then by Time using OLEDB, but as you can see, the headers is not in the correct place so have to remove them first, then sort it.

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Can Microsoft OLEDB Source be value from a string?

Post by Marcus Tettmar » Tue Aug 14, 2018 10:21 am

Do you have Excel installed? I would use Excel. Record an Excel macro to do it, convert that to VBScript and run from Macro Scheduler.
https://help.mjtnet.com/article/19-conv ... o-vbscript
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

ocnuybear
Pro Scripter
Posts: 100
Joined: Sun Jul 15, 2018 5:14 pm

Re: Can Microsoft OLEDB Source be value from a string?

Post by ocnuybear » Tue Aug 14, 2018 11:24 am

I actually want to run this without using Excel & it must be running in the background without any windows popping up.

I guess the way I'm already running it is the best solution for my requirements?

Also passing a variable's contents to OLEDB is not possible?

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Can Microsoft OLEDB Source be value from a string?

Post by Marcus Tettmar » Tue Aug 14, 2018 1:20 pm

I'm struggling to parse your question.

You can use variables pretty much anywhere in Macro Scheduler. OLEDB is Microsoft's API, a database driver if you will. So I'm not sure what you mean. If you mean can you use variables in your queries, then yes, of course you can.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

ocnuybear
Pro Scripter
Posts: 100
Joined: Sun Jul 15, 2018 5:14 pm

Re: Can Microsoft OLEDB Source be value from a string?

Post by ocnuybear » Tue Aug 14, 2018 4:11 pm

My apologies if my question is not clear, let me try again.

I have a raw file like this:

Code: Select all

	
	
	
Save Time :2018-08-13 10:30:05	
	
	
Time		Names
13-08-18 5:00	Name7
13-08-18 5:00	Name5
13-08-18 5:00	Name3
13-08-18 5:00	Name4
13-08-18 5:00	Name2
13-08-18 5:00	Name6
13-08-18 5:00	Name1
13-08-18 5:00	Name8
13-08-18 5:00	Name10
13-08-18 5:00	Name9
13-08-18 5:00	Name11
And I need to sort the values first by Name, then by Time using this:

Code: Select all

Let>SQL=select * from unsorted.csv ORDER BY Name,Time
But as you can see there is empty spaces and other unneeded info so first I have to process the file to look like this:

Code: Select all

Time		Names
13-08-18 5:00	Name7
13-08-18 5:00	Name5
13-08-18 5:00	Name3
13-08-18 5:00	Name4
13-08-18 5:00	Name2
13-08-18 5:00	Name6
13-08-18 5:00	Name1
13-08-18 5:00	Name8
13-08-18 5:00	Name10
13-08-18 5:00	Name9
13-08-18 5:00	Name11
But as I have already loaded the file to remove the spaces by using this:

Code: Select all

    Let>n1=n1+1
    DeleteFile>C:\FTP\Zero Hour Calls\Unsorted.csv
    CSVFileToArray>Files_%n1%,arrCSV
    Let>Comma=,
    Let>Body=
    Let>x=7
    Let>Count=ARRCSV_COUNT-1
    WriteLn>C:\FTP\Zero Hour Calls\Unsorted.csv,nWLNRes,Name%Comma%ID%Comma%Time%CRLF%
    Let>y=Count-3
    While>x<Count
      Let>x=x+1
      Let>Time=ARRCSV_%x%_0
      Let>ID=ARRCSV_%x%_2
      //Add>ID,1
      Let>Name=ARRCSV_%x%_3
      //Let>Line=%Time%%Comma%%ID%%Comma%%Name%%CRLF%
      Let>Line=%Name%%Comma%%ID%%Comma%%Time%%CRLF%
      Let>Body=%Body%%Line%
    EndWhile
    WriteLn>C:\FTP\Zero Hour Calls\Unsorted.csv,nWLNRes,%Body%
    //new file to write to
    Let>new_file=C:\FTP\Zero Hour Calls\Sorted.csv
    IfFileExists>new_file
      DeleteFile>new_file
    Endif
The clean upped values now already resides in variable %Body%.

Now why must I save the values in variable %Body% to a file "unsorted.csv", reload the file again into an array (two steps), in-stead of directly load the values from %Body% into the source like this, thus eliminating one less writing and reading a file into an array which begs my original question?

Code: Select all

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%Body%;Extended Properties='text;HDR=YES;FMT=Delimited'
    DBConnect>ConStr,dbH
    Let>SQL=select * from %Body% ORDER BY Name,Time

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Can Microsoft OLEDB Source be inputted directly from variable contents?

Post by Marcus Tettmar » Wed Aug 15, 2018 7:46 am

Ok, I see what you mean.
Now why must I save the values in variable %Body% to a file "unsorted.csv", reload the file again into an array (two steps), in-stead of directly load the values from %Body% into the source like this, thus eliminating one less writing and reading a file into an array which begs my original question?
Because that's probably what Microsoft who made the database driver you are using expected.

DBQuery/DBConnect are dumb conduits. They just pass your commands onto the database driver you specify in DBConnect. In this case it is Microsoft's Jet database engine.

I suppose, in theory, there would be nothing to stop someone creating a database driver which accepted a large string instead of a filename.

I am not aware of any that do that. Why? Because it's probably not something very many people ever want to do and they probably didn't imagine it.

You'll have to consult the docs at Microsoft:
https://docs.microsoft.com/en-us/sql/ad ... rosoft-jet

Bottom line -- this isn't a constraint imposed by us. DBQuery is just passing a command to the driver specified in DBConnect. Different drivers support different things, different syntaxes. Your query in DBQuery must be in the format the driver in DBConnect expects. Connect to a different database or different type of database with a different connection string/driver and your DBQuery syntax would be different. Doesn't even have to be SQL.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

ocnuybear
Pro Scripter
Posts: 100
Joined: Sun Jul 15, 2018 5:14 pm

Re: Can Microsoft OLEDB Source be inputted directly from variable contents?

Post by ocnuybear » Wed Aug 15, 2018 8:47 am

Thank you Marcus, I realize this is a Microsoft constraint, just asked you guys as you are the experts :D

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts