How to simplify test data creation with Excel and EXCEL2TXT vbscript

This article describes EXCEL2TXT script that is located here.

The purpose of the script is to conveniently convert excel book to a set of TXT files and then archive it into zipfile - this is a format of data for the mockup_loader. Let say you've prepared a complex unit test which involves many data structures and tables. Supposedly, you enter different dataset on separate excel sheets. In one of our projects the excel sheet bar looks like this:

And sheet content looks like this (fields starting with '_' are skipped by the script):

To use mockup loader you want each sheet like that saved as a tab-delimited TXT file in unicode, then placed into a folder with the same name as the excel file and then compressed into a zipfile. To do this you have to create an additional sheet called _contents which would list all relevant sheets and mark with X those, which need to be saved. The latter is useful when you are designing the test and not all of the stuff works yet - you can disable some of sheets temporarily.

Now (supposedly the excel file is opened in excel currently) you put the excel2txt.vbs script to the same folder as the excel file and run it via cmd or a .bat file:

  excel2txt.vbs -o

The script does the following:

• checks which excel files are located in the current directory
• checks which of them are opened (option -o), ignoring closed ones
• checks existence and information at _contents sheet
• downloads all the X-marked sheets as TXT into uncompressed directory in the current folder and there places them into the directory with the Excel book name (so it is uncompressed\excelfilename\sheetname.txt actually).
• compresses the whole (!) uncompressed directory into mockup.zip file. The whole directory goes to zip assuming you are doing an incremental build (see below).

It's done. Now you can upload the zipfile to SMW0 or refer it as a file from your unit test. As you can see the console outputs also some statistics (errors in particular) on what has been processed and the results summary.

Extra features

Build all

There is a command parameter (-a) that convert all the Excel files in the current directory, opening them if required (only those which have _contents sheet are actually processed).

Also there is a command to explicitly include (copy) an external directory to uncompressed folder. We use it to include some XMLs which are not generated dynamically. So a kind of 'Build complete' command, which rebuild the whole uncompressed folder, would look like:

  excel2txt.vbs -a -i static_asset 

For convenience I created a couple of .bat files for different purposes in the test folder so that I can call incremental or complete build quickly when I need: build-all.bat, build-open.bat ...

GUI

For those who doesn't like console there is a GUI mode - just run the script with a double click. :)

The GUI doesn't have all the console features yet, however, it is more visually friendly.

Color console

And yet for console lovers - you can also get it in color. For this you have to install ansicon and use -color switch. Enjoy! ;)

---

More information on the script usage can be found in EXCEL2TXT.md