Solved

How do I populate an MSFlexGrid with a CSV file?

Posted on 2009-05-05
18
472 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

22 Experts available now in Live!

Get 1:1 Help Now