Solved

How can I upload a text file formatted CVS into a VB6 grid

Posted on 2012-03-16
25
672 Views
Last Modified: 2012-06-27
We have an application where we display the contents of a text file received daily.  We would like to display the contents of the text file in a grid.

Is this possible and how can we go about it?
0
Comment
Question by:rayluvs
  • 15
  • 5
  • 5
25 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37732910
You can use an ADO data control and a Datagrid.

Set  the ADO data control connection string to something like:

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\MyFolder\;Extensions=asc,csv,tab,txt;

Set the data control RecordSource to:

Select * from MyFile.csv

Set the DataSource for the DataGrid to the datacontrol, e.g. ADODC1

Thr grid should now be filled when the form is run
0
 

Author Comment

by:rayluvs
ID: 37732989
Thanx!  Will try!
0
 

Author Comment

by:rayluvs
ID: 37733438
I can't seem to be getting it done and i am getting an error (see pic where included images of project, datagrid, adodc & error message.

Please advice
VB6tryinghTEXTwDATAGRID.jpg
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37734549
As I said, you need a Select statement, like this:PropertyPages, so you won't be able to browse for it.
0
 
LVL 15

Expert Comment

by:eemit
ID: 37734901
Another approach:
Using ADODB.Recordset and Schema.ini file and MSFlexGrid Control.
Private Sub cmdImportCsvToFlexGrid_Click()
  
  Dim sConnectionString As String
  Dim oCon As ADODB.Connection
  Dim oRs As ADODB.Recordset
  
  Dim sFilePath As String
  Dim sFileName As String

  sFilePath = App.Path & "\"
  sFileName = "Example.csv"

  'Create a Schema.ini File.
  'It must be in the same folder as your csv file.
  'In this file, you define the columns (fields) using syntax similar to this:

  '/Example.csv
  'LastName;FirstName
  'John;Doe
  'Terry;Wortham
  'Jack;McGinnis
  'Mary;Copeland
  '/

  '/Schema.ini
  '[YourFileName.csv]
  'ColNameHeader = True
  'CharacterSet = ANSI
  'Format=Delimited(;)
  'Col1="LastName" Text
  'Col2="FirstName" Text
  '/


  sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & ";" & _
                      "Extended Properties=""text;HDR=Yes;FMT=Delimited""" 'CSVDelimited


  Set oCon = New ADODB.Connection
  oCon.CursorLocation = adUseClient
  
  oCon.Open sConnectionString

  Set oRs = New ADODB.Recordset

  oRs.Open "Select * From " & sFileName, oCon, adOpenStatic, adLockOptimistic, adCmdText

  oRs.MoveFirst
  Debug.Print oRs.RecordCount

  Call Me.FillFlexGridADORs(Me.MSFlexGrid1, oRs)

End Sub


' Fill MSFlexGrid with ADO Recordset
Public Sub FillFlexGridADORs( _
                        oFlexGrid As MSFlexGrid, _
                        oRs As ADODB.Recordset _
                        )
  
  Dim nRow As Long
  Dim nCol As Long
  
  With oFlexGrid
     .Clear
     
     .Redraw = False
     
     .FixedRows = 1
     .FixedCols = 1
     
     ' Set Count Columns
     .Cols = oRs.Fields.Count + .FixedCols
     
     ' Grid Header
     For nCol = 0 To oRs.Fields.Count - 1
        .TextMatrix(0, nCol + 1) = oRs.Fields(nCol).Name
     Next
     
     ' Set Count Rows
     .Rows = oRs.RecordCount + .FixedRows
     
     ' Fill FlexGrid with Data
     Do While Not oRs.EOF

         ' Row Number is Record Number
         nRow = oRs.AbsolutePosition

         ' Record Number to Column 0
         .TextMatrix(nRow, 0) = nRow

         For nCol = 0 To oRs.Fields.Count - 1

            If Not IsNull(oRs.Fields(nCol).Value) Then
               .TextMatrix(nRow, nCol + 1) = oRs.Fields(nCol).Value
            End If
         Next
         oRs.MoveNext
     Loop
     
     ' Optimize Column Width
     Call AutoSizeColumnWidthFlexGrid(oFlexGrid, 90)

     .Redraw = True
  End With

