I recently had a need write records from a database to text files, with one file per record. This post covers how I did it, with VBScript.
This project was actually part of a much larger project, which in reality was one of those “Let’s see if I can do it” type of projects. It turned out successful, and I thought I would share my methods with anyone who needs some help.
The database being used here is MySQL, with a free joke database being used for the sample data. The test system is Windows 10 home.
First, we need to define some items. First, some constants for working with the file system.
Const ForReading=1
Const ForWriting=2
Const ForAppending=8
Set objFSO = CreateObject("Scripting.FileSystemObject")
For this example, we’ll be using the OpenTextFile method of the Scripting Filesystem Object. This method is used to open a text file and returns a TextStreamObject that can then be used to write to, append to, and read from the file.
| Constant | Value | Description |
|---|---|---|
| ForReading | 1 | Opens a file for reading only |
| ForWriting | 2 | Opens a file for writing. If the file already exists, the contents are overwritten. |
| ForAppending | 8 | Opens a file and starts writing at the end (appends). Contents are not overwritten. |
We’ll only be using one of these (ForWriting), but over the years of adopted the practice of defining all three. The last line in the code block above defines the File System Object, which is the piece that allows VBScript to access the computers file system.
Next, some database connectivity will be set up.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
CFG_Conn="Driver={MySQL ODBC 5.3 ANSI Driver};Server=MyServerName;Database=DemoDB;User=MyUserName; Password=MyPassword;Option=3;"
First, the cursor type and lock type are set. The cursor type sets how we will be working with the recordset, such as read only, updating, etc. MSDN has a great explanation of cursortypes and their values here.
Next, we set the record locking type. Believe it or not, we can actually block access to a record while our silly little program is accessing it. Since we are only going to be reading, not updating or writing, we’ll use the Optimistic setting.
Here is a list which includes other lock types that can be used:
| Constant | Value | Description |
|---|---|---|
| adLockUnspecified | -1 | Unspecified type of lock. Clones inherits lock type from the original Recordset. |
| adLockReadOnly | 1 | Read-only records |
| adLockPessimistic | 2 | Pessimistic locking, record by record. The provider lock records immediately after editing |
| adLockOptimistic | 3 | Optimistic locking, record by record. The provider lock records only when calling update |
| adLockBatchOptimistic | 4 | Optimistic batch updates. Required for batch update mode |
The next two lines create the connection to the database (objConnection), and the recordset object (objRS). Some folks get confused by this, thinking they are one and the same. The first one is creating the connection to the RDBMS itself, as well as the correct database schema within the RDBMS. The recordset object gives you access to a specific table within the database schema.
So, the object have been created, how does data get read? The important part is crafting a correct SQL query. This example is pretty basic, in that there are only a few fields to work with in the first place. Below, a query is defined, the connection to the database, the recordset is opened (based on the SQL query), and we move to the first record of the recordset.
strSQL="select id,category,joke from tbljokes"
objConnection.open CFG_Conn
objRS.Open strSQL, objConnection, adOpenStatic, adLockOptimistic
objRS.MoveFirst
Now, all we need to do is loop through the recordset, a row at a time, and output our data. This sounds like a job for a DO loop:
Do while not objRS.EOF
intRecID=objRS.Fields.Item("ID")
strCategory=objRS.fields.item("category")
strContent=objRS.fields.item("joke")
strPaddedID=trim(intRecID)
strPaddedID=right(string(6,"0") & strPaddedID,6)
strOutputFileName=strPaddedID & ".txt"
Set objTextFile = objFSO.OpenTextFile ("c:\DemoTesting\output\" & strOutputFilename, ForWriting, True)
objTextFile.Writeline("Joke ID: " & intRecID)
objTextFile.Writeline("Category: " & strCategory)
objTextFile.Writeline(" ")
objTextFile.Writeline(strContent)
objTextFile.Close
objRS.MoveNext
Loop
At the top of the loop, it is defined that we are going to perform these actions until there are no more records in the recordset (EOF = End Of File).
The next three lines are data actually being grabbed from the database; the field names are ID, category, and joke.
We’ll be needing a filename, and the next three lines are how we are building it. Since there is a unique identifier in the database (the ID field), it will be used as the base of the filename. first, it is trim’d to make sure there are no trailing or leading spaces. Next, the string is padding with zeroes, so the filenames are a consistent length of six characters. Finally, the .txt extension is added.
Next, here is the FSO in action. First, we define a new object (objTextFile) for writing, we the necessary path, including the filename that was built in the previous step. I’ve used “ForWriting” here, in the event that this script is eventually run as a scheduled job in the future. That way, the script will overwrite existing files with the most recent data from the database.
Once the file is open, some data needs to be written to it, and that is exactly what is going on in the four lines referencing objTextFile. Nothing magical here, eh?
Next, the file object is closed, and the script is instructed to move to the next record in the recordset. Finally, the loop is closed. But wait! There’s more! Once the loop is complete, and there are no more records, you need to close your database connections. Always a good habit to get into to. The connections will close after a period of time, but if you ever build a “busier”, multi-user application, you’ll want to close and destroy your objects in an efficient manner to prevent high memory usage, or worse, blocked requests.
objRS.Close
objConnection.close
And there you have it, dumping of records to a bunch of text files with VBScript. There are about a thousand ways to do this, with many different languages and methods. This is just one method that I used to get something done, quickly. I hope you find it useful and informative, but if you have any questions or comments, please let me know!