• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

Determining array length or if it exists

Hello Experts,

I'm following the example here:
Split multi value column into multiple records

The data I'm using is different from the example.  The flat file I'm accessing doesn't only contain multi-value fields.  It contains a mixture of both MV fields and regular single records.  So the script task works great if I only have one or the other.  When the file contains both I get an "Index was outside the bounds of the array" error.  I am trying to determine if the array exists or if it has more than one value.  A small example of the data:

Code     Amount     Type
"1,2,3","10.00,5.00,7.00","A,,C"
1,10.00,A

Here is the modified script from the example above:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' First we are converting the comma seperated list into a string array.
        ' You can change the comma if you are using an other seperator like | or ;

        Dim Opcodes As String() = Row.SVCOPCODES.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim CWI As String() = Row.CWI.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim LaborAmt As String() = Row.LABOR.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim BillHR As String() = Row.BILLHR.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim JobCost As String() = Row.JOBCOST.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim JobTime As String() = Row.JTIME.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim RO As String() = Row.RO.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim Prefix As String() = Row.PFX.ToString().Split(New Char() {","c}, StringSplitOptions.None)

        ' Counter var used the loop through the string array
        Dim i As Integer = 0

        ' Looping through string array with student names
        While i < Opcodes.Length And Opcodes.Length > 1
            ' Start a new row in the output
            Output0Buffer.AddRow()

            ' Pass through all columns that you need downstream the data flow
            Output0Buffer.RONumber = Row.RO
            Output0Buffer.Prefix = Row.PFX

            ' This is the splitted column. Take the [n] element from the array
            ' and put it in the new column.
            If Opcodes.Length = 1 Then
                Output0Buffer.OPCODES = Opcodes.ToString
            Else
                Output0Buffer.OPCODES = Opcodes(i).ToString
            End If
            If CWI.Length = 1 Then
                Output0Buffer.CWI = CWI.ToString
            Else
                Output0Buffer.CWI = CWI(i).ToString
            End If
            If LaborAmt.Length = 1 Then
                Output0Buffer.LaborAmt = LaborAmt.ToString
            Else
                Output0Buffer.LaborAmt = LaborAmt(i).ToString
            End If

            If BillHR.Length = 1 Then
                ' handle missing bill hr, replace with 0
                If BillHR.Length < 1 Then
                    Output0Buffer.BillHR = "0"
                Else
                    Output0Buffer.BillHR = BillHR.ToString
                End If
            Else
                ' handle missing bill hr, replace with 0
                If BillHR(i).Length < 1 Then
                    Output0Buffer.BillHR = "0"
                Else
                    Output0Buffer.BillHR = BillHR(i).ToString
                End If
            End If

            If JobCost.Length = 1 Then
                Output0Buffer.JobCost = JobCost.ToString
            Else
                Output0Buffer.JobCost = JobCost(i).ToString
            End If
            If JobTime.Length = 1 Then
                Output0Buffer.JobTime = JobTime.ToString
            Else
                Output0Buffer.JobTime = JobTime(i).ToString
            End If

            ' Increase counter to go the next value
            i += 1
        End While
    End Sub

Open in new window


It is possible that the BIllHR field will be empty, which is why I set it to "0".  How do I get this script to work properly with the csv I'm importing?  As you can see I tried saying if the length of the array is only 1 (single record, not MV) then just output that value.
0
jay-are
Asked:
jay-are
  • 10
  • 6
1 Solution
 
rspahitzCommented:
not sure if this is the problem but...

While i < Opcodes.Length And Opcodes.Length > 1

Open in new window


This is the same as:

While Opcodes.Length > 1 And i < Opcodes.Length

Open in new window


and

While Opcodes.Length > 1 And Opcodes.Length > i

Open in new window


Looking at it this way, the
Opcodes.Length > 1

Open in new window

portion is useless if i>1
0
 
jay-areAuthor Commented:
Yeah I've made so many changes at this point I probably need to start over. I just tried changing to this:

If Not Row.SVCOPCODES.Contains(",") Then
                Output0Buffer.OPCODES = Opcodes.ToString
            Else
                Output0Buffer.OPCODES = Opcodes(i).ToString
            End If

Open in new window


This works for most of the columns, but JOBCOST and JTIME.  I think because they are empty in some cases.  Also, now I don't get any of the non-MV rows...hah!  

I just want to split the MV columns into rows.  The example I posted works fine if I only have MV columns.  As soon as it hits a row that doesn't contain multivalues I get the index out of bounds error.
0
 
