Link to home
Start Free TrialLog in
Avatar of endrec
endrec

asked on

How to convert an ArrayList to Array?

I need help converting the output of this function, an ArrayList, into an array.  I would like the ArrayList to be converted to an array and stored in an array named splitout.

Private Function ParseQuotedCSV2(ByVal strInput As String) As ArrayList
        Dim a As New ArrayList
        Dim s As String() = strInput.Split(",")
        Dim i As Integer
        For i = s.Length - 1 To 1 Step -1
            If s(i).TrimEnd.LastIndexOf(Chr(34)) = s(i).TrimEnd.Length - 1 Then
                If s(i).TrimStart.IndexOf(Chr(34)) <> 0 Then
                    s(i - 1) = s(i - 1) & "," & s(i)
                    s(i) = "|||" 'This is an 'ignore' marker which needs to be set to something that would never appear in the input string
                End If
            End If
        Next
        For i = 0 To s.Length - 1
            If s(i) <> "|||" Then 'See comment above
                If s(i).TrimStart.IndexOf(Chr(34)) = 0 Then
                    s(i) = s(i).Remove(s(i).IndexOf(Chr(34)), 1)
                    s(i) = s(i).Remove(s(i).LastIndexOf(Chr(34)), 1)
                End If
                a.Add(s(i))
            End If
        Next
        Return a

End Function
Avatar of bchoor
bchoor
Flag of United States of America image

try this:

Dim splitout() As string

splitOut = a.toArray(getType(string))


HTH
~BC
Avatar of endrec
endrec

ASKER

I receive the following error on splitout(14).

"Conversion from string "68 Item T+30" to type 'Double' is not valid."
Does this work?

Dim al As ArrayList = ParseQuotedCSV2("1,2,3")
Dim splitout As Array = al.ToArray(GetType(String))
>>
I receive the following error on splitout(14).

"Conversion from string "68 Item T+30" to type 'Double' is not valid."
<<

That has nothing to do with the the conversion from the ArrayList to the Array.  It means that the function has put "68 Item T+30" in the ArrayList element numbered 14 - that is, the 15th element.  So what was the string that you passed to the function when it produced that result and from which you were expecting a value that would convert to a double in its 15th comma-delimited position?  If you can post that string we can see whether the error is in the function and, if so, where.

Roger
I agree with Roger on this - its seems that the error is in the parsing not the conversion.

You can try to run a check on what value is being added to the arraylist, verifying that it is a double, if not reject that value. Using TryParse

Dim dbl As double

If System.Double.TryParse(s(i), dbl)=True Then
  a.Add(s(i))
End If


HTH
~BC
Avatar of endrec

ASKER

This is what the actual input line is:
Soft Agreement 1-1-01,,,License For Software,Outbound,Ok,Ok – 100%,,1-2-01,, Four,Six, Donaldw, Software, Company Inc.,1,Contact Ray,No,68 Item T+30,5300

I think the problem is that the function is ignoring blank values i.e. ,, and not including them in the array.  It is also placing some commas in their own section of the array.

The type double error for "68 Item T+30" came from a validation effort that should be performed on the index with the index 14 i.e. the number 1 in the string above.
Here's a revision of that function which (a) does deal with blank fields and (b) returns a string array rather than an arraylist.

    Private Function ParseQuotedCSV3(ByVal strInput As String) As String()
        Dim a As New ArrayList
        Dim s As String() = strInput.Split(",")
        Dim i As Integer
        For i = s.Length - 1 To 1 Step -1
            If s(i).TrimEnd.Length > 0 Then
                If s(i).TrimEnd.LastIndexOf(Chr(34)) = s(i).TrimEnd.Length - 1 Then
                    If s(i).TrimStart.IndexOf(Chr(34)) <> 0 Then
                        s(i - 1) = s(i - 1) & "," & s(i)
                        s(i) = "|||" 'This is an 'ignore' marker which needs to be set to something that would never appear in the input string
                    End If
                End If
            End If
        Next
        For i = 0 To s.Length - 1
            If s(i) <> "|||" Then 'See comment above
                If s(i).TrimStart.IndexOf(Chr(34)) = 0 Then
                    s(i) = s(i).Remove(s(i).IndexOf(Chr(34)), 1)
                    s(i) = s(i).Remove(s(i).LastIndexOf(Chr(34)), 1)
                End If
                a.Add(s(i).ToString)
            End If
        Next
        Dim ss(a.Count - 1) As String
        a.CopyTo(ss)
        Return ss
    End Function

