fortypoundhead.com

Dump Database to Text Files

Posted On 2016-04-24 by VB6Boy
Keywords:
Tags: VBScript Tip Tutorial Windows
Views: 1214


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!


About the Author

VB6Boy has posted a total of 13 articles.

 


Comments On This Post

By: AnonymousCoward
Date: 2017-03-17

I am creating around 5000 xml files at one location. The xml data for these files is from sql table. I am first storing the data from the table in vb script record set and looping through the record ser to create xml files one by one.The issue I am facing is that xml file creation is very slow i.e. around 1 hour for creating 5000 files. Could you please suggest any other way of doing to improve the file creation performance if any?

By: dwirch
Date: 2017-03-17

One hour? wow that is rather, err, slow.  Sorry, but, wow.

Is the vbscript running on your workstation, reading from a sql on a network location, and writing the xml location to another network location? Or is it all on a local machine? Without seeing the code or db, I can only think that network latency might be introducing slowness in the process. I ran into a similar situation, documented here.

Other than that, I couldn't hazard a guess without at least seeing the code, and a sample dataset.

If you'd like some help, and you're not comfortable posting the above items here, you can mail me directly at scripthelp@fortypoundhead.com.

You might also look at something besides a VBScript to do this processing for you. You probably would not want to use the script above for a large dataset.

I will do some testing this weekend, and post back here what I find out.

By: dwirch
Date: 2017-03-21

@AnonymousCoward: You might want to check out this post.


Do you have a thought relating to this post? You can post your comment here. If you have an unrelated question, you can use the Q&A section to ask it.

Or you can drop a note to the administrators if you're not sure where you should post.


Your IP address is:54.198.233.27

Before you can post, you need to prove you are human. If you log in, this test goes away.



Recent Forum Posts

Fold Code Manager into main KB?
VB6Boy posted on July 22, 2017 at about 14:42 in Site News

Fold Code Manager into main KB?
dwirch posted on July 22, 2017 at about 14:41 in Site News

Fold Code Manager into main KB?
dwirch posted on July 21, 2017 at about 22:46 in Site News

Fold Code Manager into main KB?
dwirch posted on July 20, 2017 at about 7:55 in Site News

Job Spammer: Sam Mallon
dwirch posted on July 18, 2017 at about 18:36 in Spammers

When setting up a certificate authority ...
dwirch posted on July 13, 2017 at about 9:07 in General