VB: Convert any Access table to CSV without truncating numbers

We produce a lot of CSV files in my current job. A lot of the time they come out of Access databases. But when you export CSV from Access, it does funny things you don't want, like truncating numbers or representing them in exponential representation. I wrote this VBScript to solve that problem. It takes the name of an Access database, the name of a table, and an output file as its input and creates a CSV file of the contents of one whole table. The script could be tweaked to work with other file types by giving the correct connection string:

Note: A lot of folks have written who were familiar with Access but who could not make the VBScript run. Here is one way to make this script run:

1. Open Notepad
2. Paste this code into the notepad. Be sure that you are careful to fix lines that may have split onto two lines.
3. Save the text file in notepad to "DB_to_CSV.vbs" in "My Documents"
4. Open "My Documents" and double click the DB_to_CSV.vbs file.

This should open the script and prompt you for the database and the table you want to export.

Incidentally, VBScript is one of the most powerful features of Microsoft Windows. They hid an entire programming language right in the operating system. Granted, it is not Java or C++, but you can do a lot of very cool stuff with VBScript. You can even easily do a lot of things that would be very difficult to code in a more advanced programming language (this script is an example).

option explicit

const ForWriting = 2

'Prompt for these variables
dim file_name
file_name = "C:\data\test_data.mdb"
dim table_name
table_name = "sales"

' Prompt the user for a database name
file_name = inputbox("Access filename?", "Access to CSV", file_name)
if (file_name = "") then
    ' The user hit "Cancel"
    wscript.quit
end if

' Prompt the user for a table name
table_name = inputbox("Table name?", "Access to CSV", table_name)
if (table_name = "") then
    ' The user hit "Cancel"
    wscript.quit
end if

' Prompt the user for a table name, default to the Access database name
' with .CSV concatanated to the end.
dim output_file
output_file = file_name & ".csv"
output_file = inputbox("Output CSV file name?", "Access to CSV", output_file)
if (output_file = "") then
    ' The user hit "Cancel"
    wscript.quit
end if

doit file_name, table_name, output_file

Sub doit(file_name, table_name, output_file)
    Dim sql
    Dim cn
    Dim rs
    Dim oxl
    dim t
    t = timer

    file_name = trim(file_name)
    table_name = trim(table_name)

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' Here we set connection properties, open a connection, and create a recordset with the SQL
    ' Note that setting the properties takes the place of creating a connection string.
    With cn
        ' This can work with other databases.  Look at http://connectionstrings.com/
        ' You could extend this to accept other database types.

        if (right(file_name, 6) = ".accdb") then
            ' For Access 2007, use this:
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file_name & ";Persist Security Info=False;"
        elseif (right(file_name, 4) = ".mdb") then
            ' This is for Access 2003 files
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file_name & ";User Id=admin;Password=;"
        else
            wscript.echo "I don't recognize this file type: " & file_name
            wscript.quit
        end if
        .Open
    End With


    ' Here we specify the SQL we want to select data from
    sql = "SELECT * FROM [" & table_name & "]"
    rs.Open sql, cn

    ' Prepare the output CSV file
    output_file = trim(output_file)
    dim fso, file
    Set fso = CreateObject("Scripting.FileSystemObject")
    set file = fso.opentextfile(output_file, ForWriting, TRUE)

    wscript.echo "I'll output the CSV file to " & output_file

    ' Let's output the header row
    dim col
    dim line_to_write
    line_to_write = ""
    for col = 0 to rs.fields.count - 1
     line_to_write = line_to_write & ", " & rs(col).name
    next

    ' knock off the leading comma
    line_to_write = mid(line_to_write, 3)
    file.write line_to_write & vbcrlf

    ' Write out lines of data
    dim number_rows 
    number_rows = 0

    Do While Not rs.EOF
        line_to_write = ""
        For col = 0 To rs.Fields.Count - 1
     line_to_write = line_to_write & ", """ & rs(col).value & """"
        Next
        ' knock off the leading comma
        line_to_write = mid(line_to_write, 3)
        file.write line_to_write & vbcrlf
        number_rows = number_rows + 1
        rs.movenext
    Loop
    rs.Close

    wscript.echo "I'm done.  I wrote " & number_rows & " rows to " & output_file & " in " & cstr(timer - t) & " seconds"

    ' Close all of the ADODB objects
    If rs.State = 1 Then
        rs.Close
    End If
    If cn.State = 1 Then
        cn.Close
    End If
    Exit Sub
End Sub

One nice thing about this code is that it does not require you to know the names of the columns in the table you are exporting to CSV.