// ============================================================================ // // 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 and
// // // 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=''; gsTDR=''; gsTABLE_='
'; gsTH=''; gsTHR_='
'; gsTD=''; gsTDR_='
'; 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