DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: how to upload a CSV file in VB6

  1. #1
    Join Date
    Dec 2008
    Location
    *****, Nigeria
    Posts
    62

    how to upload a CSV file in VB6

    Hi,

    I need to upload a CSV file via my application into recordsets and then to SQLserver DB. Can somebody give a hand, please?

    Thanks for your usual quick response.

    Charles.
    Mobile: +234 706 042 6036
    Email: charles@hedonmark.com
    Website: www.hedonmark.com

    “To be yourself in a world that is constantly trying to make you something else is the greatest accomplishment.”
    —Ralph Waldo Emerson

  2. #2
    Join Date
    Jun 2009
    Posts
    135
    In help file, on index tab, look up the following...

    FreeFile Function
    Open Statement
    Line Input Statement
    Split Function
    LBound Function
    UBound Function
    Close Statement

    Then it is time to use your friends (yahoo, google, ask, answers, bing) to search for vb6 ado tutorial. Also, you may want to look up SQL Insert statement and see http://www.connectionstrings.com



    Good Luck

  3. #3
    Join Date
    Dec 2008
    Location
    *****, Nigeria
    Posts
    62
    Hi Vb5prgrmr,

    I saw the list of functions you gave in your reply but i would like to know which help file you are talking about. If its MSDN for vb6.0, i do not have it.
    People always refer to www.connectionstrings.com but i can't navigate to the exact string i need.
    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
    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.
    Mobile: +234 706 042 6036
    Email: charles@hedonmark.com
    Website: www.hedonmark.com

    “To be yourself in a world that is constantly trying to make you something else is the greatest accomplishment.”
    —Ralph Waldo Emerson

  4. #4
    Join Date
    Jun 2009
    Posts
    135
    yes, I mean MSDN and since you don't have it, you can use your friends to search the web for examples or you can use the MSDN Library at M$ which I believe still has all those functions listed...

    As for what you posted, from the looks of it, it should do what you want as I can see no obvious errors but then again, I can tell there is a lot if information missing from that snippet.... Although, after a second look I see the .MoveFirst... Should that not be .MoveNext? And then I do not see a .Update... Could be wrong but I thought a .Addnew needed a .Update to commit the changes (source http://www.vb6.us/tutorials/database...o-vb6-tutorial )...




    Good Luck

  5. #5
    Join Date
    Dec 2008
    Location
    *****, Nigeria
    Posts
    62
    Thanks for you suggestion vb5prgrmr,
    I found a solution and adapted it to my scenario to obtain the following:
    Code:
    Dim a$, o$, ColumnNames$(), RowFieldValues$()
                  Open m_FileName For Input As #1
                  Line Input #1, a$ 'read the column names (first line in file)
                  ColumnNames = Split(a$, ",") 'ubound(ColumnNames)+1 is now number of data fields per line
                  If rsoriginal.Fields.Count <> UBound(ColumnNames) + 1 Then
                    MsgBox "The Target File and the Destination Table are NOT Compatible!", vbCritical, LoadResString(101)
                    Exit Function
                  End If
                  While Not EOF(1)
                    Line Input #1, a$
                    RowFieldValues = Split(a$, ",")
                    
                    For i = 0 To rsoriginal.Fields.Count - 1 'or UBound(RowFieldValues)
                      rsoriginal.AddNew
                      rsoriginal.Fields(i).Value = RowFieldValues(i)
                      rsoriginal.Update
                    Next
                  Wend
                  Close #1
                  Me.dtgImported.ReBind
              Else
                MsgBox "Please Select a Destination For the Upload", vbInformation, LoadResString(101)
              End If
        Else
          MsgBox "Please Browse and select a Target File For the Upload", vbInformation, LoadResString(101)
        End If
        Exit Function
    Mobile: +234 706 042 6036
    Email: charles@hedonmark.com
    Website: www.hedonmark.com

    “To be yourself in a world that is constantly trying to make you something else is the greatest accomplishment.”
    —Ralph Waldo Emerson

  6. #6
    Join Date
    Dec 2008
    Location
    *****, Nigeria
    Posts
    62
    Hi guys,
    I bumped into this error after changing my connection string to the one i put in my post-quoted below. Could not find installable ISAM. -2147467259.
    Code:
    sXL = Trim$(Me.txtCSVtextFile.Text)
        Set cn = New ADODB.Connection
        With cn '"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";Extended Properties=text;HDR=Yes;FMT=Delimited"
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sXL & ";Extended Properties=text;HDR=Yes;FMT=Delimited"
            .ConnectionTimeout = 40
            .Open
        End With
    in my VB6 application anyone with an answer?
    Mobile: +234 706 042 6036
    Email: charles@hedonmark.com
    Website: www.hedonmark.com

    “To be yourself in a world that is constantly trying to make you something else is the greatest accomplishment.”
    —Ralph Waldo Emerson

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Have a look at this.

    Or, try this.

  8. #8
    Join Date
    Dec 2008
    Location
    *****, Nigeria
    Posts
    62
    Hi Hack,
    Thanks for your contribution. I've moved beyond the ISAM issue. The recordset gets loaded with contents from the CSV file(placed a couple of breakpoints in the code). The problem is displaying it in the datagrid. It doesn't return an error but the datagrid doesnt display anything:
    here's the code that runs:
    Code:
        Dim CSVConn As ADODB.Connection
        Dim csvRS As ADODB.Recordset
        Dim FilePath As String
        Dim baseName As String
        Dim importCount As Long
        Const adOpenStatic = 3
        Const adLockOptimistic = 3
        Const adCmdText = &H1
        
        Dim fso As Scripting.FileSystemObject
        Set fso = New Scripting.FileSystemObject
        FilePath = fso.GetParentFolderName(m_FileName)
        baseName = fso.GetFileName(m_FileName)
        Set CSVConn = CreateObject("ADODB.Connection")
        Set csvRS = CreateObject("ADODB.Recordset")
        
        CSVConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & FilePath & ";" & _
              "Extended Properties=""text;HDR=YES;FMT=Delimited"""
              
        csvRS.Open "SELECT * FROM " & baseName, _
              CSVConn, adOpenStatic, adLockOptimistic, adCmdText
        Dim testVar
        Do Until csvRS.EOF
             testVar = csvRS.Fields.Item(0)
                 
             csvRS.MoveNext
        Loop
    
        Set Me.dtgImported.DataSource = csvRS
        
    
        Exit Function
    fix_err:
    
        CSVConn.CLOSE
        Set csvRS = Nothing
    Could you help out? Guys I'm counting on you all.
    Thanks.
    Last edited by proffy; 07-03-2010 at 07:18 AM.
    Mobile: +234 706 042 6036
    Email: charles@hedonmark.com
    Website: www.hedonmark.com

    “To be yourself in a world that is constantly trying to make you something else is the greatest accomplishment.”
    —Ralph Waldo Emerson

  9. #9
    Join Date
    Dec 2008
    Location
    *****, Nigeria
    Posts
    62
    Hi Guys,
    I've found the solution...i guess it was because of the recordset modes, the adOpenKeyset and adLockBatchOptimistic
    Here's the solution:
    Code:
    Dim objConnection As New ADODB.Connection
        Dim rsoriginal As New ADODB.Recordset
        Dim FilePath As String
        Dim baseName As String
        Dim importCount As Long
        Dim fso As Scripting.FileSystemObject
        
        On Error GoTo fix_err:
        
        Set fso = New Scripting.FileSystemObject
        FilePath = fso.GetParentFolderName(Me.txtExcelFile.Text)
        baseName = fso.GetFileName(Me.txtExcelFile.Text)
            
        objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & FilePath & ";" & _
              "Extended Properties=""text;HDR=YES;FMT=Delimited"""
           
    
        With rsoriginal
            On Error Resume Next
            .CLOSE
            If err.Number <> 0 Then
                err.Clear
            End If
            .CursorLocation = adUseClient
            .CursorType = adOpenKeyset
            .LockType = adLockBatchOptimistic
    
        .Open "SELECT * FROM " & baseName, objConnection
    
         End With
    
        Set Me.dtgImported.DataSource = rsoriginal
    Thanks for the help! We did it again!
    Mobile: +234 706 042 6036
    Email: charles@hedonmark.com
    Website: www.hedonmark.com

    “To be yourself in a world that is constantly trying to make you something else is the greatest accomplishment.”
    —Ralph Waldo Emerson

Similar Threads

  1. Replies: 3
    Last Post: 04-22-2008, 09:42 AM
  2. Need help with Bulk Insert/ CSV file
    By Andrew Cushen in forum Database
    Replies: 9
    Last Post: 09-27-2006, 11:19 PM
  3. updating xml file from VB6
    By smithg22 in forum VB Classic
    Replies: 9
    Last Post: 04-12-2005, 06:23 PM
  4. How long before the next version??
    By _CAG in forum .NET
    Replies: 146
    Last Post: 08-12-2002, 10:40 PM
  5. NullPointerException when reading text file
    By Andrew McLellan in forum Java
    Replies: 3
    Last Post: 05-09-2001, 05:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center