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.

SQL Server: How to read from a stored procedure like a table

I wrote a really spiffy stored procedure for my client. It returns a bunch of rows, just like a table. Today the client asked if I could run the stored procedure with a WHERE clause (a perfectly reasonable request). I didn't know how to do that. But now I do:

It was actually quite simple. As far as I can see, the only trick here was I needed to have the user name and password of a SQL Server user:

-- Parameters that take single quotes
-- need two single quotes
Select * from Openrowset(
'SQLOLEDB.1'
,'MYSERVER';'joe_user';'password1'
,'exec mydatabase.dbo.my_stored_procedure ''7/1/2001'')
where address like '%elm%'
order by 7


This takes advantage of the OPENROWSET function. OPENROWSET lets you do magical things, like one-time ad hoc queries from Access and Excel and CSV. The way I'm using it is probably a bastardization, but so is everything that works well in SQL Server. As we said in the Marines, if it looks stupid but it works, it's not stupid.

Three things that can go wrong:
1) Don't forget to prefix the stored procedure name with the database name and owner (in my example, this is "mydatabase.dbo.".
2) The user you select has to have execute rights on the stored procedure.
3) this worked great in SQL 2000. It also worked great in SQL 2005, however, when I first went to do it, it failed with the following (horrible) error:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server...

I had to go directly to the server, and run the "SQL Server Surface Area Configuration" utility (Start->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration). From there I added myself as an administrator, then clicked on "Surface Area Configuration for Features". Under "Database Engine" I clicked "Ad Hoc Remote Queries", then a checkbox appeared for "Enable OPENROWSET and OPENDATASET support". I checked this. After that, the error stopped happening and I was able to run my SQL statement.