See if string variable is empty. If it's empty add a value to it

Question

I'm reading in energy data from an windfarm via a CSV file. And passing it out in a new CSV file. this file need to have values to it.

But when sometimes the windfarm is offline and the my variables will be empty. So I need a way to see if there are empty and the assign a "0" to it. CSV file values get read by an OPC server so there need to be values in it.

On Error Resume Next

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objFile, strNewestFile, dtmMax
For Each objFile In objFSO.GetFolder("C:\CSV\Test\CSVOriginal").Files
    If objFile.DateLastModified > dtmMax Then
        dtmMax = objFile.DateLastModified
        strNewestFile = objFile.Path
    End If
Next

objFSO.GetFile(strNewestFile).Copy "C:\CSV\Test\CSVFlytt\Data.csv"

Const ForReading = 1

Dim arrCSVData, arrCSVDataOpc(10,1)
Dim strData, X1, Y1, X2, Y2, X3, Y3, X4, Y4, X5, Y5, X6, Y6, X7, Y7, X8, Y8, X9, Y9, X10, Y10, X11, Y11
Dim X20, X21, X22, X23, X24, X25

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\CSV\Test\CSVFlytt\Data.csv", ForReading)

Do Until objFile.AtEndOfStream
    strNextLine = objFile.ReadLine
    If Len(strNextLine) > 0 Then
        strLine = strNextLine
    End If
Loop

objFile.Close

'WScript.Echo strLine

strData = strLine
arrCSVData = Split(strData , ";")
For i = LBound(arrCSVData) To UBound(arrCSVData)
    'WScript.Echo arrCSVData(i)

    X1 = "Wm2"
    Y1 = arrCSVData(2)

    X2 = "°C"
    Y2 = arrCSVData(4)

    X3 = "m/s"
    Y3 = arrCSVData(6)

    X4 = "VR1 kWh"
    Y4 = arrCSVData(9)  
    X5 = "VR1 W"
    Y5 = arrCSVData(18)

    X6 = "VR2 kWh"
    Y6 = arrCSVData(30) 
    X7 = "VR2 W"
    Y7 = arrCSVData(39)

    X8 = "VR3 kWh"
    Y8 = arrCSVData(51) 
    X9 = "VR3 W"
    Y9 = arrCSVData(60)

    X20 = CSng(Y4)
    X21 = CSng(Y6)
    X22 = CSng(Y8)

    X10 = "Total kWh"
    Y10 = (X20 + X21 + Y8)

    X23 = CSng(Y4)
    X24 = CSng(Y7)
    X25 = CSng(Y9)

    X11 = "Total W"
    Y11 = (X23 + X24 + X25)
Next

arrCSVDataOpc(0,0)= X1
arrCSVDataOpc(0,1)= Y1
arrCSVDataOpc(1,0)= X2
arrCSVDataOpc(1,1)= Y2
arrCSVDataOpc(2,0)= X3
arrCSVDataOpc(2,1)= Y3
arrCSVDataOpc(3,0)= X4
arrCSVDataOpc(3,1)= Y4
arrCSVDataOpc(4,0)= X5
arrCSVDataOpc(4,1)= Y5
arrCSVDataOpc(5,0)= X6
arrCSVDataOpc(5,1)= Y6
arrCSVDataOpc(6,0)= X7
arrCSVDataOpc(6,1)= Y7
arrCSVDataOpc(7,0)= X8
arrCSVDataOpc(7,1)= Y8
arrCSVDataOpc(8,0)= X9
arrCSVDataOpc(8,1)= Y9
arrCSVDataOpc(9,0)= X10
arrCSVDataOpc(9,1)= Y10
arrCSVDataOpc(10,0)= X11
arrCSVDataOpc(10,1)= Y11
arrCSVDataOpc(10,0)= X11
arrCSVDataOpc(10,1)= Y11

Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim filesys, testfile 
Set filesys = CreateObject("Scripting.FileSystemObject") 
Set testfile = oFSO.CreateTextFile("C:\CSV\Test\OPC\data.csv", True)
testfile.WriteLine X1 & ";" & Y1
testfile.WriteLine X2 & ";" & Y2
testfile.WriteLine X3 & ";" & Y3
testfile.WriteLine X4 & ";" & Y4
testfile.WriteLine X5 & ";" & Y5
testfile.WriteLine X6 & ";" & Y6
testfile.WriteLine X7 & ";" & Y7
testfile.WriteLine X8 & ";" & Y8
testfile.WriteLine X9 & ";" & Y9
testfile.WriteLine X10 & ";" & Y10
testfile.WriteLine X11 & ";" & Y11
testfile.Close

like: Y1 = iif(arrCSVData(2)="","0",arrCSVData(2)) ? check the iif function — Flakes
– Flakes, Commented Aug 14, 2015 at 13:28
What exactly is empty? The array? Or the fields of the array? Also, do you actually mean Empty when you say "empty"? — Ansgar Wiechers
– Ansgar Wiechers, Commented Aug 14, 2015 at 14:01
@SearchAndResQ - VBScript doesn't have the IIf() function. — Bond
– Bond, Commented Aug 14, 2015 at 15:15

JosefZ · Accepted Answer · 2015-08-15 06:55:51Z

1

Some remarks to your code:

use Option Explicit statement (the 1st line of code);
use On Error Goto 0 statement (the 2nd line of code) instead of On Error Resume Next;
redefining Set objFSO = CreateObject("Scripting.FileSystemObject") seems to be superabundant: the first occurrence of this statement should suffice;
I'd use strNextLine = Trim( objFile.ReadLine) for next If Len(strNextLine) > 0 test accuracy;
If UBound(arrCSVData) < 60 Then Redim Preserve arrCSVData(60) to ensure all used array items are present and to prevent the VBScript runtime error Subscript out of range: '[number: #]';
omit the For i = LBound(arrCSVData) To UBound(arrCSVData) loop (i.e. this line and appropriate Next): it loops the same code UBound(arrCSVData)+1 times;
use e.g. Y9 = replaceEmpty(arrCSVData(60), "0") if the replaceEmpty function is defined as follows:

Function replaceEmpty( sValueToTest, defaultVal)
  If IsNull( sValueToTest) Then
    replaceEmpty = ""
  Else
    replaceEmpty = CStr(sValueToTest)
  End If
  If Trim( replaceEmpty) = "" Then replaceEmpty = defaultVal
End Function

answered Aug 15, 2015 at 6:55

JosefZ

30.6k6 gold badges52 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ansgar Wiechers Over a year ago

Empty and Null are not the same.

JosefZ Over a year ago

@AnsgarWiechers I know the difference among Empty (== uninitialized) vs. Null (== no valid data) vs. "" (== string of zero length, commonly named empty string). The If IsNull( sValueToTest) ... check is here for the sake of bullet-proof code because the CStr function raises a VBScript runtime error if its argument expression is Null while CStr( Empty) is valid and results to "".

Ansgar Wiechers Over a year ago

Does the person reading your answer know it too? If not I'd consider it worth explaining.

HarveyFrench · Accepted Answer · 2015-08-14 13:39:47Z

0

In your for loop when you assign a value to the variables from the array with your data. Change the code as per the example below. ie for every data item that can potentially be equal to "" when you need it to be 0 change the corresponding line

ie change

Y1 = arrCSVData(2)

to

Y1 = iif( arrCSVData(2)="",0 ,arrCSVData(2) )

Also, I was wondering why you are using on error resume next.

answered Aug 14, 2015 at 13:39

HarveyFrench

4,6184 gold badges22 silver badges36 bronze badges

Collectives™ on Stack Overflow

See if string variable is empty. If it's empty add a value to it

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related