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

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

Thanx!  Will try!

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

As I said, you need a Select statement, like this:, so you won't be able to browse for it.

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

Select allOpen in new window

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?

Hi eemit, what would be th difference between Datagrid and MSFlexGrid?

Ramante,
which suggestion gives you only two columns? The datagrid & adodc suggestion gives the correct number of columns in my tests

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)

The "c:\p.txt" file has 9 columns

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

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?

Can you review what we are doing wrong?

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.

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

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

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

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

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?

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

Select allOpen in new window

Please advice

Hi... we really didn't want to use your option, but we just tried and it worked excellently!

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

Select allOpen in new window

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

Select allOpen in new window

Thanx