Getting started with ExportTxt2msSQL

Introduction

Did you ever want, for some reason, to export a set of code files to SQL Server, with each line in the text file becoming a row in the table ?

May be, you thought you could do your own analysis of the code
May be, you wanted to plan for migration to another language or platform
May be, you wanted to write your own quick queries to find out some patterns in good/bad code you inherited to maintain ?

Whatever the need, if you wanted notepad-readable text files or code files to be exported to Microsoft SQL Server, (export as in DTS), then this utility might come in handy.

At the time of this writing, I think I did find tools that can merge text files for you, but I couldn't find any that will also export that for you to SQL Server and something you run again with parameters drawn from an xml config file.

---

What ?

Useful for exporting each line in a text file (or set of text files) to Microsoft SQL Server as a row in a table.
Can also be used for exporting files with other extensions such as frm, bas or other notepad-readable files.
Useful for code lines analysis and such other text analysis tasks.

How ?

The parameters for the export run are drawn from the xml file MyConfig.xml in the same folder as the executable.
UserId and password for the destination SQL Server connection are sought in a form that loads.
Other parameters cannot be edited in the form. They will be drawn from the config file.
If the config file mentions that the run be unattended, UserId and Password are drawn from the config file too.

Parameters in the Configuration File and what they mean ?

(1). SKIP_COMPLETION_HEADERS_YES_NO

Before and after each text file is processed, the utility adds an additional row in the same table, logging success and other details. This is called Completion Header.

Values should be YES or NO. Can be left blank. Default Value is NO.

(2). COMPLETION_HEADER_PREFIX
Prefix which can be appended to the completion header, to differentiate it from an exported text line.
For example, you can include the commenting begin character of your programming language. Say, '--- for Microsoft Visual Basic 6.0

(3). COMPLETION_HEADER_SUFFIX

Suffix which can be appended to the completion header. Similar to the prefix above.

(4). HEADER_TEMP_TEXT_FILE_NAME

Temporary text file name where the headers are written before export.
By default, it is TempHeader.txt in the same folder as the executable.
Should have write rights, if completion headers are required.

(5). LOG_TEXT_FILE_NAME

Text file for logging errors or initiation.
By default, it is MyLog.txt in the same folder as the executable.
Should have write rights.
Contents will be overwritten in the beginning of each run.

(6). UNATTENDED_YES_NO

If marked YES, the username and password for the Destination SQL Server is drawn from the config file and no form/UI is presented for entering the same waiting for user input.
Default is NO.

(7). SOURCE_TEXT_FILE_NAME_WITH_PATH

To be used if you wish to export a single text file.
Ignored if SOURCE_FOLDERS_LIST parameter below is not blank. In that case, the text files in those folder(s) are processed.
Include full path and name.

(8). SOURCE_FOLDERS_LIST

List of folders to be looped and processed.
For separation character for the list, see the next parameter below.
Include full path of the folder.

(9). SOURCE_FOLDERS_LIST_SEP_CHAR

Separation character for parsing folders list, found in the above parameter.
Default is | ( the pipe character).

(10). SOURCE_FILES_EXCLUSION_LIST

Filenames mentioned in this list will be excluded.
Include file names with full path, separated by SOURCE_FILES_EXCLUSION_LIST_SEP_CHAR.

(11). SOURCE_FILES_EXCLUSION_LIST_SEP_CHAR

Separation character for parsing list of files to be excluded.
Default is | ( the pipe character).

(12). SOURCE_FOLDER_AVOID_RECURSION_YES_NO

Should be YES or NO. Default is NO.
When YES, recursion is avoided, subfolders are not looped for text files.

(13). SOURCE_FILE_ROW_DELIMITER

Default is the word VBCRLF. Meaning, the new line character as read by Microsoft Visual Basic 6.0.
Relates to the Row Delimiter parameter of Microsoft Data Transformation Services Packages in Visual Basic 6.0.
Not tested with other row delimiters.

(14). SOURCE_FILE_EXTENSIONS_COMMA_SEP_LIST

List of file extensions to be processed. The list should be comma-separated.
Default is FRM,BAS,CLS

(15). SQL_SERVER_NAME

Name of Destination SQL Server Instance. Must be as seen in @@SERVERNAME global variable of Microsoft SQL Server.
Usually, server_machine_name\SQL_Server_Instance_Name.

(16). SQL_SERVER_DATABASE_NAME

Database name in Destination SQL Server.

(17). UNATTENDED_RUN_USER_NAME

User Name to be used for connecting to Destination SQL Server Database when the utility is set to run as unattended with UNATTENDED_YES_NO parameter above.

(18). UNATTENDED_RUN_PASSWORD

Password to be used for connecting to Destination SQL Server Database when the utility is set to run as unattended with UNATTENDED_YES_NO parameter above. Read in plain text from the config file.

Note !! Make your own security arrangements !! If you require encryption of passwords, you should modify the source code accordingly to read authentication information from another encrypted file.

(19). DESTINATION_TABLE_NAME

Table to which each text line will be exported as a row.
Ensure Insert rights and other permissions required for a typical DTS export.
Must be present already. If table is not found, export will fail.
Default is SysCodeLines_Temp

(20). DESTINATION_COLUMN_NAME

The Column name to which text line will be exported as row.
Must be present already. If column is not found, export will fail.
Default is MyCodeLine.

Note : The DESTINATION_TABLE_NAME can contain other useful columns such as an Auto Number column or a Batch Id that your script may update later. DESTINATION_COLUMN_NAME is the only required column.
Also note that, the only way to identify rows inserted in a given run/batch is the BATCH_CODE found in the completion headers.

Language :

Developed in Microsoft Visual Basic 6.0 SP6.
Tested for export to Microsoft SQL Server 2000 and Microsoft SQL Server 2005.
Might work for higher versions of SQL Server, but untested.
Uses the DTS Package code generated in Visual Basic 6.0, that is generated by Microsoft Data Transformation Services shipped with SQL Server 2000. Such package (.bas file) has been parameterized to include any specified text file to any specified table, as per parameters drawn from a config file.

Limitations :

Config files should be hand-edited with a text editor or auto-edited through any other script. Editing of the config file through the User Interface is disabled currently. View only.
Limited error handling/logging/in-line commenting in the source code. Treat it as quick-dirty but functional code.

Does not update any other fields in the destination table with some Batch Id to identify the lines exported during a run. Look for Batch_Code in the imported lines to identify the batch.

Why it started ?

Wanted to export all the code files related to an application written in Microsoft Visual Basic 6.0, that evolved over the years and that used third party controls. Wanted to see major areas that may get impacted and plan for migration to .NET. Hoped/hoping also to be able to MAKE the migration changes through SQL scripting ??

Also wanted to do other things such create a HTML file automatically that showed the menu tree of the application in the tree form.