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,"","",,"","",""
remove cntLINE = 0
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
ASKER
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
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
ASKER
Here's what I get now.
Clipboard01.jpg
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
ASKER
Here's what I get
Clipboard01.jpg
Clipboard01.jpg
Forgot the code...
MSFlexGrid.cols = ubound(sTokens)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for all of your help!!
im glad i could help...:-)
game-master
ASKER
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+
sTokens = Split(strRecord, ",")
game-master
ASKER
Can't do that because some records are:
Bumbershoot Festival, 1234 Street
Bumbershoot Festival, 1234 Street
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