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

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?
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
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
rayluvsAuthor Commented:
Thanx!  Will try!
rayluvsAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

GrahamSkanRetiredCommented:
As I said, you need a Select statement, like this:PropertyPages, so you won't be able to browse for it.
eemitCommented:
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

rayluvsAuthor Commented:
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?
rayluvsAuthor Commented:
Hi eemit, what would be th difference between Datagrid and MSFlexGrid?
GrahamSkanRetiredCommented:
Ramante,
which suggestion gives you only two columns? The datagrid & adodc suggestion gives the correct number of columns in my tests
rayluvsAuthor Commented:
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
rayluvsAuthor Commented:
The "c:\p.txt" file has 9 columns
eemitCommented:
Hi Ramante,
With approach using ADODB.Recordset and Schema.ini file
you can also use a DataGrid control:
Set DataGrid1.DataSource = oRs
rayluvsAuthor Commented:
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?
rayluvsAuthor Commented:
Can you review what we are doing wrong?
GrahamSkanRetiredCommented:
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.
eemitCommented:
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
rayluvsAuthor Commented:
GrahamSkan: We are with the suggestion in ID: 37732910, because of what we said regarding the .INI file.
rayluvsAuthor Commented:
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
rayluvsAuthor Commented:
How can I read in an ascii text file into a datagrid; preferably not using MSFlexGrid/scheme.ini file first in ID: 37732910)
GrahamSkanRetiredCommented:
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?
rayluvsAuthor Commented:
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
eemitCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Hi... we really didn't want to use your option, but we just tried and it worked excellently!
rayluvsAuthor Commented:
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

eemitCommented:
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

rayluvsAuthor Commented:
Thanx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.