Project LID Homepage: Distributable Modules
LCCSQLDataExport Homepage
Updates

Update 6/20/205: Upgrade: implented white space characters for keys "lcc:valuesFlagReplaceCRLF", "lcc:valuesFlagReplaceTab"

Update 9/5/2024: Upgrade: added key lcc:dataColumnsQualifyForceOff

Update 10/27/2023: Upgrade: handle auto/manual column lists better

Update 10/16/2023: Log fix: removed debug line

Update 10/10/2023: Upgrade: added key "lcc:chunkSize"

Update 2/27/2023: Upgrade: added key "lcc:dataEncodeType"

Update 9/30/2022: Upgrade: added retries when receiving a timeout on Table Fills (i.e. pulling records down from server). Will now try up to 3 times, with 1 second pause between.

Update 6/21/2022: Change: when exporting to file, the last line of data will no longer have new line characters (CR/LF) appended, i.e. no blank line showing after the last line of data.

Update 2/7/2022: Upgrade: Upgraded to .NET 4.8 and changed oracle DLL to differnt/more recent version "Oracle.ManagedDataAccess.dll".

Update 9/9/2021: Upgrade: Changed program to 64bit version, as well as 64bit Oracle driver to support larger tables.

Update 8/24/2020: Upgrade: Added Key 'lcc:dataColumnsQualifyAllExcludeEmpty'.

Update 7/7/2020: Upgrade: Added Key 'lcc:logicFromFile'. Updated Whitepaper for better Syntax/Example formats.

Update 6/25/2020: Upgrade: Added support for using lcc:SQLQuery and lcc:dataExportFilename multiple times in a single Logic File.

Update 6/8/2020: Upgrade: added Keys 'lcc:dataColumnsAutoFilterOut', 'lcc:dataColumnsHeadersAutoFilterOut'

Update 5/18/2020: Upgrade: added adhoc command line key id/values, 'lcc:paramId', 'lcc:paramValue'

Update 4/20/2020: Upgrade: added Key 'lcc:dataColumnChangeFormat'

Update 4/8/2020: Upgrade: issue with long queries on closing connection for retrieving schema/columns information.

Update 4/2/2020: Upgrade: added Key 'lcc:dataColumnsQualifyAll'.

Update 4/1/2020: Upgrade: Added column qualifier (double quotes) if a column value contains a double quote, and escaping any double quotes.

Update 3/22/2020: Upgrade: Added flag keys for [~WATerms...

Update 2/7/2020: Upgrade: Added Keys 'lcc:recordsFoundHeartbeat', 'lcc:flushMaxRecords'. Upgraded memory management for large downloads.

Update 1/8/2020: Upgrade: Added Tab Delimited Logic Files, see 'lcc:logicPath'. Added Adhoc User Input, see 'lcc:userInput'.

Update 12/13/2019: Upgrade: improved Auto Column Headers.

Update 12/6/2019: Upgrade: improved lcc:SQLQueryFromFileFromList processing.

Update 12/5/2019: Upgrade: Added keys Added keys lcc:dataColumnsAuto, lcc:dataColumnsHeadersAuto

Update 11/22/2019: Upgrade: Added keys "lcc:valuesFlagReplaceCRLF", "lcc:valuesFlagReplaceTab"

Update 11/14/2019: Upgrade: added 32 and 64 bit versions to support latest Oracle Database driver files

Update 5/6/2019: Upgrade: added flags to sqlQuery 'WAYRQQuarters...Year'.

Update 2/1/2019: Upgrade: added SQLConnectionCommandTimeout.

Update 9/19/2018: Upgrade: added queryStatementFillIn capability to the dataExportFilename key.

Update 8/17/2017: Document update.

Update 4/13/2017: Upgrade: added Date/Time Flags.

Update 2/17/2017: Bug fix: when using key 'SQLQueryFromFileFromList', was not resetting output records between each connection.

Update 1/10/2017: Upgrade: skips loading Oracle libraries if 'SQLConnectionPlatform' is not configured for 'Oracle'

Update 12/2/2016: Added column qualifier (double quotes) if a column value contains the export delimiter.

Update 11/22/2016: Added keys 'SQLConnectionPlatform', 'SQLConnectionDataPort', 'SQLQueryFromFileFromList'. Now supports Oracle connections.

Update 7/19/2016: Added sub-key 'lccQueryFromFile'.

Update 9/10/2015: Added key 'lcc:exportColumnCapitalize'.

Update 12/10/2014: Fixed bug with 'lcc:exportColumn[...]' keys, updated PDF notes on same keys.

Update 12/13/2013: Modified 'minimum column' logic to add mandatory blank column 'before' processing 'swap' logic.

Update 5/15/2013: Added runPostCommand to perform post-processing commands.

Update 5/3/2013: Fixed a bug with the Column SplitAt option.

Update 11/15/2012: Added timeout.

Update 10/30/2012: Added exportColumnPadLeft/Right.

Update 2/17/2012: Added SQL Query Fill-In settings to allow Query modifications by non-SQL users, i.e. Quarter Codes

Update 4/21/2010: Added exportColumnPosition, exportColumnMinLength, exportColumnMaxLength. These enhancements allow you to control the minimum and/or maximum length a column of data will end up in the exported data. For example, you may want the Middle Name column to be only a single character. If you set a minimum length and the data is less, it will be right-padded with spaces.

Update 1/12/2010: Added dataColumnSwapColumns to allow swapping of split columns, i.e First Name with Last Name. See updated manual for details.

Update 12/1/2009: Bug Fixed: issue with TrimLeftOn/TrimRightOn with empty records.

Update 11/23/2009: Added dataColumnsHeaders and dataRecordsMaxPerFile.

Update 9/25/2009: Added dataColumnSplitAt, dataColumnTrimLeft and dataColumnTrimRight settings.

Update 9/11/2009: Program released.

Description

lccSQLDataExport was created to export data from SQL servers. This data could either be viewed or exported to a file. As the data is exported, it could be manipulated.

This need came about with the introduction of some new tools (HPSA, ODS, ADP) that allow institutions to download data from the state database system (HP3000) to local SQL servers.

The lccSQLDataExport application does not worry about the storage of data or the database engine for querying information, but, rather handles receiving the records from a query and manipulating as needed. One specific need that arose for this tool was that some columns in the HP3000 database are actually multiple fields in one. For example, a students name is stored in a column as DOE JOHN TIM. This tool will allow you to export the data and split that column into multiple columns as the data is read/exported. In this example, you can also designate the minimum and maximum number of columns to produce from splitting one column. This handles situations where information (like a name) may have only two names, while other records may contain a name with 5 parts, but, your requirements may need a specific numbers of columns for other programs/need, like [LAST], [FIRST], [MIDDLE]. By designating a minimum, the application will append column separators if the data is too few parts, and concatenate parts if it is too many parts.

Application Files

Documentation

Disclaimer
The programs, scripts and documentation are provided AS IS without warranty of any kind. Lower Columbia College further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the programs, scripts and documentation remains with you. In no event shall Lower Columbia College, its authors, or anyone else involved in the creation, production, or delivery of the programs, scripts or documentation be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the programs, scripts or documentation, even if Lower Columbia College has been advised of the possibility of such damages.