Friday, August 17, 2007

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.

3 comments:

  1. Hi,

    I tried to use the code to convert an Access table into CSV file, I copied the code into new module in Access - but it didnt work for me. Would you be so kind and advice what should I do?

    I have a table that I need to send as a CSV file over to an ftp. I cant figure out how to use your code.

    Many thanks in advance, my email is marcinpec@yahoo.com.

    Thanks,
    Marcin

    ReplyDelete
  2. Thanks Tim, I was able to use this code. But I did not needed " in the csv and the header and modified it this way. After first function where the data inputs are made I added this. Got a result like this

    field1,field2,field3
    qwqwqw,qwqwqw,qwqwqq

    SSub 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
    ' 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
    MsgBox "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 Tablename "

    rs.Open sql, cn
    ' Prepare the output CSV file
    output_file = Trim(output_file)
    Dim fso, file
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set file = fso.OpenTextFile(output_file, ForWriting, True)

    MsgBox "I'll output the CSV file to " & output_file

    ' Let's output the header row
    Dim col
    Dim line_to_write As String
    '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
    'MsgBox " & number_rows & "
    Do While Not rs.EOF
    'Dim number_rows
    'number_rows = 0
    line_to_write = ""
    'line_to_write =
    For col = 0 To rs.Fields.Count - 1
    line_to_write = line_to_write & "" & rs(col).Value & """"
    'line_to_write = line_to_write & ",""" & rs(col).Value & """"
    'line_to_write = line_to_write & ", "" & rs(col).Value & """
    'line_to_write = line_to_write & ", " & rs(col).Value & "
    '" & rsProt!Protocol & "'
    Next
    ' knock off the leading comma

    line_to_write = Replace(line_to_write, Chr(34), Chr(44))

    file.Write line_to_write & vbCrLf
    number_rows = number_rows + 1
    rs.MoveNext
    Loop
    rs.Close
    MsgBox "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

    ReplyDelete
  3. Hi Timothy,

    I am currectly trying to convert my access database query straight to .csv format, however I am having difficulty. I think your script could be the answer to my problems however I am unsure where to input the script.
    I would really appreciate if you could explain where to input it. My email is teresan2009@gmail.com

    Many thanks for all your hard work and sharing it with everyone,
    Teresa

    ReplyDelete

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.