The script was written to be invoked from the command line using cscript.exe and then with the following parameters; (1) ODBC DSN name; (2) table Name; and (3) output CSV name. You could quite easily update strDNS, strSQLTable, strFile though.
Option Explicit
Dim objConn, objRecordset
Dim objFileSystem, objFile
Dim intCount, intNoFields, strSQL
Dim strDSN, strSQLTable, strFile
Const ForReading = 1, ForWriting = 2
' get command line arguments
strDSN = WScript.Arguments.Item(0)
strSQLTable = WScript.Arguments.Item(1)
strFile = WScript.Arguments.Item(2)
' open odbc dsn connection
Set objConn = WScript.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=" & strDSN & ";Uid=myUsername;Pwd=;"
objConn.Open
' execute query
strSQL = "SELECT * FROM " & Trim(strSQLTable)
Set objRecordset = objConn.Execute(strSQL)
' open file for writing
Set objFileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set objFile = objFileSystem.OpenTextFile(strFile, ForWriting, True)
' loop through recordset
intNoFields = (objRecordset.Fields.Count-1)
objRecordset.MoveFirst
Do While Not objRecordset.EOF
' get number of fields (columns) within recordset and
' for each field output to file seperated by a tab,
' but skip nulls and don't add a tab if at last field.
For intCount = 0 to intNoFields
If IsNull(objRecordset.Fields.Item(intCount)) = False Then
objFile.Write objRecordset.Fields.Item(intCount)
If intCount <> intNoFields Then objFile.Write vbTab
Else
If intCount <> intNoFields Then objFile.Write vbTab
end if
Next
' write new line at end of each record and move onto next
' (nicely does it now)
objFile.Write vbNewLine
objRecordset.moveNext
Loop
' finish up
objFile.Close
Set objFile = Nothing
Set objFileSystem = Nothing
objRecordset.Close
Set objRecordset = Nothing
objConn.Close
Set objConn = Nothing
As I wanted to have the script scheduled and the output zipped and placed onto a UNC path, I created the following Windows shell script. The script is actually a bit of an overkill but as I had already written it for something else and liked the logging and verbose output features used it again.
I've modified this script slightly for sake of simplification and posting on the web; I actually extracted a number of tables and captured these in additional phases (phase 2, phase 3, etc.). The current phase 2 (ZIP compression) was numbered after the last extract phase (e.g. phase 4). If you’re going to do the same, don’t forget to update the variables section to add multiple "set odbc_tableX=", etc. Good luck!
rem -----------------------------------------------
rem determine current date and time
rem -----------------------------------------------
for /f "tokens=1-3 delims=-/. " %%a in ("%date%") do set XDate=%%c%%b%%a
for /f "tokens=1-3 delims=:. " %%a in ("%time%") do set XTime=%%a%%b%%c
rem Possible bug: Above only works if regional
rem settings are set to region that has either
rem has "-", "/", "." as the date seperator
rem and either ":" or "." as a time seperator
rem --which should cover all default regional
rem settings. Please update 'delims' above if
rem you have issues. To help test, uncomment
rem the following lines.
rem echo %XDate%%XTime%
rem goto end
rem -----------------------------------------------
rem user variables
rem (do not include trailing slash on path names!)
rem -----------------------------------------------
setlocal
set description=ODBC2TXT script
rem 7-zip location, destination dir and destination output
set zipprog_dir=C:\Program Files\7-Zip
set dest_dir=\\server\share
set dest_zip=extract_%Xdate%-%XTime%.zip
rem wsh script location
set script_dir=c:\odbc2txt
set wshscript=odbc2txt.vbs
rem odbc dns and SQL tables
set odbc_dsn=databasename
set odbc_table1=tablename
rem working dir and log file
set working_dir=%TEMP%\ODBC2TXT
set log_file=%working dir%\odbc2txt.log
rem -----------------------------------------------
rem clean up working directory
rem -----------------------------------------------
rem create working folder if not already exists) and ensure
rem it is empty.
if not exist "%working_dir%" md "%working_dir%"
if exist "%working_dir%\*.*" del /f /q "%working_dir%\*.*"
rem -----------------------------------------------
rem script header information
rem -----------------------------------------------
set message=%description%
echo %message%> "%log_file"
cls
echo *===============================================
echo %message%
echo *===============================================
echo.
set message=*** Script started. Processing...
echo %message%>>"%log_file"
echo %message%
rem -----------------------------------------------
rem phase 1: extract table
rem -----------------------------------------------
:phase1
set message=*** Phase 1: Extracting table %odbc_table1%...
echo %message%>>"%log_file"
echo.
echo %message%
rem start WSH script with parameters
cscript.exe /Nologo "%script_dir%\%wshscript%" %odbc_dsn% %odbc_table1% "%working_dir%\%odbc_table1%.txt" >>"%log_file"
if not exist "%working_dir%\%odbc_table1%.txt" goto phase1_error
set message=DONE!
echo %message%>>"%log_file"
echo %message%
goto phase2
:phase1_error
set message=ERROR! File was not created. Script failed!
echo %message%>>"%log_file"
echo %message%
goto end
rem -----------------------------------------------
rem phase 2: Compress and copy to outbound folder
rem -----------------------------------------------
:phase2
set message=*** Phase 2: Compressing extracts and copying to outbound folder...
echo %message%>>"%log_file"
echo.
echo %message%
"%zipprog_dir%\7za.exe" a -y -w"%working_dir%" "%working_dir%\archive.zip" "%working_dir%\*.txt" >>"%log_file"
if not exist "%working_dir%\archive.zip" goto phase3_error
rem copy zip file to given destination directory.
copy /y "%working_dir%\archive.zip" "%dest_dir%\%dest_zip%" >>"%log_file"
if errorlevel 1 goto phase3_error
set message=%dest_zip% created and copied to
echo %message%>>"%log_file"
echo %message%
set message=%dest_dir%
echo %message%>>"%log_file"
echo %message%
set message=DONE!
echo %message%>>"%log_file"
echo %message%
goto end
:phase2_error
set message=ERROR! Failed. Check log.
echo %message%>>"%log_file"
echo %message%
goto end
rem -----------------------------------------------
rem Finish off
rem -----------------------------------------------
:end
set message=*** Script complete. Exiting...
echo %message%>>"%log_file"
echo.
echo %message%
echo.
endlocal
pause