VB6 - Convert csv into MSHFlexgrid

Hi,

I have this below code that i use to upload a csv file into an MSHFlexgrid.

In the code, it has a ',' delimiter. 95% of the time, it's perfect.

Where i see a problem is when in my csv file, i have this:
1,2,3,"409, test",6,7,8

Normally, i should get in my Grid cells
Cells(0,0) = 1
Cells(0,1) = 2
Cells(0,2) = 3
Cells(0,3) = 409, test
Cells(0,4) = 6
Cells(0,5) = 7
Cells(0,5) = 8

** Since 409, test are in between "", this is 1 value. But because it has a comma in the value, the result is:

But what i get is:
Cells(0,0) = 1
Cells(0,1) = 2
Cells(0,2) = 3
Cells(0,3) = 409
Cells(0,4) = test
Cells(0,5) = 6
Cells(0,5) = 7
Cells(0,6) = 8

How can i fix this in my below code?

Thanks for your help.

(Pas d’objet)
Jean-Marc Poitras (LCL) Jean-Marc Poitras (LCL);

   MSHFlexGrid1.clear

    MSHFlexGrid1.Rows = 2

    MSHFlexGrid1.Cols = 1

 

    Dim lngRow As Long

    Dim lngCol As Long

    Dim strLine As String

    Dim strParts() As String

    Dim ff As Integer

    Dim strCSV() As String

    Dim lngCSV As Long

 

    With CommonDialog1

        .CancelError = True

        .filter = "Microsoft Excel files (csv)"

        .InitDir = "C:\Documents and Settings\all users\Desktop"

        .ShowOpen

        Text1.Text = (CommonDialog1.Filename)

    End With

    ff = FreeFile

    Open Text1.Text For Input As #ff

    On Error Resume Next

    With MSHFlexGrid1

        .Redraw = False     'Dont draw until the end, so we avoid that flash

        lngRow = 0

        Do While Not EOF(ff)

            Line Input #ff, strLine

            strCSV = Split(strLine, Chr(10))

            For lngCSV = 0 To UBound(strCSV)

                strParts = Split(strCSV(lngCSV), ",")

                For lngCol = 0 To UBound(strParts)

                    If .Cols < UBound(strParts) Then

                        .Cols = UBound(strParts) + 1

                    End If

                    If .Rows < lngRow + 1 Then

                        Do Until .Rows = lngRow + 1

                            .Rows = .Rows + 1

                        Loop

                    End If

                    MSHFlexGrid1.TextMatrix(lngRow, lngCol) = strParts(lngCol)

                    ' If you want to remove the quotes do this instead of the above line

                    MSHFlexGrid1.TextMatrix(lngRow, lngCol) = Replace(strParts(lngCol), """", "")

                Next

                lngRow = lngRow + 1

            Next

        Loop

        Close #ff

        .Col = 1            'Just to remove that blue selection from Flexgrid

        .Redraw = True      'Now draw

    End With

Open in new window

LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Try this

   MSHFlexGrid1.Clear

    MSHFlexGrid1.Rows = 2

    MSHFlexGrid1.Cols = 1



    Dim lngRow As Long

    Dim lngCol As Long

    Dim strLine As String

    Dim strParts() As String

    Dim ff As Integer

    Dim strCSV() As String

    Dim lngCSV As Long
    Dim intStart As Integer
    Dim intEnd As Integer
    Dim QUOTE As String
    Dim strTemp As String
    QUOTE = """"

 

    With CommonDialog1

        .CancelError = True

        .Filter = "Microsoft Excel files (csv)"

        .InitDir = "C:\Documents and Settings\all users\Desktop"

        .ShowOpen

        Text1.Text = (CommonDialog1.Filename)

    End With

    ff = FreeFile

    Open Text1.Text For Input As #ff

    On Error Resume Next

    With MSHFlexGrid1

        .Redraw = False     'Dont draw until the end, so we avoid that flash

        lngRow = 0

        Do While Not EOF(ff)

            Line Input #ff, strLine
            
            intStart = InStr(1, strLine, QUOTE)
            If intStart > 0 Then
                intEnd = InStr(intStart + 1, strLine, QUOTE)
                strTemp = Mid$(strLine, intStart, intEnd - intStart + 1)
                strTemp = Replace(strTemp, ",", " ")
                strLine = Left$(strLine, intStart - 1) & strTemp & Mid$(strLine, intEnd + 1)
            End If
End Sub

            strCSV = Split(strLine, Chr(10))

            For lngCSV = 0 To UBound(strCSV)

                strParts = Split(strCSV(lngCSV), ",")

                For lngCol = 0 To UBound(strParts)

                    If .Cols < UBound(strParts) Then

                        .Cols = UBound(strParts) + 1

                    End If

                    If .Rows < lngRow + 1 Then

                        Do Until .Rows = lngRow + 1

                            .Rows = .Rows + 1

                        Loop

                    End If

                    MSHFlexGrid1.TextMatrix(lngRow, lngCol) = strParts(lngCol)

                    ' If you want to remove the quotes do this instead of the above line

                    MSHFlexGrid1.TextMatrix(lngRow, lngCol) = Replace(strParts(lngCol), """", "")

                Next

                lngRow = lngRow + 1

            Next

        Loop

        Close #ff

        .Col = 1            'Just to remove that blue selection from Flexgrid

        .Redraw = True      'Now draw

Open in new window

0
Wilder1626Author Commented:
Hi Martin Liss

It fails at row 67.

I did the change to End With, but i still have a End with without With run time error.
0
Martin LissOlder than dirtCommented:
I'm sorry, since I didn't have your csv file to test with I hard-coded some stuff and left line 67 in by mistake. Please recopy the code as I posted it but without the Exit Sub at line 67.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
Here's the code without that line.

   MSHFlexGrid1.Clear

    MSHFlexGrid1.Rows = 2

    MSHFlexGrid1.Cols = 1



    Dim lngRow As Long

    Dim lngCol As Long

    Dim strLine As String

    Dim strParts() As String

    Dim ff As Integer

    Dim strCSV() As String

    Dim lngCSV As Long
    Dim intStart As Integer
    Dim intEnd As Integer
    Dim QUOTE As String
    Dim strTemp As String
    QUOTE = """"

 

    With CommonDialog1

        .CancelError = True

        .Filter = "Microsoft Excel files (csv)"

        .InitDir = "C:\Documents and Settings\all users\Desktop"

        .ShowOpen

        Text1.Text = (CommonDialog1.Filename)

    End With

    ff = FreeFile

    Open Text1.Text For Input As #ff

    On Error Resume Next

    With MSHFlexGrid1

        .Redraw = False     'Dont draw until the end, so we avoid that flash

        lngRow = 0

        Do While Not EOF(ff)

            Line Input #ff, strLine
            
            strLine = "This,is," & QUOTE & "test,this" & QUOTE & ", the rest"
            intStart = InStr(1, strLine, QUOTE)
            If intStart > 0 Then
                intEnd = InStr(intStart + 1, strLine, QUOTE)
                strTemp = Mid$(strLine, intStart, intEnd - intStart + 1)
                strTemp = Replace(strTemp, ",", " ")
                strLine = Left$(strLine, intStart - 1) & strTemp & Mid$(strLine, intEnd + 1)
            End If

            strCSV = Split(strLine, Chr(10))

            For lngCSV = 0 To UBound(strCSV)

                strParts = Split(strCSV(lngCSV), ",")

                For lngCol = 0 To UBound(strParts)

                    If .Cols < UBound(strParts) Then

                        .Cols = UBound(strParts) + 1

                    End If

                    If .Rows < lngRow + 1 Then

                        Do Until .Rows = lngRow + 1

                            .Rows = .Rows + 1

                        Loop

                    End If

                    MSHFlexGrid1.TextMatrix(lngRow, lngCol) = strParts(lngCol)

                    ' If you want to remove the quotes do this instead of the above line

                    MSHFlexGrid1.TextMatrix(lngRow, lngCol) = Replace(strParts(lngCol), """", "")

                Next

                lngRow = lngRow + 1

            Next

        Loop

        Close #ff

        .Col = 1            'Just to remove that blue selection from Flexgrid

        .Redraw = True      'Now draw

    End With

Open in new window

0
Wilder1626Author Commented:
hummm, when i try the code above, it just run for ever. It does not upload in the grid and the app freeze.

NO run time error.
0
Martin LissOlder than dirtCommented:
Please post the csv file and the project. (Always a good idea if you can).
0
Wilder1626Author Commented:
Ok, i will work on a sample and send it to you by tomorrow morning.

Thanks
0
Martin LissOlder than dirtCommented:
Could you post the csv file now?
0
Martin LissOlder than dirtCommented:
This worked for me so I don't know why nothing is happening for you. I made some minor changes but I made them after it ran OK.
Project1.zip
0
Wilder1626Author Commented:
I was testing with another csv file and if you look at the first row, once you upload the file in the grid, that at the end, it cause the same problem to upload in multiple columns due to a comma.

upload to grid test 2
Also joining a csv template so you can replicate the result i have.
Test2.csv
0
Martin LissOlder than dirtCommented:
Try the following code. It contains several changes including:
1. Added code correct the reported problem which was being caused by the fact that the previous code assumed that there was only one heading column with a comma between quotes, and the current file has two occurrences of a comma between quotes. The new code handles one or many.
2. Added code to detect when the user presses the Cancel button
3. Removed the quotes from the headings
4. Corrected the common dialog file selection so that now if the file doesn't end in csv, you can select the 'Files of type' dropdown and select 'All Files'
5. Removed a line of code near the bottom referring to .Col = 1 that wasn't needed.

Also

       MSHFlexGrid1.Clear

    Dim lngRow As Long
    Dim lngCol As Long
    Dim strLine As String
    Dim strParts() As String
    Dim ff As Integer
    Dim strCSV() As String
    Dim lngCSV As Long
    Dim intStart As Integer
    Dim intEnd As Integer
    Dim strTemp As String
    Const QUOTE = """"

    With CommonDialog1
        .CancelError = True
        '.Filter = "Microsoft Excel files (csv)"
        .Filter = "Microsoft Exce CSV Files (*.csv)|*.csv|All Files (*.*)|*.*"
        .FilterIndex = 1
        
        ' You may not want to make this change. It was just easier for me.
        '.InitDir = "C:\Documents and Settings\all users\Desktop"
        .InitDir = App.Path
        
        On Error Resume Next
        .CancelError = True
        .ShowOpen
        
        If Err.Number <> 0 Then
            MsgBox "No file was selected"
            Exit Sub
        End If
        On Error GoTo 0
        
        ' Do you really need this? If you don't then change the 'Open' line to
        ' Open CommonDialog1.FileName For Input As #ff
        Text1.Text = CommonDialog1.FileName
    End With

    ff = FreeFile

    Open Text1.Text For Input As #ff

    On Error Resume Next

    With MSHFlexGrid1
        .Redraw = False     'Dont draw until the end, so we avoid that flash
        lngRow = 0
        Do While Not EOF(ff)
            Line Input #ff, strLine
            intStart = 1
            Do Until intStart = 0
                intStart = InStr(intStart, strLine, QUOTE)
                If intStart > 0 Then
                    intEnd = InStr(intStart + 1, strLine, QUOTE)
                    strTemp = Mid$(strLine, intStart, intEnd - intStart + 1)
                    strTemp = Replace(strTemp, ",", " ")
                    strLine = Left$(strLine, intStart - 1) & strTemp & Mid$(strLine, intEnd + 1)
                    intStart = intEnd + 1
                End If
            Loop
            
            strCSV = Split(strLine, Chr(10))
            For lngCSV = 0 To UBound(strCSV)
                strParts = Split(strCSV(lngCSV), ",")
                For lngCol = 0 To UBound(strParts)
                    If .Cols < UBound(strParts) Then
                        .Cols = UBound(strParts) + 1
                    End If
                    If .Rows < lngRow + 1 Then
                        Do Until .Rows = lngRow + 1
                            .Rows = .Rows + 1
                        Loop
                    End If
                    'MSHFlexGrid1.TextMatrix(lngRow, lngCol) = strParts(lngCol)
                    ' If you want to remove the quotes do this instead of the above line
                    MSHFlexGrid1.TextMatrix(lngRow, lngCol) = Replace(strParts(lngCol), QUOTE, "")
                Next
                lngRow = lngRow + 1
            Next
        Loop

        Close #ff

        .Redraw = True      'Now draw
    End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
You also have some BOM characters at the start of the file
0
aikimarkCommented:
With your data, you might use the regular expression object to do your parsing.
Using this pattern:
(")?(\w.*?|)\1(?:,|\r\n|$)

Open in new window

Your data parses the following matches/submatches:
Match 0 Start(3) Length(8) 
SubMatch 0: 
SubMatch 1: PlaceID

Match 1 Start(11) Length(10) 
SubMatch 0: 
SubMatch 1: PlaceName

Match 2 Start(21) Length(12) 
SubMatch 0: 
SubMatch 1: PlaceTypeID

Match 3 Start(33) Length(21) 
SubMatch 0: 
SubMatch 1: PlaceTypeDescription

Match 4 Start(54) Length(15) 
SubMatch 0: 
SubMatch 1: PlaceReference

Match 5 Start(69) Length(19) 
SubMatch 0: 
SubMatch 1: PlaceUserReference

Match 6 Start(88) Length(9) 
SubMatch 0: 
SubMatch 1: Latitude

Match 7 Start(97) Length(10) 
SubMatch 0: 
SubMatch 1: Longitude

Match 8 Start(107) Length(12) 
SubMatch 0: 
SubMatch 1: CaptureType

Match 9 Start(119) Length(15) 
SubMatch 0: 
SubMatch 1: PlaceTolerance

Match 10 Start(134) Length(16) 
SubMatch 0: 
SubMatch 1: TrackingEnabled

Match 11 Start(150) Length(16) 
SubMatch 0: 
SubMatch 1: AnalysisEnabled

Match 12 Start(166) Length(14) 
SubMatch 0: 
SubMatch 1: VisitsEnabled

Match 13 Start(180) Length(16) 
SubMatch 0: 
SubMatch 1: AutoLockEnabled

Match 14 Start(196) Length(23) 
SubMatch 0: 
SubMatch 1: RequestCanbusOnArrival

Match 15 Start(219) Length(15) 
SubMatch 0: 
SubMatch 1: Alert1Distance

Match 16 Start(234) Length(15) 
SubMatch 0: 
SubMatch 1: Alert2Distance

Match 17 Start(249) Length(16) 
SubMatch 0: 
SubMatch 1: Report1Distance

Match 18 Start(265) Length(16) 
SubMatch 0: 
SubMatch 1: Report2Distance

Match 19 Start(281) Length(13) 
SubMatch 0: 
SubMatch 1: AddressLine1

Match 20 Start(294) Length(13) 
SubMatch 0: 
SubMatch 1: AddressLine2

Match 21 Start(307) Length(7) 
SubMatch 0: 
SubMatch 1: County

Match 22 Start(314) Length(9) 
SubMatch 0: 
SubMatch 1: PostCode

Match 23 Start(323) Length(12) 
SubMatch 0: 
SubMatch 1: PhoneNumber

Match 24 Start(335) Length(14) 
SubMatch 0: 
SubMatch 1: DetectionType

Match 25 Start(349) Length(13) 
SubMatch 0: 
SubMatch 1: IntegratedID

Match 26 Start(362) Length(12) 
SubMatch 0: 
SubMatch 1: AlertFilter

Match 27 Start(374) Length(9) 
SubMatch 0: 
SubMatch 1: Workflow

Match 28 Start(383) Length(21) 
SubMatch 0: 
SubMatch 1: MinimumVisitDuration

Match 29 Start(404) Length(7) 
SubMatch 0: 
SubMatch 1: Active

Match 30 Start(411) Length(16) 
SubMatch 0: 
SubMatch 1: LastUpdatedDate

Match 31 Start(427) Length(22) 
SubMatch 0: 
SubMatch 1: LastUpdateByUserName

Match 32 Start(449) Length(6) 
SubMatch 0: 
SubMatch 1: 11390

Match 33 Start(455) Length(29) 
SubMatch 0: 
SubMatch 1: FUEL STATION - METABETCHOUAN

Match 34 Start(484) Length(2) 
SubMatch 0: 
SubMatch 1: 1

Match 35 Start(486) Length(17) 
SubMatch 0: 
SubMatch 1: Restricted place

Match 36 Start(503) Length(9) 
SubMatch 0: 
SubMatch 1: ULTRAMAR

Match 37 Start(512) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 38 Start(513) Length(9) 
SubMatch 0: 
SubMatch 1: 48.43029

Match 39 Start(523) Length(9) 
SubMatch 0: 
SubMatch 1: 72.08022

Match 40 Start(532) Length(9) 
SubMatch 0: 
SubMatch 1: Circular

Match 41 Start(541) Length(4) 
SubMatch 0: 
SubMatch 1: 498

Match 42 Start(545) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 43 Start(551) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 44 Start(556) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 45 Start(561) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 46 Start(567) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 47 Start(572) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 48 Start(574) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 49 Start(576) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 50 Start(578) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 51 Start(580) Length(21) 
SubMatch 0: "
SubMatch 1: RUE INDUSTRIELLE, 

Match 52 Start(601) Length(32) 
SubMatch 0: "
SubMatch 1: METABETCHOUAN, LAC A LA CROIX

Match 53 Start(633) Length(7) 
SubMatch 0: 
SubMatch 1: QUEBEC

Match 54 Start(640) Length(8) 
SubMatch 0: 
SubMatch 1: G8G 1M7

Match 55 Start(648) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 56 Start(649) Length(6) 
SubMatch 0: 
SubMatch 1: Tight

Match 57 Start(655) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 58 Start(656) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 59 Start(657) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 60 Start(658) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 61 Start(659) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 62 Start(664) Length(20) 
SubMatch 0: 
SubMatch 1: 03/05/2013 01:50:17

Match 63 Start(684) Length(2) 
SubMatch 0: 
SubMatch 1: 

Match 64 Start(686) Length(6) 
SubMatch 0: 
SubMatch 1: 12000

Match 65 Start(692) Length(51) 
SubMatch 0: "
SubMatch 1: FUEL STATION - SHELL 10529 96 ST. HIGH LEVEL, AB

Match 66 Start(743) Length(2) 
SubMatch 0: 
SubMatch 1: 1

Match 67 Start(745) Length(17) 
SubMatch 0: 
SubMatch 1: Restricted place

Match 68 Start(762) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 69 Start(763) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 70 Start(764) Length(9) 
SubMatch 0: 
SubMatch 1: 58.50953

Match 71 Start(774) Length(10) 
SubMatch 0: 
SubMatch 1: 117.13481

Match 72 Start(784) Length(9) 
SubMatch 0: 
SubMatch 1: Circular

Match 73 Start(793) Length(4) 
SubMatch 0: 
SubMatch 1: 400

Match 74 Start(797) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 75 Start(803) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 76 Start(808) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 77 Start(813) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 78 Start(819) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 79 Start(824) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 80 Start(826) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 81 Start(828) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 82 Start(830) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 83 Start(832) Length(12) 
SubMatch 0: 
SubMatch 1: 10529 96 ST

Match 84 Start(844) Length(11) 
SubMatch 0: 
SubMatch 1: HIGH LEVEL

Match 85 Start(855) Length(3) 
SubMatch 0: 
SubMatch 1: AB

Match 86 Start(858) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 87 Start(859) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 88 Start(860) Length(6) 
SubMatch 0: 
SubMatch 1: Tight

Match 89 Start(866) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 90 Start(867) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 91 Start(868) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 92 Start(869) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 93 Start(870) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 94 Start(875) Length(20) 
SubMatch 0: 
SubMatch 1: 18/12/2013 10:37:20

Match 95 Start(895) Length(2) 
SubMatch 0: 
SubMatch 1: 

Match 96 Start(897) Length(6) 
SubMatch 0: 
SubMatch 1: 12033

Match 97 Start(903) Length(52) 
SubMatch 0: "
SubMatch 1: FUEL STATION - SHELL 1441 NORTHWEST BLVD CRESTON,

Match 98 Start(955) Length(2) 
SubMatch 0: 
SubMatch 1: 1

Match 99 Start(957) Length(17) 
SubMatch 0: 
SubMatch 1: Restricted place

Match 100 Start(974) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 101 Start(975) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 102 Start(976) Length(9) 
SubMatch 0: 
SubMatch 1: 49.11262

Match 103 Start(986) Length(10) 
SubMatch 0: 
SubMatch 1: 116.52418

Match 104 Start(996) Length(9) 
SubMatch 0: 
SubMatch 1: Circular

Match 105 Start(1005) Length(4) 
SubMatch 0: 
SubMatch 1: 498

Match 106 Start(1009) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 107 Start(1015) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 108 Start(1020) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 109 Start(1025) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 110 Start(1031) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 111 Start(1036) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 112 Start(1038) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 113 Start(1040) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 114 Start(1042) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 115 Start(1044) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 116 Start(1045) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 117 Start(1046) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 118 Start(1047) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 119 Start(1048) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 120 Start(1049) Length(6) 
SubMatch 0: 
SubMatch 1: Tight

Match 121 Start(1055) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 122 Start(1056) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 123 Start(1057) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 124 Start(1058) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 125 Start(1059) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 126 Start(1064) Length(20) 
SubMatch 0: 
SubMatch 1: 07/01/2014 11:36:23

Match 127 Start(1084) Length(2) 
SubMatch 0: 
SubMatch 1: 

Match 128 Start(1086) Length(6) 
SubMatch 0: 
SubMatch 1: 11994

Match 129 Start(1092) Length(53) 
SubMatch 0: "
SubMatch 1: FUEL STATION - SHELL 370 N SERVICE ROAD, MOOSE JAW

Match 130 Start(1145) Length(2) 
SubMatch 0: 
SubMatch 1: 1

Match 131 Start(1147) Length(17) 
SubMatch 0: 
SubMatch 1: Restricted place

Match 132 Start(1164) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 133 Start(1165) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 134 Start(1166) Length(9) 
SubMatch 0: 
SubMatch 1: 50.42685

Match 135 Start(1176) Length(10) 
SubMatch 0: 
SubMatch 1: 105.54396

Match 136 Start(1186) Length(9) 
SubMatch 0: 
SubMatch 1: Circular

Match 137 Start(1195) Length(4) 
SubMatch 0: 
SubMatch 1: 498

Match 138 Start(1199) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 139 Start(1205) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 140 Start(1210) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 141 Start(1215) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 142 Start(1221) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 143 Start(1226) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 144 Start(1228) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 145 Start(1230) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 146 Start(1232) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 147 Start(1234) Length(21) 
SubMatch 0: 
SubMatch 1: 370 NORTH SERVICE RD

Match 148 Start(1255) Length(10) 
SubMatch 0: 
SubMatch 1: MOOSE JAW

Match 149 Start(1265) Length(3) 
SubMatch 0: 
SubMatch 1: SK

Match 150 Start(1268) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 151 Start(1269) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 152 Start(1270) Length(6) 
SubMatch 0: 
SubMatch 1: Tight

Match 153 Start(1276) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 154 Start(1277) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 155 Start(1278) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 156 Start(1279) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 157 Start(1280) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 158 Start(1285) Length(20) 
SubMatch 0: 
SubMatch 1: 18/12/2013 10:23:13

Match 159 Start(1305) Length(2) 
SubMatch 0: 
SubMatch 1: 

Match 160 Start(1307) Length(6) 
SubMatch 0: 
SubMatch 1: 11990

Match 161 Start(1313) Length(51) 
SubMatch 0: 
SubMatch 1: FUEL STATION - SHELL 37418 RANGE ROAD 274 RED DEE

Match 162 Start(1364) Length(2) 
SubMatch 0: 
SubMatch 1: 1

Match 163 Start(1366) Length(17) 
SubMatch 0: 
SubMatch 1: Restricted place

Match 164 Start(1383) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 165 Start(1384) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 166 Start(1385) Length(9) 
SubMatch 0: 
SubMatch 1: 52.20626

Match 167 Start(1395) Length(10) 
SubMatch 0: 
SubMatch 1: 113.81520

Match 168 Start(1405) Length(9) 
SubMatch 0: 
SubMatch 1: Circular

Match 169 Start(1414) Length(4) 
SubMatch 0: 
SubMatch 1: 400

Match 170 Start(1418) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 171 Start(1424) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 172 Start(1429) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 173 Start(1434) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 174 Start(1440) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 175 Start(1445) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 176 Start(1447) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 177 Start(1449) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 178 Start(1451) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 179 Start(1453) Length(21) 
SubMatch 0: 
SubMatch 1: 37418 RANGE ROAD 274

Match 180 Start(1474) Length(9) 
SubMatch 0: 
SubMatch 1: RED DEER

Match 181 Start(1483) Length(3) 
SubMatch 0: 
SubMatch 1: AB

Match 182 Start(1486) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 183 Start(1487) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 184 Start(1488) Length(6) 
SubMatch 0: 
SubMatch 1: Tight

Match 185 Start(1494) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 186 Start(1495) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 187 Start(1496) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 188 Start(1497) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 189 Start(1498) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 190 Start(1503) Length(20) 
SubMatch 0: 
SubMatch 1: 17/12/2013 12:23:58

Match 191 Start(1523) Length(2) 
SubMatch 0: 
SubMatch 1: 

Match 192 Start(1525) Length(6) 
SubMatch 0: 
SubMatch 1: 12257

Match 193 Start(1531) Length(53) 
SubMatch 0: "
SubMatch 1: FUEL STATION - SHELL 3801 HWY ST HWY 3, VALLEYVIEW

Match 194 Start(1584) Length(2) 
SubMatch 0: 
SubMatch 1: 1

Match 195 Start(1586) Length(17) 
SubMatch 0: 
SubMatch 1: Restricted place

Match 196 Start(1603) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 197 Start(1604) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 198 Start(1605) Length(9) 
SubMatch 0: 
SubMatch 1: 55.05225

Match 199 Start(1615) Length(10) 
SubMatch 0: 
SubMatch 1: 117.28325

Match 200 Start(1625) Length(9) 
SubMatch 0: 
SubMatch 1: Circular

Match 201 Start(1634) Length(4) 
SubMatch 0: 
SubMatch 1: 551

Match 202 Start(1638) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 203 Start(1644) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 204 Start(1649) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 205 Start(1654) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 206 Start(1660) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 207 Start(1665) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 208 Start(1667) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 209 Start(1669) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 210 Start(1671) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 211 Start(1673) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 212 Start(1674) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 213 Start(1675) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 214 Start(1676) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 215 Start(1677) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 216 Start(1678) Length(6) 
SubMatch 0: 
SubMatch 1: Tight

Match 217 Start(1684) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 218 Start(1685) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 219 Start(1686) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 220 Start(1687) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 221 Start(1688) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 222 Start(1693) Length(20) 
SubMatch 0: 
SubMatch 1: 20/05/2014 10:46:08

Match 223 Start(1713) Length(2) 
SubMatch 0: 
SubMatch 1: 

Match 224 Start(1715) Length(6) 
SubMatch 0: 
SubMatch 1: 12405

Match 225 Start(1721) Length(43) 
SubMatch 0: "
SubMatch 1: FUEL STATION - SHELL 43ST LETHBRIDGE, AB

Match 226 Start(1764) Length(2) 
SubMatch 0: 
SubMatch 1: 1

Match 227 Start(1766) Length(17) 
SubMatch 0: 
SubMatch 1: Restricted place

Match 228 Start(1783) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 229 Start(1784) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 230 Start(1785) Length(9) 
SubMatch 0: 
SubMatch 1: 49.68328

Match 231 Start(1795) Length(10) 
SubMatch 0: 
SubMatch 1: 112.77816

Match 232 Start(1805) Length(9) 
SubMatch 0: 
SubMatch 1: Circular

Match 233 Start(1814) Length(4) 
SubMatch 0: 
SubMatch 1: 498

Match 234 Start(1818) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 235 Start(1824) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 236 Start(1829) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 237 Start(1834) Length(6) 
SubMatch 0: 
SubMatch 1: False

Match 238 Start(1840) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 239 Start(1845) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 240 Start(1847) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 241 Start(1849) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 242 Start(1851) Length(2) 
SubMatch 0: 
SubMatch 1: 0

Match 243 Start(1853) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 244 Start(1854) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 245 Start(1855) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 246 Start(1856) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 247 Start(1857) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 248 Start(1858) Length(6) 
SubMatch 0: 
SubMatch 1: Tight

Match 249 Start(1864) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 250 Start(1865) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 251 Start(1866) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 252 Start(1867) Length(1) 
SubMatch 0: 
SubMatch 1: 

Match 253 Start(1868) Length(5) 
SubMatch 0: 
SubMatch 1: True

Match 254 Start(1873) Length(20) 
SubMatch 0: 
SubMatch 1: 20/08/2014 09:53:30

Match 255 Start(1893) Length(2) 
SubMatch 0: 
SubMatch 1: 

Match 256 Start(1895) Length(0) 
SubMatch 0: 
SubMatch 1: 

Open in new window

There is one extra match at the end, which you would ignore, since all lines have 32 fields.
0
aikimarkCommented:
You would place the matches into the grid's cells as you iterate the matches.  Ignore submatches(0) for each match.  The data you want is in submatches(1).
0
Wilder1626Author Commented:
Hi aikimark, Not sure i understand that process. I will look at it.
0
Wilder1626Author Commented:
MartinLiss, just tried the code in your post: ID: 40787545.

So far, It's working. I will run a huge csv file, see if all is good. I have a csv file with more than 45000 rows.

Will let you know shortly.
0
Martin LissOlder than dirtCommented:
Hi aikimark, Not sure i understand that process. I will look at it.
I believe aikimark's code is for .Net rather than VB6.
0
aikimarkCommented:
I believe aikimark's code is for .Net
No.  It is for a VB/VBA/VBScript run-time environment.

It might look something like this:
Sub Q_28676609(ByVal parmString As String)
    Dim oRE As Object
    Dim oMatches As Object
    Dim lngRow As Long
    Dim lngCol As Long
    
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "("")?(\w.*?|)\1(?:,|\r\n|$)"
    
    lngRow = 1
    lngCol = 1
    If oRE.test(parmString) Then
        Set oMatches = oRE.Execute(parmString)
        For Each oM In oMatches
            MSHFlexGrid1.TextMatrix(lngRow, lngCol) = oM.submatches(1)
            lngCol = lngCol + 1
            If lngCol > 32 Then
                lngRow = lngRow + 1
                lngCol = 1
            End If
        Next
    End If

End Sub

Open in new window


Notes:
* This code will populate the headers
* Alternatively, you might split the file on vbLF and parse each line
* Alternatively, you might read the file line-by-line with FSO and parse each line (you may choose to skip the first line).
* You will probably want to tweak the starting row in this code.
0
Wilder1626Author Commented:
Hi
I will go for MartinLiss solution. This is doing exactly what i needed.

Thanks for all your help.

aikimark, I'm still trying to make your code work, but it's a little complicated to understand.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
aikimarkCommented:
@Wilder1626

If you want to pursue the regular expression solution open a new question and post a link to it.
0
Wilder1626Author Commented:
that is a good idea. I'm still curious about your solution. let me do this.
0
aikimarkCommented:
don't forget the new question link
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.