End Sub


' Optimize Column Width in MSFlexGrid Control
Public Sub AutoSizeColumnWidthFlexGrid( _
                        ByRef oFlexGrid As MSFlexGrid, _
                        ByVal nExtendInTwips As Long _
                        )

  Dim nRow As Long
  Dim nCol As Long
  
  Dim nColumnWidth As Single

  With oFlexGrid
      For nCol = 0 To .Cols - 1
          nColumnWidth = 0
          For nRow = 0 To .Rows - 1
              If Me.TextWidth(.TextMatrix(nRow, nCol)) > nColumnWidth Then
                  nColumnWidth = Me.TextWidth(.TextMatrix(nRow, nCol))
              End If
          Next
          .ColWidth(nCol) = nColumnWidth + nExtendInTwips
      Next
  End With

End Sub

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37734912
Ok fixed it and no errors, but no data is displayed.

When I go to the properties of the Grid, on columns teb, there are 2 columns only.  The text file has 8 columns.

Where do I set the columns?

Also, why doesn't show at least the 2 columns data value?
0
 

Author Comment

by:rayluvs
ID: 37734915
Hi eemit, what would be th difference between Datagrid and MSFlexGrid?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37735159
Ramante,
which suggestion gives you only two columns? The datagrid & adodc suggestion gives the correct number of columns in my tests
0
 

Author Comment

by:rayluvs
ID: 37735173
Juts figured it out.  The columns are not displayed because the text file "c:\p.txt" was not correctly bind to the DataGrid.

What we saw was that when "Bulid" to search for "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=C:\MyFolder\;Extensions=asc,csv,tab,txt;", the option "Microsoft Text Driver" is not available (see)

Availeble Datalinks
0
 

Author Comment

by:rayluvs
ID: 37735178
The "c:\p.txt" file has 9 columns
0
 
LVL 15

Expert Comment

by:eemit
ID: 37735282
Hi Ramante,
With approach using ADODB.Recordset and Schema.ini file
you can also use a DataGrid control:
Set DataGrid1.DataSource = oRs
0
 

Author Comment

by:rayluvs
ID: 37735302
Understood, but since the textfile is changing in contents (always are 9 columns, but Column 1 contents will the be the same type as the next text read).

We would like to do it without schema.ini file, but if we have to, we will.

Can we proceed without the .INI file?
0
What Is Threat Intelligence?

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

 

Author Comment

by:rayluvs
ID: 37735309
Can you review what we are doing wrong?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37735351
It still isn't clear whether you are trying to use the 'bound' controls method that I suggested or the step-through-the-recordset method suggested later.
0
 
LVL 15

Expert Comment

by:eemit
ID: 37735488
Hi Ramante
If no Schema.ini file exists, then ADO uses the registry to determine how to parse a text file.

You don't need to specify Column Names in Schema.ini e.g.:

Schema.ini
[YourFileName.csv]
Format=Delimited(;)
ColNameHeader = True
MaxScanRows=10      'specifies that the text driver will scan the first 10 lines of the source file to determine the field data type definition
CharacterSet = ANSI

You must define the field types and sizes in a Schema.Ini file
only to read/write a fixed length record file.

- - -
With Solution posted by GrahamSkan
you can specify all Properties at runtime e.g.:

Private Sub cmdCsvToDataGridADODC_Click()
 
  Dim sFilePath As String
  Dim sFileName As String
 
  sFilePath = App.Path & "\"
  sFileName = "Example.csv"
 
  Adodc1.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sFilePath & ";Extensions=asc,csv,tab,txt;"
  Adodc1.RecordSource = "Select * from " & sFileName
 
  Set Me.DataGrid1.DataSource = Adodc1