jay-areAuthor Commented:
Ok here's where I am now:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' First we are converting the comma seperated list into a string array.
        ' You can change the comma if you are using an other seperator like | or ;

        Dim Opcodes As String() = Row.SVCOPCODES.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim CWI As String() = Row.CWI.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim LaborAmt As String() = Row.LABOR.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim BillHR As String() = Row.BILLHR.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim JobCost As String() = Row.JOBCOST.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim JobTime As String() = Row.JTIME.ToString().Split(New Char() {","c}, StringSplitOptions.None)

        Dim RO As String() = Row.RO.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        Dim Prefix As String() = Row.PFX.ToString().Split(New Char() {","c}, StringSplitOptions.None)

        ' Counter var used the loop through the string array
        Dim i As Integer = 0
        If Row.SVCOPCODES.Contains(",") Then
            ' Looping through string array with student names
            While i < Opcodes.Length 'And Opcodes.Length > 1
                ' Start a new row in the output
                Output0Buffer.AddRow()

                ' Pass through all columns that you need downstream the data flow
                Output0Buffer.RONumber = Row.RO
                Output0Buffer.Prefix = Row.PFX


                ' This is the splitted column. Take the [n] element from the array
                ' and put it in the new column.
                Output0Buffer.OPCODES = Opcodes(i).ToString
                Output0Buffer.CWI = CWI(i).ToString
                Output0Buffer.LaborAmt = LaborAmt(i).ToString
                ' handle missing bill hr, replace with 0
                If BillHR(i).Length < 1 Then
                    Output0Buffer.BillHR = "0"
                Else
                    Output0Buffer.BillHR = BillHR(i).ToString
                End If
                If Row.JOBCOST.Length < 1 Then
                    Output0Buffer.JobCost = "0"
                Else
                    Output0Buffer.JobCost = JobCost(i).ToString
                End If
                If Row.JTIME.Length < 1 Then
                    Output0Buffer.JobTime = "0"
                Else
                    Output0Buffer.JobTime = JobTime(i).ToString
                End If


                ' Increase counter to go the next value
                i += 1
            End While
        Else
            '' Start a new row in the output
            Output0Buffer.AddRow()

            '' Pass through all columns that you need downstream the data flow
            Output0Buffer.RONumber = Row.RO
            Output0Buffer.Prefix = Row.PFX

            ' This is the splitted column. Take the [n] element from the array
            ' and put it in the new column.
            Output0Buffer.OPCODES = Row.SVCOPCODES
            Output0Buffer.CWI = Row.CWI
            Output0Buffer.LaborAmt = Row.LABOR
            ' handle missing bill hr, replace with 0
            If BillHR.Length < 1 Then
                Output0Buffer.BillHR = "0"
            Else
                Output0Buffer.BillHR = Row.BILLHR
            End If
            If JobCost.Length < 1 Then
                Output0Buffer.JobCost = "0"
            Else
                Output0Buffer.JobCost = Row.JOBCOST
            End If
            If JobTime.Length < 1 Then
                Output0Buffer.JobTime = "0"
            Else
                Output0Buffer.JobTime = Row.JTIME
            End If

        End If
    End Sub

Open in new window



There are a few of the MV columns that are blank. I think that may be what's throwing my out of bounds error now.  The current script works fine except when I exclude MV JobCost and JobTime.  With those two removed from the output the script works.  I've attached the CSV I'm importing so you can see the data in those rows. Not sure how to say "If JobCost is nothing then JobCost = "0".
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jay-areAuthor Commented:
Helps if you click "upload file"...
invoices.csv
0
 
rspahitzCommented:
So it seems that your two sample inputs are
"1,2,3","10.00,5.00,7.00","A,,C"
1,10.00,A
and they will be stored in Row.SVCOPCODES

VB used to have its own Split command that could be used on strings (rather than characters) but I think they did away with it. With that you could have split on ","

But in that vein, how about first searching to see if "," exists; if so then you have the MV data otherwise you don't (unless there are some special cases that work differently.)

If Row.SVCOPCODES.ToString().IndexOf(""",""") >= 0 Then
' MV data
Else
' single item data
End If
0
 
jay-areAuthor Commented:
rspahitz,

That is correct.  See my code above.  I added in a row.SVCOPCODES.contains(",") in order to process the MV data differently from the single item data.  That works fine.  I'm just stuck on bad data for JobCost and JobTime.  It seems that if the JobCost is blank or null/empty it throws the 'index was outside the bounds of the array' error.

For MV's I've tried:
If JobCost(i) Is Nothing Then
                    Output0Buffer.JobCost = "0"
                Else
                    Output0Buffer.JobCost = JobCost(i).ToString
                End If

Open in new window


How do I identify rows where the JobCost and JobTime don't actually exist.  The data shows just a column delimiter for these every so often.
0
 
rspahitzCommented:
>If JobCost(i) Is Nothing Then

I'm not sure where you tried that but it should have nothing to do with matching against Nothing; instead, from the error, it seems that JobCost does not have as many elements as you think.

Without re-writing things, here's a few things to investigate:

Debug.Print(JobCost.length)

This will tell you the number of items in the JobCost array (e.g. 7 means you have elements 0 to 6) The value appears in the Output or Immediate window (which you may have to open to see.)
Make sure that you are no referencing element JobCost.Length (the code you have seems correct.)

