Separate command where the delimiter is a Carriage Return

[transolut]

I figured out how to make a comma work as my delimiter as follows:

Let>delm=,
Separate>files,delm,filenames

The compiler would not let me use a comma diretly as in:
Separate>files,,,filenames

I am stuck however now on another text file where the delimiter is a carriage return. I tried Let>delm=Ascii>13 but I didn't figure you could stack commands that way.
Let>delm=CR doesn't work either.

Any way to do this?

[Bob Hansen]

Haven't tried this, but does this work for you?

Separate>files,%CRLF%,filenames

I suspect that it will NOT work, but is certainly worth a try.

[support]

Try:

Separate>list,%CR%,fields

[support]

Try:

Separate>list,%CR%,fields

[transolut]

Neither Separate>list,%CR%,fields or Separate>list,%CRLF%,fields work for me.

My data string is 1440 numeric values that represent floating point data values each with a CR. This is automatically retrieved from a browser based SQL query from a data gathering system.

I am using the HTTPRequest to retrieve all 1440 values into a single variable. This is my only possible format to retrieve the data in. The results in the variable are something like:

70.234(CR)60.789(CR)50.98776(CR)40.1(CR)...........

I have sucessfully used a modified version of Marcus Tettmar's example "Removing non numeric characters from a string" at http://www.mjtnet.com/scripts.hts?display+82

I just substituted a ; every time a non numerical value was found. Then I could use Separate>list,;,fields sucessfully. Curiously for a reason I don't understand the decimal points in the data string are retained using this method, which is exactly what I need. The down side is that this method pegs out the CPU usage for 30 seonds or so each time it is caled for. I call for it 30-40 times for each unique data string which each have 1440 values. The Separate> command is much more efficient in processer time.

I am concerned about the effect of running this script each night which will peg out the CPU for sevaral minutes on the same PC that has the data gathering software that is actively accumulating data, so if there are any other ideas on how to get the Separate> command to work directly with the CR, I would appreciate the insight.

Thanks!

Mike Miller
Transolut

[Lumumba]

Sorry have not found anything obvious which could solve the issue regarding the Separate> command.

Once the single string has been parsed to get the separate values, what's next ? D'y want to create an output file (*.csv, *.txt, ...)

-----

I guess you've expected to use MacroScheduler's Separate command similar to this one (which is from Autohotkey, another scripting tool)

StringSplit: Divides a string into an array of substrings using the specified delimiters.
Syntax: StringSplit, OutputArray, InputVar [, Delimiters, OmitChars, FutureUse]

or that,

while a string parsing loop is useful when you want to operate on each field contained in a string, one at a time. Parsing loops use less memory than StringSplit (since StringSplit creates a permanent array) and in most cases they are easier to use.

Loop (parse a string): Retrieves substrings (fields) from a string, one by one.
Syntax: Loop, Parse, InputVar [, Delimiters, OmitChars, FutureUse]

[support]

Hi,

It's unusual to have fields in a record separated by carriage returns. Sounds like an odd format for a file - are you sure about this?

In any case if this is what you want to do how about replace all occurences of carriage return with comma with the VBScript Replace function before using the Separate command where the delimiter is now a comma.

VBEval>Replace("%line%",chr(13),","),line
Let>comma=,
Separate>line,comma,fields

[Lumumba]

My data string is 1440 numeric values that represent floating point data values ... [output of a] SQL query from a data gathering system.

Maybe you can bypass the HTTP mambo yambo using this

SQLCMD (Commandline)

Many times I've wished for a simple way to run SQL queries from a batch file, or have a commandline-based SQL query tool, so AnalogX SQLCMD was born!

AnalogX SQLCMD is pretty simple, it just allows you to specify a database, username and password (if necessary) and a SQL query you wish to perform. The results are either output to the screen, or alternatively output into a log file that you specify. Very simple to use but if you've needed the functionality in the past, you know how handy it can be.

[Download] (43K)

[transolut]

Thank you for the responses. They really broadened my horizons. After working through your helpful replies, I figured out an easy way to accomplish my task. The system I am acquiring the data from works through a web browser. It uses Cloudscape which is a JAVA SQL database engine. Depending on how many data fields I ask for in the HTTP request, I can get them back in comma delimited format with a CR at the end of each line. If as in my case I am only asking for one field, I get one stream of data with a CR after each field.

My goal here was to collect about 50 single queries of data logged once a minute. That is 1440 samples daily for each of the 50 logs. The required output was to then create a .txt file with column headers for each log file, sample times at the beginning of each line, and writing back out the data values across the line to vertically stack the data under each header in the finished product.

I now send each sample to a file instead of a variable with the HTTP request. The CR at the end of each data value naturally stacks each one onto a different line number in the file. I can then use the ReadLn and WriteLn to sequentially access each data value and write it out as needed.

This greatly simplified my script. It smokes now and does everything I need. Thanks for the great support from all who answered my questions and to mjtnet for a great product.