Link to home
Start Free TrialLog in
Avatar of Taylor814
Taylor814

asked on

How do I populate an MSFlexGrid with a CSV file?

Hi - Can someone please tell me how to populate an MSFlexGrid with a CSV file? The CSV file looks like:
Date,Time,Venue,Address,City,State,Postal Code,Country,Details,Age Limit,Private,Ticket Details,Ticket Link,Artists
05/07/2009,9:00 PM,"Paddy ONeills @ Historic Alex Johnson Hotel","523 Sixth Street","Rapid City",SD,"57701",US,"","21+",,"Free","",""
05/08/2009,9:00 PM,"Paddy ONeills @ Historic Alex Johnson Hotel","523 Sixth Street","Rapid City",SD,"57701",US,"","21+",,"Free","",""
05/09/2009,9:00 PM,"Paddy ONeills @ Historic Alex Johnson Hotel","523 Sixth Street","Rapid City",SD,"57701",US,"","21+",,"Free","",""
05/16/2009,4:30 PM,"U District Street Fair","47th Street Stage","Seattle",WA,"98105",US,"","All Ages",,"Free","",""
06/06/2009,9:30 PM,"Lucia Italian Restaurant & Bar","222 Park Place Center","Kirkland",WA,"98033",US,"","All Ages",,"Free","",""
06/06/2009,11:00 AM,"Tastin and Racin Festival","Lake Sammamish State Park, 2000 NW Sammamish Road","Issaquah",WA,"98027",US,"","",,"","",""

Open in new window

Avatar of game-master
game-master
Flag of Philippines image


Dim strRecord As String
Dim sTokens() As String
Dim x as Integer
Dim sTemp as String

cntLINE = 0
Open "C:\Test.CSV" For Input As #1
Do While Not EOF(1)
   Line Input #1, strRecord
         sTokens= Split(strRecord,",")
         For x = 0 To UBound(sTokens)
                sTemp = sTokens(nI)
               additional code here to do whatever is required with the substring
              ' put the substring to the flexgrid.
         Next x
Loop
Close #1


i hope i could give u some idea...


game-master

remove cntLINE = 0
Avatar of Taylor814
Taylor814

ASKER

What's the code to populate the msflexgrid?

GOOD morning!

the code to populate the msFlexgrid is...

msFlexgrid.additem sToken(0) & vbtab &  stoken(1) & vbtab  & stoken(2)

thats how to populate the flexgrid....



game-master
The below does not work as you can see from my image
Private Sub mnuLoadCSV_Click()
Dim strRecord As String
Dim sTokens() As String
Dim x As Integer
Dim sTemp As String
Dim strRow As String
 
 
 
On Error Resume Next
Dim FileName As String
With CommonDialog1
    .FileName = ""
    .InitDir = App.Path & "\lists"
    .Filter = "CSV Files (*.csv)|*.csv"
    .ShowOpen
    FileName = .FileName
End With
If Len(FileName) Then
 
Open FileName For Input As #1
Do While Not EOF(1)
   Line Input #1, strRecord
         sTokens = Split(strRecord, ",")
         For x = 0 To UBound(sTokens)
                sTemp = sTokens(nI)
                MSFlexGrid.AddItem sTokens(0) & vbTab & sTokens(1) & vbTab & sTokens(2) _
                                    & vbTab & sTokens(4) & vbTab & sTokens(5) & vbTab & sTokens(6) _
                                    & vbTab & sTokens(7) & vbTab & sTokens(8) & vbTab & sTokens(9) _
                                    & vbTab & sTokens(10) & vbTab & sTokens(11) & vbTab & sTokens(12) _
                                    & vbTab & sTokens(13)
                                    
         Next x
Loop
Close #1
End If
End Sub

Open in new window

Clipboard01.jpg


before the for next loop... add this code...


MSFlexGrid.cols = ubound(sTokens)
For x = 0 To UBound(sTokens)
                sTemp = sTokens(nI)
                MSFlexGrid.AddItem sTokens(0) & vbTab & sTokens(1) & vbTab & sTokens(2) _
                                    & vbTab & sTokens(4) & vbTab & sTokens(5) & vbTab & sTokens(6) _
                                    & vbTab & sTokens(7) & vbTab & sTokens(8) & vbTab & sTokens(9) _
                                    & vbTab & sTokens(10) & vbTab & sTokens(11) & vbTab & sTokens(12) _
                                    & vbTab & sTokens(13)
                                   
         Next x
Here's what I get now.
Clipboard01.jpg


oh im sorry...my mistake...remove the for next loop...

try this code...


Private Sub mnuLoadCSV_Click()
Dim strRecord As String
Dim sTokens() As String
Dim x As Integer
Dim sTemp As String
Dim strRow As String
 
 
 
On Error Resume Next
Dim FileName As String
With CommonDialog1
    .FileName = ""
    .InitDir = App.Path & "\lists"
    .Filter = "CSV Files (*.csv)|*.csv"
    .ShowOpen
    FileName = .FileName
End With
If Len(FileName) Then
 
Open FileName For Input As #1
Do While Not EOF(1)
   Line Input #1, strRecord
         sTokens = Split(strRecord, ",")
         MSFlexGrid.AddItem sTokens(0) & vbTab & sTokens(1) & vbTab & sTokens(2) _
                                    & vbTab & sTokens(4) & vbTab & sTokens(5) & vbTab & sTokens(6) _
                                    & vbTab & sTokens(7) & vbTab & sTokens(8) & vbTab & sTokens(9) _
                                    & vbTab & sTokens(10) & vbTab & sTokens(11) & vbTab & sTokens(12) _
                                    & vbTab & sTokens(13)
                                   
Loop
Close #1
End If
End Sub
Here's what I get
Clipboard01.jpg

Forgot the code...

MSFlexGrid.cols = ubound(sTokens)
ASKER CERTIFIED SOLUTION
Avatar of game-master
game-master
Flag of Philippines image

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
Thank you very much for all of your help!!


im glad i could help...:-)




game-master
I do see an issue. The CSV has two delimiters: comma and quote+comma. How do I get it to split with both in mind?


use the "replace" function  to replace the quote+comma with comma only before you split the line record...


sumthing like this...

strRecord = replace(strRecord,"<quote+comma>",",")
sTokens = Split(strRecord, ",")


game-master
Can't do that because some records are:

Bumbershoot Festival, 1234 Street