VB How to extract data from a text file

OL Connect Professional Workflow
1

I have a text file that I need to use a VB scrip or JS to extract data from certain position in the line. Below example:

AAA INFO POPEYES 15.97 1
BBB ESSO AMAZON CANADA 104.51 5
CCCCCC LEGACY Used for MULTIPLE filecod 131.30 5

I need to extract the last filed and add them to get the total

1 + 5 + 5 = 11

Can someone help me to code the above in VB with many thanks.
my below code is not working

Option Explicit
const cRead=1, cWrite=2, cAppend=8
dim FSO
set FSO=CreateObject(“Scripting.FileSystemObject”)

dim fileInput, fileOutput, stringtosearch,strArray,fileName,strCurrentCounter, strNewCounter

’ Open the current input file
Set fileInput=FSO.OpenTextFile(Watch.GetJobFilename, cRead)

’ Loop through the lines. Do a search and extract desired string
do while not (fileInput.AtEndOfStream)
stringtosearch = fileInput.ReadLine()

    if ((mid(stringtosearch, 1, 5) = "AAA")) then
            'here i need to say extract from postion 76 for the lenght of 3
	'save it into a variable
	'then go to the next line till end of the file there could be multiple lines of data

)))

    end if

loop

2

As a hint. I would prefer to split the lines into array in your case based on the space.
So read only the first line and split it into an array, do a loop to find the array index. After that you can check all other lines based on that index.

Example VBS:

cvar = Watch.ExpandString("%c") 'Current jobfile content in Workflow process
cvar = Replace(cvar,chr(34),"") 'Remove double quotes from content
seperator = " " 'Seperator character for splitting line to columns
arr_lines = Split(cvar,vbLf) 'Split content to lines (use vbLf, vbCr or vbCrLf)
ref_column = "AAA" 'Column name to find
arr_headline = Split(arr_lines(0),seperator) 'Split first line
column_index = 0
'Loop over first line array to find the index of the searched column:
for sn = 0 to UBound(arr_headline)
  if(arr_headline(sn) = ref_column) then
    column_index = sn
  exit for
  end if
next
'Loop over content lines array to get the corresponding value per line:
for i = 1 to UBound(arr_lines) - 1
  myContent = Split(arr_lines(i),seperator)(column_index)
  Watch.Log "myContent " & i & ": " & myContent,2 'Log in Workflow
next

Of course it is also possible to do it in JS.

I hope it helps. :wink:

3

I think the following JavaScript code is a more elegant way of achieving this. It reads the file line by line (instead of loading it all in memory) and it uses the lastIndexOf() method to find the last space character in each line instead of parsing the string into an array, which could have a variable number of elements.

var total = 0;
var fso = new ActiveXObject("Scripting.FileSystemObject");
var dataFile = fso.OpenTextFile(Watch.GetJobFileName());
try {
  while(!dataFile.atEndOfStream){
    var oneLine = dataFile.readLine();
    var lineTotal = oneLine.slice(oneLine.lastIndexOf(" ")+1).trim();
    total += lineTotal*1
  }
} finally {
  dataFile.Close();
}
Watch.log("TOTAL: "+total,2);

Note also the use of a try...finally structure to ensure that whatever happens, the file is always closed at the end of the script.

1 Like
4

Hi Phil, Thank you for the code, your VB code finds the name of the first fields and displays the result, What I need for the code to do is to go through each line of data and search for specific wording, in this case, “AAA” was found than store the value of the last filed into a variable that in this case, the value would be “1”. Then I will save these values in a text file

Then, at the end of each month, I will have to find the total for each type. I can do the calculation, but for now, I need the script to look through the daily files find the Names and values, and save them in another text file so I can find the total of each type at the end of each month for the finance department.

for example at the end of the month, I need to generate a text file or xls file to show like below.

AAA = 999
BBB = 7346
CCCCC = 98

I hope I have explained this correctly.

5

Sorry for the delay, your reply came while I was on holiday. :slight_smile:

The following JavaScript code should do what you’re looking for. Just set the value to search for on the first line, and only lines that start with that exact pattern will be added to the total:

var ItemToSearchFor = "CCC";
var total = 0;
var fso = new ActiveXObject("Scripting.FileSystemObject");
var dataFile = fso.OpenTextFile(Watch.GetJobFileName());
try {
  while(!dataFile.atEndOfStream){
    var oneLine = dataFile.readLine();
    var lineTotal = 0;
    if(oneLine.slice(0,ItemToSearchFor.length)===ItemToSearchFor) {
      lineTotal = oneLine.slice(oneLine.lastIndexOf(" ")+1).trim();
    }
    total += lineTotal*1
  }
} finally {
  dataFile.Close();
}
Watch.log("TOTAL: "+total,2);