Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a csv from ADO RS (VB6)

    I tried to write some quick and dirty code to simply take a recordset and save in a csv format so that someone else can pull it up in a speadsheet. The code is as follows:

    Dim iFileNumber As Integer

    iFileNumber = FreeFile ' Get unused file number.
    Open "c:InactiveHNAM_Users.txt" For Output As #iFileNumber ' Create file name.

    Write #iFileNumber, grsOU.Fields(0).Name & vbTab & grsOU.Fields(1).Name & vbTab & grsOU.Fields(2).Name ' Output text.
    Debug.Print grsOU.Fields(0).Name & ", " & grsOU.Fields(1).Name & ", " & grsOU.Fields(2).Name

    grsOU.MoveFirst

    Do While Not grsOU.EOF

    If UCase(grsOU(2)) = UCase("Inactive") Then

    Write #iFileNumber, grsOU(0) & vbTab & grsOU(1) & vbTab & grsOU(2) ' Output text.
    Debug.Print grsOU(0).Value & ", " & grsOU(1).Value & ", " & grsOU(2).Value & vbCrLf

    End If

    grsOU.MoveNext
    Loop

    Close #iFileNumber ' Close file.

    What is happening is that, when you look at it in wordpad, each line has a double quote starting and ending the string. When I try to open it in Excel it puts everything in one column, quotes and all. I started out using commas and then switched to tabs as delimiters. Both did the same thing.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating a csv from ADO RS (VB6)

    Have you tried using Put #iFileNumber instead of Write?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a csv from ADO RS (VB6)

    I modified the code to use Put. It runs but returns a 0 byte file. I used a debug.print on the same code passed using the PUT statement so I know that it should be outputting data.

    Dim iFileNumber As Integer

    iFileNumber = FreeFile ' Get unused file number.
    Open "c:InactiveHNAM_Users.txt" For Output As #iFileNumber ' Create file name.

    Put #iFileNumber, , grsOU.Fields(0).Name & vbTab & grsOU.Fields(1).Name & vbTab & grsOU.Fields(2).Name

    Debug.Print grsOU.Fields(0).Name & ", " & grsOU.Fields(1).Name & ", " & grsOU.Fields(2).Name

    grsOU.MoveFirst

    Do While Not grsOU.EOF

    If UCase(grsOU(2)) = UCase("Inactive") Then

    Put #iFileNumber, , grsOU(0) & vbTab & grsOU(1) & vbTab & grsOU(2) ' Output text.
    Debug.Print grsOU(0).Value & ", " & grsOU(1).Value & ", " & grsOU(2).Value & vbCrLf

    End If

    grsOU.MoveNext
    Loop

    Close #iFileNumber ' Close file.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Creating a csv from ADO RS (VB6)

    Are you suppressing VB error messages? When I run similar code, I am told by the de###### that I cannot Put to a file opened for Output (or Append, for that matter). The file needs to be opened for Random before I can Put to it. The contents, though, are rather a mess when I do that.

    Why not create the output file using ADO and the ODBC Text Driver? Together they can read a CSV file, so I assume they can write such a file. Perhaps a naive assumption. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Take a look at this article and test drive the code. Perhaps it will help. (And let us know if it is as cool as it looks.)

    <IMG SRC=http://www.fawcette.com/archives/premier/mgznarch/vbpj/2001/11nov01/gs0111/fig1.gif>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •