Discuss "seprate coulmn from line." in the ASP Development forum.

guddu · #1 July 16th, 2008, 12:12 PM

this is exist function in my code.this is used for splitting coulmn form a line .
coulmn is seprated by comma.i have maximum 4 coulmns in a line.but problem is my fourth column can contain more than one value
like this
200044,ABC125,Y,FD,FC,FY
but problem is this is working fine if coulmn 4 has no value or only one value.but suppose it has more than one value like example i mentioned then in this case only it return first value of col 4 which is FD.but i want those value which is after FD.how to modify this function.basically this function is written in vb.
hope it makes some sense.

Code:

Function mbGetLineArray(ByVal vsSource As String, _
                                ByVal vlColumnCount As Long, _
                                ByRef rasLineArray() As String, _
                                Optional vsSeparator As String = ",") As Boolean


Dim bReturn As Boolean
Dim nPosition As Integer
Dim nLength As Integer
Dim n1 As Integer
    On Error GoTo ErrorHandler

    ReDim rasLineArray(vlColumnCount - 1)
  
    For n1 = 0 To (vlColumnCount - 2)
      If InStr(1, vsSource, ",") = 0 Then vsSource = vsSource & ","
      
      If Left(vsSource, 1) = """" Then
        vsSource = Mid(vsSource, 2)
        rasLineArray(n1) = Left(vsSource, InStr(1, vsSource, """,") - 1)
        
        nPosition = 1
        Do Until InStr(nPosition, rasLineArray(n1), """""") = 0
          nPosition = InStr(nPosition, rasLineArray(n1), """""")
          If nPosition = 1 Then
            rasLineArray(n1) = Mid(rasLineArray(n1), nPosition + 1)
          Else
            rasLineArray(n1) = Left(rasLineArray(n1), nPosition - 1) & Mid(rasLineArray(n1), nPosition + 1)
          End If
          nPosition = nPosition + 1
        Loop
        
        vsSource = Mid(vsSource, InStr(1, vsSource, """,") + 2)
      
      Else
        nLength = InStr(1, vsSource, ",") - 1
        If nLength = 0 Then
          'nothing to do for this element as it is blank, but we want to continue as
          'there are more elements to be parsed from this string.
        ElseIf nLength > 0 Then
          rasLineArray(n1) = Left(vsSource, nLength)
        Else
          'we have reached the final element so there is no point continuing to loop.
          'the final value will be handled by the code following the for-next loop.
          Exit For
        End If
        vsSource = Mid(vsSource, nLength + 2)
      End If
    Next n1
    
    If vsSource <> vbNullString Then
    
        If Left(vsSource, 1) = """" Then
          If InStr(1, vsSource, """,") <> 0 Then
            vsSource = Mid(vsSource, 2)
            vsSource = Left(vsSource, InStr(1, vsSource, """,") - 1)
            vsSource = Replace(vsSource, """""", """")
          Else
            vsSource = Mid(vsSource, 2)
            vsSource = Left(vsSource, Len(vsSource) - 1)
          End If
        Else
          If InStr(1, vsSource, ",") <> 0 Then
            vsSource = Left(vsSource, InStr(1, vsSource, ",") - 1)
          End If
        End If
        
        rasLineArray(n1) = Trim(vsSource)
        
    End If
    

End Function

vsSource - source string to split up
vlColumnCount - expected number of columns
[vsSeparator] - column separator (default to comma)
rasLineArray - array containing split up line

jmurrayhead · #2 July 16th, 2008, 12:32 PM

Why not just use a different delimiter for that 4th element?

guddu · #3 July 16th, 2008, 12:36 PM

no i can use bcoz csv file is coming from client.so i can not make any chnages to it.

jmurrayhead · #4 July 16th, 2008, 12:49 PM

I see...will there always only be four positions? If so, when you are in the 4th position of the loop, you can perform some data formatting and then exit the loop.

guddu · #5 July 16th, 2008, 12:54 PM

yes only 4 coulmns but 4 th one may more than one value with seprated comma.after spliiting i m passing values to stored procedure.so my arrayname(3) mean 4th coulmn has values like FD,FY,FH.this code is not written by me.so can u give me some idea where exactly i have to check 4 th coulmn and exit from loop.

jmurrayhead · #6 July 16th, 2008, 01:01 PM

I'm not familiar with this function so it would be difficult for me to say. However, if you can attach a sample ASP page and populate the array used with some sample data, I might be able to give you a solution when I get home from work.

guddu · #7 July 16th, 2008, 02:26 PM

this is the sample data rite now i have
200044,ABC125,Y,FD,FC,FY
200055,,,
200048,ABC124,N,FD
200044,ABC125,Y,FD,FC,FY,dd,ss

i have to done this at vb end only.its similar to vbscript

jmurrayhead · #8 July 16th, 2008, 06:32 PM

Wait, just realized that you said this is a VB file. Even though this is similar to ASP, I will move it to the Visual Basic forum if this is a VB file.

As for your code problem, look for the portion of the script where it is iterating through your array. This is where you will want to check if it is the 4th position or not. If it is, then exit the loop and continue processing as normal.

guddu · #9 July 17th, 2008, 04:53 AM

hey i found the place where i have to do some stuff .but now how to go ahead now.what to do now?

Code:

 If nLength = 0 Then
          'nothing to do for this element as it is blank, but we want to continue as
          'there are more elements to be parsed from this string.
        ElseIf nLength > 0 Then
          rasLineArray(n1) = Left(vsSource, nLength)
        Else
          'we have reached the final element so there is no point continuing to loop.
          'the final value will be handled by the code following the for-next loop.
          Exit For
        End If

guddu · #10 July 17th, 2008, 08:30 AM

i gotted now.here is final code

Code:

 ReDim rasLineArray(vlColumnCount - 1)
  
    For n1 = 0 To (vlColumnCount - 2)
      If InStr(1, vsSource, ",") = 0 Then vsSource = vsSource & ","
      
      If Left(vsSource, 1) = """" Then
        vsSource = Mid(vsSource, 2)
        rasLineArray(n1) = Left(vsSource, InStr(1, vsSource, """,") - 1)
        
        nPosition = 1
        Do Until InStr(nPosition, rasLineArray(n1), """""") = 0
          nPosition = InStr(nPosition, rasLineArray(n1), """""")
          If nPosition = 1 Then
            rasLineArray(n1) = Mid(rasLineArray(n1), nPosition + 1)
          Else
            rasLineArray(n1) = Left(rasLineArray(n1), nPosition - 1) & Mid(rasLineArray(n1), nPosition + 1)
          End If
          nPosition = nPosition + 1
        Loop
        
        vsSource = Mid(vsSource, InStr(1, vsSource, """,") + 2)
      
      Else
        nLength = InStr(1, vsSource, ",") - 1
        If nLength = 0 Then
          'nothing to do for this element as it is blank, but we want to continue as
          'there are more elements to be parsed from this string.
        ElseIf nLength > 0 Then
          rasLineArray(n1) = Left(vsSource, nLength)
        Else
          'we have reached the final element so there is no point continuing to loop.
          'the final value will be handled by the code following the for-next loop.
          Exit For
        End If
        vsSource = Mid(vsSource, nLength + 2)
      End If
    Next n1
    
    If vsSource <> vbNullString Then
    
        If Left(vsSource, 1) = """" Then
          If InStr(1, vsSource, """,") <> 0 Then
            vsSource = Mid(vsSource, 2)
            If Not vbFillLastColumn Then
              vsSource = Left(vsSource, InStr(1, vsSource, """,") - 1)
            End If
            vsSource = Replace(vsSource, """""", """")
          Else
            vsSource = Mid(vsSource, 2)
            vsSource = Left(vsSource, Len(vsSource) - 1)
          End If
        Else
          If InStr(1, vsSource, ",") <> 0 Then
            If Not vbFillLastColumn Then
               vsSource = Left(vsSource, InStr(1, vsSource, ",") - 1)
            End If
          End If
        End If
        
        rasLineArray(n1) = Trim(vsSource)
        
    End If

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Discuss "seprate coulmn from line." in the ASP Development forum.

ASP Development - Learn coding practices and tips to get the best out of your Active Server Pages (ASP). The Classic ASP forum is for ASP/VBScript and ASP/JScript applications.