End Sub
0
 

Author Comment

by:rayluvs
ID: 37735555
GrahamSkan: We are with the suggestion in ID: 37732910, because of what we said regarding the .INI file.
0
 

Author Comment

by:rayluvs
ID: 37758383
We're using the option without MSFlexGrid/scheme.ini file first in ID: 37732910 and gone thru the steps and no data is displayed.  No errors either, so syntax seems ok.

Please advice on what not working
0
 

Author Comment

by:rayluvs
ID: 37800689
How can I read in an ascii text file into a datagrid; preferably not using MSFlexGrid/scheme.ini file first in ID: 37732910)
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37802453
If the Ascii file is in CSV format, you might like to pursue the suggestion that I made originally.

If not, can you describe the format? I.e. how are the fields in each record defined and does the first record contain the column headings?
0
 

Author Comment

by:rayluvs
ID: 37814204
We have tried tried your suggestion in ID: 37732910 but in, but no data is displayed.

The text file is in CSV.  The first record of the file is no header (no header located in the file).

The CSV file we are working at this moment has has 6 columns:
    - Date   (date format mm/dd/yyyy)
    - Project   (string)
    - Theme   (string)
    - Tasks   (string)
    - Type   (string)
    - Dept   (string)

We would like to fill a datagrid with this data from CSV file.

Here is an example of the file contents:

2/1/2012,Remodeling,Central,Painting,Support,Financials
2/15/2012,Outsource Flanks,Accounts BLD,Parade,Development,Purchases
3/8/2012,Military,Window Migrate,Suppor,Meetings,Wrehouse
3/21/2012,Steaming Front,Central,Training,Support,Financials
2/2/2012,Cost Project,Central,Install,Development,Financials

Open in new window


Please advice
0
 
LVL 15

Accepted Solution

by:
eemit earned 500 total points
ID: 37814763
1)
Put your csv file in AppData Folder
(Create your own folder in AppData Folder and use it for storing your data)

2)
Put Schema.ini file in that folder

3)
Schema.ini
[YourCSVFileName.csv]
Format=Delimited(,)
ColNameHeader=False
MaxScanRows=2

4)
Set In IDE:

Adodc1.ConnectionString to:
Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=YourCSVFilePath\;Extensions=asc,csv,tab,txt;

Adodc1.RecordSource to:
Select * from YourCSVFileName.csv

DataGrid1.DataSource to:
DataGrid1.DataSource = Adodc1

4)
Set at Runtime:

sFilePath = "YourCSVFilePath" & "\"
sFileName = "YourCSVFileName.csv"
 
Adodc1.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sFilePath & ";Extensions=asc,csv,tab,txt;"
Adodc1.RecordSource = "Select * from " & sFileName
 
Set Me.DataGrid1.DataSource = Adodc1

- - -
Schema.ini File (Text File Driver)
http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx
0
 

Author Comment

by:rayluvs
ID: 37815056
Hi... we really didn't want to use your option, but we just tried and it worked excellently!
0
 

Author Comment

by:rayluvs
ID: 37815177
Question, we are starting to use vbAccelerator (from http://vbaccelerator.com) but it doesnt work.  It points that the problem reside in vbalGrid1 which in doesnt accedp "DataSource.

Somehow can't include the small test project nor the OCX & DVV of vbAccelerator.  So here s the link the reference & components for vbAccelerator

    http://www.vbaccelerator.com/home/VB/Code/Controls/S_Grid_2/S_Grid_2/article.asp
   
Please advice if possible.


Code VB6 was placed in an OK buttom:
Private Sub Command1_Click()
  Dim sFilePath As String
  Dim sFileName As String
  
  sFilePath = "C:\Temp\"
  sFileName = "zCSVfile.txt"
  
  Adodc1.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sFilePath & ";Extensions=asc,csv,tab,txt;"
  Adodc1.RecordSource = "Select * from " & sFileName
  
  'Set Me.DataGrid1.DataSource = Adodc1
  Set Me.vbalGrid1.DataSource = Adodc1
End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:eemit
ID: 37817070
Hi Ramante,
Glad it worked for you!
OK, I can try vbAccelerator Grid, but please open new question.

However you can try MSHFlexGrid (not MSFlexGrid) as it supports ADO binding e.g.:

4)
Set at Runtime:

