Solved

How do I populate an MSFlexGrid with a CSV file?

Posted on 2009-05-05
18
473 Views
Last Modified: 2013-12-26
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

0
Comment
Question by:Taylor814
  • 9
  • 7
18 Comments
 
LVL 13

Expert Comment

by:game-master
ID: 24310550

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
0
 
LVL 13

Expert Comment

by:game-master
ID: 24310552

remove cntLINE = 0
0
 

Author Comment

by:Taylor814
ID: 24315485
What's the code to populate the msflexgrid?
0
 
LVL 13

Expert Comment

by:game-master
ID: 24321475

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
0
 

Author Comment

by:Taylor814
ID: 24331772
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
0
 
LVL 13

Expert Comment

by:game-master
ID: 24331942


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
0
 

Author Comment

by:Taylor814
ID: 24331988
Here's what I get now.
Clipboard01.jpg
0
 
LVL 13

Expert Comment

by:game-master
ID: 24332075


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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Taylor814
ID: 24332187
Here's what I get
Clipboard01.jpg
0
 
LVL 13

Expert Comment

by:game-master
ID: 24332314

Forgot the code...

MSFlexGrid.cols = ubound(sTokens)
0
 
LVL 13

Accepted Solution

by:
game-master earned 500 total points
ID: 24332321



Line Input #1, strRecord
         sTokens = Split(strRecord, ",")
         MSFlexGrid.cols = ubound(sTokens)
         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)
0
 

Author Closing Comment

by:Taylor814
ID: 31578069
Thank you very much for all of your help!!
0
 
LVL 13

Expert Comment

by:game-master
ID: 24332363


im glad i could help...:-)




game-master
0
 

Author Comment

by:Taylor814
ID: 24332367
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?
0
 
LVL 13

Expert Comment

by:game-master
ID: 24332440


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
0
 

Author Comment

by:Taylor814
ID: 24332498
Can't do that because some records are:

Bumbershoot Festival, 1234 Street
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now