Using VBA to Create Dummy text files for SQL Server

In this post, I am going to demonstrate a VBA class I built to help create dummy text files. Creating dummy files allows me to work with mock data, in the absence of any real data when working on new projects. The idea behind the class is to be able to create text files of any length and column size, and with as many different data types as possible. With dummy data I can start to build out the mechanics of a new import process for a new project and test out any potential issues with whatever setup I am dealing with. The class also creates the required SQL statement needed to create a table that fits my text file.

Preliminary Steps

If you are going to use the code within a new Workbook, make sure that the Microsoft Scripting Runtime reference is ticked within the VBE Reference Window:

The Text File Generator Class

This is the entire code for the textfile class which I named csTextFileGenerator:

 
How to use the class

So in the example code below, I am using the class to create a comma delimited text file that has 25 rows and 12 columns called “Test_YYYYMMDD.csv” which will be saved in the same location as my macro workbook:

Output

This is what the dummy CSV file will look like:

In addition to this, a second text file is generated containing the required SQL needed to create a table in SQL Server to store the file:

Hopefully, you can use this class and the steps outlined here to create dummy test files for your own projects!

Thank you for reading this post. Please take time to read the disclaimer about content found on this site.

Share :Facebooktwittergoogle_plusredditpinterestmail