Have you ever tried using the debugger? Right-Click on a line and add Breakpoint.  When you run the code, it will stop on any line with a maroon circle in the left sidebar (if it gets there or if it is allowed to stop there--it won't typically stop on comments and some Dim's).  You can then use the debug menu to examine other things or continue, either one line at a time or until the next breakpoint.
0
 
jay-areAuthor Commented:
rspahitz,

I'm using a script component inside SSIS.  The script is being processed inside a data task flow. It receives input from a flat file source (csv) and the outputs it to an ole db destination.  

This script component won't let me debug like I normally would in VS.  I try to run debug and it does nothing at all.  I assume this is because I'm inside an SSIS package instead of the normal dev environment.

So I sit here making changes and executing the package to see what sticks.

My latest is altering the declaration of JobCost like so:

Dim JobCost As String()
        If Row.JOBCOST_IsNull = True Then
            'JobCost = "0"
        Else
            JobCost = Row.JOBCOST.ToString().Split(New Char() {","c}, StringSplitOptions.None)
        End If

Open in new window


I assume that if jobcost is null I can assign the array a default value?  Sorry, I'm all over the place here.  :)
0
 
rspahitzCommented:
Frustrating...but since you have your function set up to receive an Input0Buffer object, then you can create a subroutine to manually create a few and feed them into your Input0_ProcessInputRow sub.  If necessary, you might have to pull the code out into another project until you get the code right.  If you don't have an Input0Buffer object available, you could always create a String and temporarily change the parameter to support strings while you test it.  Hopefully that helps get you through this.
Basically, you need to find a way to isolate where it's going wrong and extracting the sub seems like a good start.
0
 
jay-areAuthor Commented:
How would I create a string to mimic the columns and field values?  I can do that and test the rows in the csv I believe are causing the trouble.  Would this be similar to setting up a datatable with string values?
0
 
jay-areAuthor Commented:
You got me curious about debugging a script component.  You can't use breakpoints but you can use messagebox!  

So I put in a messagebox to display all JobCost(i).  I clicked through about 30 boxes before it failed. It looks like there are instances in the data where there are X number of opcodes, say 4, but only 3 JobCosts.  The column doesn't contain a trailing comma to show there was no value for the 4th opcode.  So in this case it is outside the array index.  Is there a way to work around this?  

I get this error because Opcodes.length is greater than the length of JobCost?

While i < Opcodes.Length
'''
End While

Open in new window

0
 
jay-areAuthor Commented:
Thanks to you pushing me into reading about debugging I was able to figure this out.  It only took me most of the day.

If Not Opcodes.Length > JobCost.Length Then
                    Output0Buffer.JobCost = JobCost(i).ToString
                Else
                    Output0Buffer.JobCost = "0"
                End If

Open in new window


This works nicely.  The script runs and I get values.  I did the same for JobTime and it outputs normally as well!  

Thank you for the help!
0
 
jay-areAuthor Commented:
Due to debugging with a MessageBox I was able to locate the actual data that was causing the issue.
0
 
rspahitzCommented:
I'm not familiar with the Input0Buffer data type.  It seems like it's a class that mimics the way a database table row works.

At this point, you'll need to get better feedback from the sub.  One possibility is to simply log things somewhere and see what shows up.  Debug.Print may work if the sub gives you access to the Intermediate or Output window.  Otherwise you may have to open a text file (maybe locally if the sub has permissions) and just write all the sequence lines there.

I suspect that with a bit of review, you'll be able to find out which line is causing the problem.

 
so you might do things like this:
        Dim Opcodes As String() = Row.SVCOPCODES.ToString().Split(New Char() {","c}, StringSplitOptions.None)
Debug.Print (OpCodes.length)
        Dim CWI As String() = Row.CWI.ToString().Split(New Char() {","c}, StringSplitOptions.None)
Debug.Print (CWI.length)
        Dim LaborAmt As String() = Row.LABOR.ToString().Split(New Char() {","c}, StringSplitOptions.None)
Debug.Print (LaborAmt.length)
        Dim BillHR As String() = Row.BILLHR.ToString().Split(New Char() {","c}, StringSplitOptions.None)
Debug.Print (BillHR.length)
        Dim JobCost As String() = Row.JOBCOST.ToString().Split(New Char() {","c}, StringSplitOptions.None)
Debug.Print( JobCost.length)
        Dim JobTime As String() = Row.JTIME.ToString().Split(New Char() {","c}, StringSplitOptions.None)
Debug.Print (JobTime.length)
...
       If Row.SVCOPCODES.Contains(",") Then
            ' Looping through string array with student names
Debug.Print("opcodes contains a comma")

Open in new window

etc.
0
 
rspahitzCommented:
I guess you figured it out before my post.  You kinda did it the hard way, but 30 messageboxes is not too bad.
Seems that you found the solution by verifying that each data element had the correct number of sub-items as the opcodes group.
0
 
jay-areAuthor Commented:
Yeah I should have compared the count of opcodes to JobCost prior to doing this, but I didn't and I paid the price for it.  I appreciate your help!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now