VB 6.0 - How to retrieve CSV file records using VB 6 0?

Asked By Ralph Acido on 07-Jul-06 04:02 AM
Hello! I need your help how to retrieve data from a CSV file that contains as much as 1000 rows. Using a manual connection...

Thanks!

re - Asked By Rupali Randhave Kolhe on 07-Jul-06 04:42 AM

hi,
Download e.g
http://www.vb-helper.com/howto_csv_to_ragged_array.html
in this e.g csv file data stored in array
u can store it as a string .
data splited here using comma.

Use Oledb provider - Asked By Shallu Gupta on 07-Jul-06 05:04 AM

Hi, yu can use oleDB provider to read a csv file in as recordset

Dim CSVConn As ADODB.Connection
Dim csvRS As ADODB.Recordset
Dim filePath As String
Dim baseName As String
Dim importCount As Long

Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
filePath = fso.GetParentFolderName(FileName)
baseName = fso.GetFileName(FileName)

Set CSVConn = New ADODB.Connection

CSVConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & filePath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"""

CSVConn.Open

Set csvRS = CSVConn.Execute("Select * From [" & baseName & "]")


Do Until csvRS.EOF

' do processing

Loop

Great Thanks Mr /Ms Gupta - Asked By Ralph Acido on 07-Jul-06 05:15 AM

Thanks a lot it works.. :)
Using ODBC - Asked By Rupali Randhave Kolhe on 07-Jul-06 05:23 AM
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DataFile As FileInfo = New FileInfo("C:\Temp\badger.csv")
        Dim cnCSV As OdbcConnection
        Dim daCSV As OdbcDataAdapter
        Dim dt As DataTable = New DataTable("badger")

        cnCSV = New OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & DataFile.Directory.FullName & ";")
        daCSV = New OdbcDataAdapter("SELECT * FROM [" & DataFile.Name & "]", cnCSV)
        daCSV.Fill(dt)
        DataGrid1.DataSource = dt
    End Sub
Charles Chukwu replied to Shallu Gupta on 01-Jul-10 04:50 AM
Somebody pasted this in another forum that uses VBScript. After adding the reference to Microsoft Script Runtime, and adapting it to my scenario this is what i got: [CODE] Dim CSVConn As ADODB.Connection Dim csvRS As ADODB.Recordset Dim FilePath As String Dim baseName As String Dim importCount As Long Dim fso As Scripting.FileSystemObject Set fso = New Scripting.FileSystemObject FilePath = fso.GetParentFolderName(m_FileName) baseName = fso.GetFileName(m_FileName) Set CSVConn = New ADODB.Connection CSVConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";Extended Properties=text;HDR=Yes;FMT=Delimited" CSVConn.Open ' csvRS.CursorLocation = adUseClient ' csvRS.CursorType = adOpenKeyset ' csvRS.LockType = adLockBatchOptimistic Set csvRS = CSVConn.Execute("Select * From [" & baseName & "]") 'Set Me.dtgImported.DataSource = csvRS With csvRS ' On Error Resume Next ' .CLOSE ' If err.Number <> 0 Then ' err.Clear ' End If rsoriginal.CursorLocation = adUseClient rsoriginal.CursorType = adOpenKeyset rsoriginal.LockType = adLockBatchOptimistic 'sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile '.Open "SELECT * FROM [pr_loandetails]", cn '.MoveFirst Do While Not .EOF If .Fields(0).Value <> "" Then rsoriginal.AddNew For i = 0 To .Fields.Count - 1 If .Fields(i).Value <> "" Then rsoriginal.Fields(i).Value = .Fields(i).Value End If Next i End If .MoveNext Me.lblTotalImported.Caption = "Total record imported: " & rsoriginal.recordcount Loop .MoveFirst End With Set Me.dtgImported.DataSource = rsoriginal [\CODE] However, I came up with these errors: First one was that The rowset is not bookmarkable. 7004, which made me use the second recordset- rsoriginal to load contents of the first one. Then i got this error later: Operation is not allowed when the object is closed. 3704. Which i've not resolved. Can you help me out please? Charles.
Uploading CSV file into ADO recordset into SQL Server. - Charles Chukwu replied to Charles Chukwu on 01-Jul-10 05:32 AM

Hi,
Sorry for the disorderly post i earlier sent. Somebody posted this on this forum that uses VBScript. After adding the reference to Microsoft Script Runtime, and adapting it to my scenario this is what i got:

Dim CSVConn As ADODB.Connection
    Dim csvRS As ADODB.Recordset
    Dim FilePath As String
    Dim baseName As String
    Dim importCount As Long
    
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    FilePath = fso.GetParentFolderName(m_FileName)
    baseName = fso.GetFileName(m_FileName)
    
    Set CSVConn = New ADODB.Connection
    
    CSVConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";Extended Properties=text;HDR=Yes;FMT=Delimited"
    
    CSVConn.Open
    
'        csvRS.CursorLocation = adUseClient
'        csvRS.CursorType = adOpenKeyset
'        csvRS.LockType = adLockBatchOptimistic

    
    Set csvRS = CSVConn.Execute("Select * From [" & baseName & "]")
    
    'Set Me.dtgImported.DataSource = csvRS
    
    With csvRS
'        On Error Resume Next
'        .CLOSE
'        If err.Number <> 0 Then
'            err.Clear
'        End If
        rsoriginal.CursorLocation = adUseClient
        rsoriginal.CursorType = adOpenKeyset
        rsoriginal.LockType = adLockBatchOptimistic

        'sconn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & sFile
        '.Open "SELECT * FROM [pr_loandetails]", cn
        '.MoveFirst

        Do While Not .EOF
            If .Fields(0).Value <> "" Then
                rsoriginal.AddNew
                For i = 0 To .Fields.Count - 1
                    If .Fields(i).Value <> "" Then
                        rsoriginal.Fields(i).Value = .Fields(i).Value
                    End If
                Next i
            End If
            .MoveNext
            Me.lblTotalImported.Caption = "Total record imported: " & rsoriginal.recordcount
        Loop
        .MoveFirst
    End With
    
    Set Me.dtgImported.DataSource = rsoriginal


However, I came up with these errors: First one was that
The rowset is not bookmarkable. 7004, which made me use the second recordset- rsoriginal to load contents of the first one- csvRS

Then i got this error later:
Operation is not allowed when the object is closed. 3704. Which i've not resolved. I suspect the connection string. The File is a CSV text file, containing filed names as the first contents separated by commas, then the values in the fields come in subsequent lines all separated by commas
Can you help me out please?


Charles.