Trigger file to start process

OL Connect Professional Workflow
21

My data is csv and I would the script!!

Thanks

22

Hi,

Here you go. Just add to a Run Script plugin before the data mapping and be sure it is set to VBScript.

'OL - Rod
Dim objFSO, objInputFile, objOutputFile, objRegEx
Dim myCSV, strNewLineContent, strLine

Set objFSO = CreateObject("Scripting.FileSystemObject")
set objRegEx = new RegExp
objRegEx.pattern = "[^;]{1,}"

myCSV = Watch.GetJobFileName
set objInputFile = objFSO.OpenTextFile(myCSV, 1, true, 0)
strNewLineContent = ""

Do Until objInputFile.AtEndOfStream
    strLine = objInputFile.Readline
    strLine = Trim(strLine)
    If objRegEx.test(strLine)Then
        strNewLineContent = strNewLineContent & strLine & vbCrLf
    End If
Loop

objInputFile.Close
set objOutputFile = objFSO.OpenTextFile(myCSV, 2, true, 0)

objOutputFile.Write strNewLineContent
objOutputFile.Close
23

Thank you so much !! :grinning:

I am testing the script and getting an error -
W3602 : Error 0 on line 24, column 1: Microsoft VBScript runtime error: Invalid procedure call or argument

line 24 = objOutputFile.Write strNewLineContent

I am not that familiar with VB Script so not sure how to fix this.

24

What encoding is your data file? What encoding is your Data Selector in Workflow set too. (Data Options/Data Encoding when selecting data in Debug in Workflow) Looks to me that you have weirdly encoded text in your data. Like ἀ, Ö etc.

Another note, the RegEx pattern in the script needs to change based on the data delimiter. Currently "[^;]{1,}" is for semicolon. So change it to "[^,]{1,}" for comma separated data.

25

@ahaddad

At the end of all this: DO NOT FORGET TO TURN OFF THE SEQUENTIAL LOGGER. Sorry for the caps use but this is of the upmost importance. Failure to do so will produce log files in gigabytes size and impede your performances.

26

Sharne - I had to move the location of the script. It is running through debug now. I ran it through and it is removing all the extra commas at the end that was causing problems. However, it also removed two lines of good data. So looking at that now.

hamelj - No worries on the caps. Thank you for the reminder. I did turn this off already so I wouldn’t forget.

27

Late to the party, but where one of the input files is XLS(X) accessed through a database plugin you can edit the SQL adding a where clause to select only rows where the obligatory field is populated, or just test against each column separately, like so:
SELECT [F1] AS ArbitraryColumnName1, [F2] AS ArbitraryColumnName2 FROM [SheetName$] WHERE ([F1] <> ‘’ OR [F2] <> ‘’)
No scripting necessary.