Roger
Avatar of endrec

ASKER

Thank you for your help.

The function works great, but I ran into some data that caused an error in it.  Some unrecognizeable character between X and Subsidiaries in the text below.  In Notepade it appears as [], but in Excel it starts a new line similar to Chr(10) or Chr(13).  However using the replace function for Chr(10) or Chr(13) does not replace this character.

"Related Companies - Subsidiaries designated in writing signed by X
Subsidiaries - (i) majority of control"

(The line is actually in quotes, e.g. 1, "this line", 3")

X[]Subsidiaries.



Error:

ArguementOutofRangeException

StartIndex cannot be less than zero.
Parameter name: startIndex

on the line s(i) = s(i).Remove(s(i).LastIndexOf(Chr(34)), 1
Avatar of endrec

ASKER

Ok, when I tried to narrow down what this mystery character is, I get the following error using Mid on it's character position

"Length of argument 'String' must be greater than zero."

How would I get rid of this odd character?
Try putting the offending string through this

    Private Sub checkchars(ByVal teststring As String)
        'For i As Integer = 0 To teststring.Length - 1
        For i As Integer = 63 To 72
            Debug.Write(i.ToString & "/" & Asc(teststring.Substring(i, 1)) & ":")
        Next
        Debug.Flush()
    End Sub

Note that I've "customised" it.  I reckon that the offending character/s is/are at position 66 in the string, so I've just done the iteration from 63 to 72.  Otherwise the output becomes difficult to analyse.

Until we know what it is, it is difficult for me to see how it is throwing the function out.

Roger
Avatar of endrec

ASKER

For some reason VB is not reading the character.  It is considering the end of the string this character, but there is text following it.  When your function above gets to it's character position it breaks.  I put the upper bound as greater than the mystery character's position, so maybe this character marks the end of a string somehow.  That would explain why the ArguementOutofRangeException occurs in both your test sub and the original function, as this character marks the end of a string or VB cannot interpret it (which I doubt).
Avatar of endrec

ASKER

When I just read the string into a label on a form VB stops reading the string where the mystery character is and disregards any information following the character.
It sounds to me like chr(0).  Try .Replace(Chr(0), "") on it.  Change the start of the function to this

    Private Function ParseQuotedCSV3(ByVal strInput As String) As String()
        Dim a As New ArrayList
        Dim s1 As String = strInput.Replace(Chr(0), "") '<<< NEW LINE
        Dim s As String() = s1.Split(",") '<<< ALTERED LINE

Roger
 
Avatar of endrec

ASKER

I edited the function to replace Chr(0), Chr(10), and Chr(13), but the odd character still persists.  Anyone have any ideas?
Please can you post the exact code by which you put the troublesome string through the test sub?  From your description of "the ArguementOutofRangeException [occurring in the] test sub" after you had "put the upper bound as greater than the mystery character's position" it sounds to me as though the string that was passed to the sub was indeed truncated at the mystery character.  What you need to do is take the string concerned exactly as it comes in from Excel.  Going back to what looks like the source of the data you are having trouble with

https://www.experts-exchange.com/questions/21920064/Validation-On-Array-and-Files-That-contains-a-variable-number-of-fields-per-line-and-I-keep-getting-an-index-out-of-range-error.html

you are, as I understand it, bringing over (what you think are) full rows from Excel with a datareader.

                strLine = objStreamReader.ReadLine

The first thing you need to check (with the data from the row concerned) is whether the length of strLine is the same as the full row in Excel or whether the datareader has already truncated it at the mystery character.  You need to do this by checking the .Length of strLIne, rather than trying to inspect the text in it because any display of text as text in VB.NET is likely to stop when it reaches the mystery character even if there is indeed other text after it.  But .Length will show how many characters there are in total even if, after a certain point, they will not display.

If the whole of the row is there then try putting the WHOLE of the row - that is, strLine as it stands at that point - through the test sub, in the following form

    Private Sub checkchars(ByVal teststring As String)
        For i As Integer = 0 To teststring.Length - 1
            If Asc(teststring.Substring(i, 1)) < 32 Or Asc(teststring.Substring(i, 1)) > 126 Then
                 Debug.Write(i.ToString & "/" & Asc(teststring.Substring(i, 1)) & ":")
            End If
        Next
        Debug.Flush()
    End Sub

That will restrict the output to "unusual" character codes.  I have, incidentally, just done that with a string composed with the following code

    Private Function makestring() As String
        Dim s As String
        For i As Integer = 0 To 255
            s &= Chr(i)
        Next
        Return s
    End Function

with no problems.

I share your disbelief that "VB cannot interpret it", but until we can find out what it is ...

Roger
Ignore that reference to Excel.  It is, of course, from a csv text file.  It was just that you mentioned how it appeared in Excel.

Roger
Avatar of endrec

ASKER

VB breaks even during your function when it hits this character.  Would it help if I posted a text file for you to see if there is a way you can read it line by line in VB.NET.  Basically VB is skipping all the text on the rest of the line that the mystery character appears on and the sub continues on reading other lines.  I need all the text to appear though as validation is done on the array.



Namespace to add to your form:
Imports System.IO

Sub I use to read the text/csv files:

    Private Sub subStreamReader(ByVal strFilePath As String)
        Dim objStreamReader As StreamReader
        Dim strLine As String
        Dim rowcount As Integer


        'Pass the file path and the file name to the StreamReader constructor.
        objStreamReader = New StreamReader(strFilePath)

        'Read the first line of text.
        strLine = objStreamReader.ReadLine


        'Continue to read until you reach the end of the file.
        Do While Not strLine Is Nothing


            'Write the line to the Console window.
            txtCSVContent.Text = txtCSVContent.Text & strLine & vbCrLf
            ' Console.WriteLine(strLine)

            'Read the next line.
            strLine = objStreamReader.ReadLine
            'Adds one to a running count of the number of rows

            rowcount = rowcount + 1

        Loop

        'Displays the total number of rows in the file
        txtCSVContent.Text = txtCSVContent.Text & vbCrLf
        lblStatus.Text = "Row Count (excluding first header row): " & rowcount - 1
        'Close the file.

        objStreamReader.Close()
        'Console.ReadLine()

    End Sub
>>
Would it help if I posted a text file for you to see if there is a way you can read it line by line in VB.NET.
<<

Yes

Go here http://www.ee-stuff.com/

Login.  Go to the Expert Area tab.  Then Upload a new file and follow the instructions (you'll need the this question number - 21922018 - to put with it).  Then post the URL back here.

Roger
Avatar of endrec

ASKER

I uploaded a .CSV with the mystey character in it.

https://filedb.experts-exchange.com/incoming/ee-stuff/419-DataTest-6-27-06.zip
Got it.  It's really my bedtime (I'm in the UK and it's after midnight) but I couldn't resist a quick look.  The troublesome character is chr(10) but - and this is the problem - it is being recognised by StreamReader.Readline as an end of line.  So

Test 3,,,Other,signed,Other,,1/15/1996,1/15/2009,1/15/1996,TST0004,jman,Test Generic Terms Bulkload,"Adobe

is being treated as one line/record and

Subsidiaries - (i) majority of shares;(ii) majority of equity interest",1,J:\\file3.doc,No,AnotherTest,123456-78945

is being treated as a separate line/record.

I think the way round it is going to have to be to use StreamReader.ReadToEnd to get the whole file in one lump and then split it up with a Regex.Split - which will take two characters, ie. CR & LF, whereas String.Split will take only one - into lines/records and then replace any remaining chr(10) with spaces and then do the rest of your processing.

That may be enough for you to sort it out.  I'm not up to coding it myself right now.  I'm off to bed.  But I'll look in in the morning to see if you need any further help with that.

Roger
Avatar of endrec

ASKER

Thanks!!!!  I'm not much of a coder myself, i'm just trying to make sure this data does not have any errors when it's loaded into a database.  With the rest of the code, it performs validations by line.  Is there a way to do what your are talking about above with ReadToEnd and the Regex.Split, but still move through the remaining array/string (with the mid-line Chr(10) ) line by line as .ReadLine would do?
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of endrec

ASKER

Roger did an amazing job with this!!!