// ============================================================================
//
// NAME
// DumpODBC.js
//
//
// DESCRIPTION
// Extracts data from ODBC sources (and Excel spreadsheets ending in '.XLS',
// or Access files ending in '.MDB').
//
// You can also LIST the tables available.
//
//
// OUTPUT FORMATS
// o CSV - comma seperated. Values which contain a comma or a double
// quote will be enclosed in double quotes. All double quotes
// in the value will be 'escaped' with another double quote.
//
// ie ab -> ab
// a,b -> "a,b"
// a"b -> "a""b"
// a'b -> a'b
//
// This is compatible with Microsoft Excel.
// This is the default format.
//
// o TAB - each field is delimited by a tab character.
//
// o PIPE - each field is delimited by a pipe '|' character.
//
// o HTML - Start with Seperated by End with
// Column headings
// Data rows
//
// Additional markup
//
//
// USAGE
// From the command line only :-
//
// cscript [ //NOLOGO ] DumpODBC.js
//
// The '//NOLOGO' instructs cscript to suppress the Microsoft banner.
//
// can be the ODBC DSN set up in ODBC Administrator tool, or it
// can be the filename of an excel spreadsheet. You may need to enclose
// the name in double quotes if it contains spaces.
//
// can be LIST [ ]
// or DUMP [ ] [ tables... ]
//
// The default is CSV.
// If you do not specify a list of tables to dump, it will dump all tables.
// If you have a table whose name conflicts with one of the format options,
// then specify the format too.
//
// All dumps go to standard output, so you will probably need to redirect
// it to a file.
//
//
// DATA TYPES
// Supported
// o Boolean. Outputs 'true' or 'false'
// o Date. ISO 8601 YYYY-MM-DDTHH:MI:SS
// o Numeric.
// o Currency.
// o Character.
//
// Unsupported (ADO constants)
// The following types should generate a warning, but may still print.
//
// o adEmpty (0)
// o adBSTR (8)
// o adGUID (72)
// o adBinary (128)
// o adVarBinary (204)
// o adLongVarBinary (205)
// o adVarNumeric (139)
// o adVariant (12)
// o adError (10)
// o adUserDefined (132)
// o adIDispatch (9)
// o adIUnknown (13)
// o adChapter (136)
// o adFileTime (64)
// o adPropVariant (138)
// o adArray (0x2000)
//
//
// EXAMPLES
// List the 'tables' in the spread sheet 'book2.xls'
// cscript DumpODBC.js book2.xls LIST
//
// Dump the 'Sheet1' and 'Sheet2' worksheets in 'book2.xls' as CSV
// cscript DumpODBC.js book2.xls DUMP Sheet1$ Sheet2$
//
// Dump all tables in the SPONGE ODBC source as HTML
// cscript DumpODBC.js SPONGE DUMP HTML >sponge.html
//
// Dump the 'emp' table in the 'scott' schema, from the
// 'test_oracle' ODBC source as CSV to the file 'scott_emp.csv'
// cscript //NOLOGO DumpODBC.js TEST_ORACLE DUMP scott.emp >scott_emp.csv
//
//
// NOTES
// Microsoft Excel
// o mumble mumble SYSTEM TABLES mumble WORKSHEETS
// o mumble TABLES mumble NAMED RANGES mumble
// o mumble UNNAMED RANGES mumble mumble
// o mumble mumble WHERE COLUMN HEADINGS COME FROM mumble mumble
// o mumble HOW EXCEL DERIVES THE DATA TYPE mumble mumble
// o mumble mumble EXCEL 97 WARNING mumble
// o mumble PASSWORD PROTECTED WORKBOOKS mumble mumble
//
// SQL Server
// o mumble TEXT AS LONG ERROR mumble mumble
//
// If you do a lot of ODBC stuff, try WinSQL - http://www.synametrics.com/
//
//
// REQUIREMENTS
// The script will check these requirements are met and warn you.
// You can download recent versions of both items from Microsofts website.
//
// o Windows Scripting Host (WSH), v5.6 or later.
// o Microsoft Data Access Components (MDAC) 2.6 or later.
//
//
// TESTING
// This product was developed and tested with :-
// o Windows 98
// o Windows 2000
// o WSH 5.6
// o MDAC 2.8
// o Microsoft Excel
// o Oracle 9i
// o 'nwind.mdb' (sample access database);
//
// No animals were harmed during testing.
//
//
// SOURCES OF INFORMATION (all on microsoft's website)
// o Windows Scripting Host documentation
//
// o Microsoft Data Access Web page at http://www.microsoft.com/data/
// o Q257819 HOWTO: Use ADO and ADOX with Excel Data from Visual Basic or VBA
// o Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
// o Q278973 SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks
// o Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
// o Q214854 INFO: Improved ODBC DataType Mappings with Jet 4.0
// o Q159557 XL97: Using System, User, and File Data Sources
// o Q211378 XL2000: "Could Not Decrypt File" Error with Password Protected File
// o Q178717 INF: Excel ODBC Driver and Text ODBC Driver Notes
// o Q288343 BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header Setting
// o Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver
// o Q141809 BUG: 2nd Insert Statement Fails On a Recreated Table in Excel
// o Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
//
// o OLE DB/ADO: Making Universal Data Access a Reality, May 1998
// o Understanding ODBC and OLE, 1997
// o ADO Provider Properties and Settings, January 1999
// o Appendix B: Microsoft Jet 4.0 OLE DB Properties Reference
//
//
// COPYRIGHT
// This script is public domain. It is my gift to you. If you like this
// script, pay it forward! Do 3 strangers a favour. Visit a forum and
// answer some questions - when was the last time you helped a new user?
// Let's be public spirited, and make this imperfect world a better place.
//
//
// ============================================================================
//
//
// TO DO
// It is easy to dream, so let's dream big!
//
// o Specify a username/password (for Oracle, SQL Server etc...)
// o Display the structure of a table.
// o List/Dump tables by pattern.
// o List/Dump tables based on schema
// o Specify an SQL Query - lets use that SQL engine!
// o Send dump to a named file instead of standard out.
// o Send each seperate table/query to a seperate file
// o Specify a filename for each table/query
// o Generated filename automatically based on a format string.
//
// o Batch conversion mode - specify multiple sources.
// o Interactive mode.
// o Scripted mode! Reads instructions from a script.
// o 'Monitor' a directory mode! (For excel).
//
// o Option to set the DATE FORMAT.
// o Option to set the NUMERIC FORMAT
// o Option to set the CURRENCY FORMAT
// o New output format - SYLK
// o Option to specify value seperator
// o Option to add row number to HTML/XML output
// o Option to load format from a file.
// o Option to write a 'create table' script.
// o Option to write an Oracle SQL*Loader control file.
// o New format option - SQL*Loader control file!
//
// o Repackage it as a WSF.
// o Re-write in VBScript - strings are faster in current engine.
//
//
// HISTORY
// 1.2 30-May-2004 released Tak Tang
// Wrote documentation header; added remaining numeric types.
//
// Added PIPE format and check on WSH and MDAC versions.
//
// Now detects EXCEL; ACCESS and ORACLE if used through ODBC, and 'quotes'
// table names appropriately.
//
// Arranged functions so that output and error file handles are passed, in
// preparation for dumping to files.
//
// 1.1 28-May-2004 stable Tak Tang
// Added primitive command line interface; support for Excel files;
// TAB and HTML formats. Can now dump all tables.
//
// 1.0 25-May-2004 stable Tak Tang
// Combined existing scripts to list tables and dump as csv.
//
// ============================================================================
var gsDefaultDateFormat='ISO 8601';
// Month abbreviations
// See DateToString()
var gasMonthAbrv=[ 'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN',
'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC' ];
// Some constants for handling file sources
var gsJetProvider='Microsoft.Jet.OLEDB.4.0';
var gsJetExcelExtendedProp='Excel 8.0;HDR=Yes';
// ----------------------------------------------------------------------------
// Constants and variables
// Ado Constants
var adSchemaTables = 20;
var adModeRead=1;
// Regular expressions
var greQuote = /"/g
var grePercS = /%s/ig
var grePercD = /%d/ig
var greQuoteComma=/[",]/
var greAmpLtGt=/[&<>]/
var greAmp=/&/g
var greLt=//g
// Strings to control output format
// See CheckFormat()
var gsOutputFormat='';
var gsTABLE = '';
var gsTHR = '';
var gsTH = '';
var gsTHR_ = '';
var gsTDR = '';
var gsTD = '';
var gsTDR_ = '';
var gsTABLE_ = '';
// Are we processing an Excel workbook?
var gsFileType='ODBC';
// ----------------------------------------------------------------------------
main();
// ----------------------------------------------------------------------------
function main()
{
var argc, argv;
var dbcon, dbrs;
var lsDSN, lsExtension, lnLength;
Check_WSH_ver(5,6);
Check_MDAC_ver(2,'6');
argv = WScript.Arguments;
argc = argv.length;
if (argc < 1)
{
WScript.echo('Usage: cscript dumpodbc.js \n'
+ ' can be any ODBC DSN, or an excel file.\n'
+ ' ::= LIST [ ]\n'
+ ' | DUMP [ ] [ tables ]\n'
+ ' LIST will product a list of all tables\n'
+ ' DUMP will dump all tables to screen, or just those specified\n'
+ ' ::= CSV | TAB | PIPE | HTML'
);
}
else
{
// Create an ADODB.Connection Object
dbcon = new ActiveXObject('ADODB.Connection');
// If the parameter ends in '.XLS' we will assume it is an Excel
// file instead of an ODBC connection.
lsExtension='';
lsDSN = argv(0);
lnLength=lsDSN.length;
if (lnLength>4)
{
lsExtension=lsDSN.substr(lnLength-4,4).toUpperCase();
if ('.XLS'==lsExtension)
{
dbcon.Provider = gsJetProvider;
dbcon.Mode=adModeRead;
dbcon.Properties('Extended Properties').Value = gsJetExcelExtendedProp;
gsFileType = 'EXCEL';
}
else if ('.MDB'==lsExtension)
{
dbcon.Provider = gsJetProvider;
dbcon.Mode=adModeRead;
gsFileType = 'ACCESS';
}
}
// Open the DSN
dbcon.Open(lsDSN);
try
{
// Do something with it
if (argc<2) // no additional commands? Just testing the ODBC/file
{
WScript.StdErr.WriteLine( (Check(dbcon)?'OK ':'ERROR opening ') + lsDSN);
}
else
{
lsCmd = argv(1).toUpperCase();
if ('LIST'==lsCmd)
{
void CheckFormat(argc, argv);
CmdList(dbcon, WScript.StdOut);
}
else if ('DUMP'==lsCmd)
{
switch (dbcon.Properties('DBMS NAME').value)
{
case 'ACCESS' : gsFileType='ACCESS'; break;
case 'ORACLE' : gsFileType='ORACLE'; break;
case 'EXCEL': gsFileType='EXCEL'; break;
default:
} // switch
CmdDump(dbcon, argc, argv, WScript.StdOut, WScript.StdErr );
}
else
{
WScript.StdErr.WriteLine('Invalid command ' + lsCmd);
}
}
}
catch (e)
{
WScript.StdErr.WriteLine(e.description);
}
// Close and dispose of the db connection
dbcon.Close();
dbcon=null;
}
argv=null;
} // function main()
// ----------------------------------------------------------------------------
// NAME
// Check_WSH_ver()
//
// DESCRIPTION
//
// ----------------------------------------------------------------------------
function Check_WSH_ver( in_major, in_minor )
{
var lnMajor
var lnMinor
try
{
lnMajor = ScriptEngineMajorVersion();
lnMinor = ScriptEngineMinorVersion();
if (lnMajor i)
{
lsArg=p_argv(i).toUpperCase();
if (lsArg=='CSV')
{
i++;
}
else if (lsArg=='TAB')
{
gsOutputFormat=lsArg;
gsTH='\t';
gsTD='\t';
gsTABLE='%s';
gsTHR='';
gsTHR_='';
gsTDR='';
gsTDR_='';
gsTABLE_='';
i++;
}
else if (lsArg=='PIPE')
{
gsOutputFormat=lsArg;
gsTH='|';
gsTD='|';
gsTABLE='%s';
gsTHR='';
gsTHR_='';
gsTDR='';
gsTDR_='';
gsTABLE_='';
i++;
}
else if (lsArg=='HTML')
{
gsOutputFormat=lsArg;
gsTABLE='';
gsTHR='';
gsTH=' ';
gsTHR_=' ';
gsTDR='';
gsTD=' ';
gsTDR_=' ';
gsTABLE_='
';
i++;
}
}
return i;
} // function CheckFormat()
// ---------------------------------------------------------------------------
// NAME
// CmdDump()
//
// DESCRIPTION
// Process a command to dump all or specified tables.
// ---------------------------------------------------------------------------
function CmdDump( p_con, p_argc, p_argv, p_fh_output, p_fh_error )
{
var ln_i;
var ls_table;
ln_i = CheckFormat( p_argc, p_argv );
if (p_argc == ln_i)
{
DumpAllTables( p_con, p_fh_output, p_fh_error );
}
else
{
for ( ; p_argc>ln_i ; ln_i++ )
{
try
{
ls_table = p_argv(ln_i);
DumpTable( p_con, ls_table, p_fh_output, p_fh_error );
}
catch (e)
{
p_fh_error.WriteLine(e.description);
} // catch
} // for each named table
} // if tables have been named
} // function CmdDump()
// ---------------------------------------------------------------------------
// NAME
// DumpAllTables()
//
// DESCRIPTION
// Generate a list of tables, and call DumpTable() for each one.
// ---------------------------------------------------------------------------
function DumpAllTables( p_con, p_fh_output, p_fh_error )
{
var l_rs
var l_table;
// Fetch a list of tables from that connection
l_rs = p_con.OpenSchema(adSchemaTables);
// List tables
for ( ; !l_rs.EOF; l_rs.MoveNext )
{
// Build table name (ie add the schema prefix)
l_table = l_rs('TABLE_SCHEMA').value;
if (null==l_table)
{
l_table = l_rs('TABLE_NAME').value;
}
else
{
l_table += '.' + l_rs('TABLE_NAME').value;
}
try
{
DumpTable( p_con, l_table, p_fh_output );
}
catch (e)
{
p_fh_error.WriteLine(e.description);
}
} // for each table
// Close and dispose of the record set
l_rs.Close();
l_rs=null;
} // function DumpAllTables()
// ---------------------------------------------------------------------------
// NAME
// DumpTable()
//
// DESCRIPTION
// Dumps a table to the screen
// ---------------------------------------------------------------------------
function DumpTable( p_con, p_tablename, p_fh_output, p_fh_error )
{
var l_rs
p_fh_error.WriteLine('Extracting table ' + p_tablename);
switch (gsFileType)
{
case 'EXCEL':
// if the tablename is not already in square brackets
if (p_tablename.substr(0,1) != '[')
{
p_tablename = '[' + p_tablename + ']';
}
break;
case 'ACCESS':
if ( p_tablename.indexOf(' ') != -1)
{
p_tablename = '[' + p_tablename + ']';
}
break;
case 'ORACLE':
if ( p_tablename.indexOf(' ') != -1)
{
p_tablename = '"' + p_tablename + '"';
}
break;
default:
} // switch
try
{
// Execute SELECT command
l_rs = p_con.Execute('SELECT * FROM ' + p_tablename);
try
{
// Write header
p_fh_output.WriteLine(gsTABLE.replace(grePercS, p_tablename));
WriteHeader( l_rs, p_fh_output, p_fh_error );
WriteBody( l_rs, p_fh_output, p_fh_error );
if (gsTABLE_) p_fh_output.WriteLine(gsTABLE_);
p_fh_output.WriteLine('');
}
catch (e)
{
// Clean up
l_rs.close();
l_rs=null;
p_fh_error.WriteLine('Error processing result set.');
throw(e);
}
// Clean up
l_rs.close();
l_rs=null;
}
catch (e)
{
throw(e);
}
} // function DumpTable()
// ---------------------------------------------------------------------------
// NAME
// WriteHeader()
//
// DESCRIPTION
// Examines the column headings, and writes these out.
// ---------------------------------------------------------------------------
function WriteHeader( p_rs, p_fh_output, p_fh_error )
{
var l_buffer;
var l_fields;
var l_field;
var l_name;
var l_cols;
var i;
l_fields = p_rs.fields;
l_cols = l_fields.count;
l_buffer = '';
for ( i=0; i -1)
{
// we have found a '"' or a ','
l_buffer += '"' + l_value.replace(greQuote,'""') + '"';
}
else
{
// no ',' or '"', so no need to process
l_buffer += l_value;
};
break;
case 'HTML':
if ( l_value.search(greAmpLtGt) > -1)
{
l_value = l_value.replace(greAmp, '&');
l_value = l_value.replace(greLt, '<');
l_value = l_value.replace(greGt, '>');
}
l_buffer += l_value;
break;
default:
l_buffer += l_value;
} // switch
break;
default:
l_buffer += l_value;
} // switch (type)
} // if not null
} // for each column
// Remove excess value seperator at start, and append row trailer.
l_buffer = l_buffer.substr(lth) + gsTDR_;
// Add row header, substituting row number
if (gsTDR)
{
l_buffer = gsTDR.replace(grePercD, l_rows) + l_buffer;
}
// Output row
p_fh_output.WriteLine(l_buffer);
} // for each row
p_fh_error.WriteLine( l_rows + ' row' + (1==l_rows?'':'s') + ' processed.');
} // function WriteBody()
// ---------------------------------------------------------------------------
// NAME
// lpadnum2()
//
// DESCRIPTION
// Pads a number to 2 digits by adding a '0' at the front if necessary.
// ---------------------------------------------------------------------------
function lpadnum2( inValue )
{
if (inValue<10)
{
return '0' + inValue;
}
else
{
return '' + inValue;
}
} // function lpadnum2()
// ---------------------------------------------------------------------------
// NAME
// DateToString()
//
// DESCRIPTION
// Formats a date to my specification.
// ---------------------------------------------------------------------------
function DateToString( idDate, isFormat )
{
var lnFullYear;
var lnMonth;
var lsMonth;
var lnDate;
var lsDate;
// If we get an 'activeX' date instead of a date object, covert it.
if (typeof(idDate)=='date')
{
idDate = new Date(idDate);
}
// Get year
lnFullYear = idDate.getFullYear();
// Convert Month to 2 digits
lnMonth=idDate.getMonth();
lsMonth=lpadnum2(1+lnMonth);
// Convert Date to 2 digits
lnDate=idDate.getDate();
lsDate=lpadnum2(lnDate);
// Default format
switch (isFormat)
{
case 'DD/MM/YYYY' : return lsDate + '/' + lsMonth + '/' + lnFullYear;
case 'MM/DD/YYYY' : return lsMonth + '/' + lsDate + '/' + lnFullYear;
case 'DD-MON-YYYY': return lsDate + '-' + gasMonthAbrv[lnMonth] + '-'
+ lnFullYear;
case 'MON-DD-YYYY': return gasMonthAbrv[lnMonth] + '-' + lsDate + '-'
+ lnFullYear;
case 'YYYYMMDD' : return lnFullYear + lsMonth + lsDate;
case 'YYYYMMDDHHMMSS':
return '' + lnFullYear + lsMonth + lsDate
+ lpadnum2(idDate.getHours())
+ lpadnum2(idDate.getMinutes())
+ lpadnum2(idDate.getSeconds());
case 'ISO 8601':
default : // ISO 8601 YYYY-MM-DDTHH:MM:SS
return lnFullYear
+ '-' + lsMonth
+ '-' + lsDate
+ 'T' + lpadnum2(idDate.getHours())
+ ':' + lpadnum2(idDate.getMinutes())
+ ':' + lpadnum2(idDate.getSeconds());
} // switch
} // function DateToString()
// ============================================================================
// END OF FILE