sFilePath = "YourCSVFilePath" & "\"
sFileName = "YourCSVFileName.csv"
 
Adodc1.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sFilePath & ";Extensions=asc,csv,tab,txt;"
Adodc1.RecordSource = "Select * from " & sFileName
 
Set Me.MSHFlexGrid1.DataSource = Adodc1

5)
' Optional
Call FormatDateColumnHFlexGrid(Me.MSHFlexGrid1, "dd/mm/yyyy")
 
Call AutoSizeColumnWidthHFlexGrid(Me.MSHFlexGrid1, 150)

' Highlight the entire row when the grid is clicked
Call HighlightEntireRowHFlexGrid(Me.MSHFlexGrid1)

  
Private Sub FormatDateColumnHFlexGrid( _
                        ByRef oHFlexGrid As MSHFlexGrid, _
                        ByVal sDateFormat As String _
                        )
                        
  On Error GoTo Err_Handler
  
  Dim nRow As Long
  Dim nCol As Long
    
  With oHFlexGrid
        
      For nRow = 0 To .Rows - 1
          For nCol = 0 To .Cols - 1
              If IsDate(.TextMatrix(nRow, nCol)) Then
                  .TextMatrix(nRow, nCol) = Format(.TextMatrix(nRow, nCol), sDateFormat)
              End If
          Next nCol
      Next nRow
        
  End With
  
  Exit Sub
  
Err_Handler:
  Debug.Print "ERROR (FormatDateColumnHFlexGrid): " & Err.Description & ", " & CStr(Err.Number)

End Sub

Private Sub HighlightEntireRowHFlexGrid( _
                        ByRef oHFlexGrid As MSHFlexGrid _
                        )
                        
  On Error GoTo Err_Handler
  
  '/ Highlight the entire row when the grid is clicked
  With oHFlexGrid
      .FocusRect = flexFocusNone
      .SelectionMode = flexSelectionByRow
  End With
  
  With oHFlexGrid
      '.Col = 1
      .Col = .FixedCols
      
      .ColSel = .Cols - 1
  End With
  '/
  
  Exit Sub
  
Err_Handler:
  Debug.Print "ERROR (HighlightEntireRowHFlexGrid): " & Err.Description & ", " & Err.Number

End Sub


' Optimize Column Width in MSHFlexGrid Control
Public Sub AutoSizeColumnWidthHFlexGrid( _
                        ByRef oHFlexGrid As MSHFlexGrid, _
                        ByVal nExtendInTwips As Long _
                        )

  On Error GoTo Err_Handler
  
  Dim nRow As Long
  Dim nCol As Long
  
  Dim nColumnWidth As Single

  With oHFlexGrid
      
      For nCol = 0 To .Cols - 1
          nColumnWidth = 0
          For nRow = 0 To .Rows - 1
              If Me.TextWidth(.TextMatrix(nRow, nCol)) > nColumnWidth Then
                  nColumnWidth = Me.TextWidth(.TextMatrix(nRow, nCol))
              End If
          Next
          .ColWidth(nCol) = nColumnWidth + nExtendInTwips
      Next
  End With
  
  Exit Sub
  
Err_Handler:
  Debug.Print "ERROR (AutoSizeColumnWidthHFlexGrid): " & Err.Description & ", " & CStr(Err.Number)

End Sub

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37817164
Thanx
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…

747 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

10 Experts available now in Live!

Get 1:1 Help Now