Sample Access Database

All,

Here is a sample Access Database file that will open a csv file and sort through all of the data to find only the rows that have to deal with a certain user.

Dumping your data to a text file during a model run is fast way to export tons of data. In this case we formatted all of our dumps so that they would all be formated the same way. This database was created because our original file will have more than Excel's maximum of 33k lines (this is the problem of dumping out tons of data) but we don't expect the sorted data to have more than that. So we needed a file that would sort the data and create a new csv file that we could then open in Excel to create graphs and such for.

The incoming information is expected to be comma seperated and Formated in the following way (The sample input file only has the first four columns):

Name - Text
ObjectType - Text
EventType - Text
N1 - Number
S2 - Text
S3 - Text
N4 - Number
N5 - Number
N6 - Number
N7 - Number
N8 - Number
N9 - Number
N10 - Number

There is a table called names in the file that holds all of the expected names. It is used to populate the Combo Box.

When you open the file double click on the main form and select the Machine Name that you want to sort through the file for. Next Click the "Generate File" button. A GUI will then have you search for the file you want to sort through (Test Output.csv). The code will then create a file (name - OriginalFileName.csv) will in the same directory as the file you selected. This new file will only have the data for the machine of interest.

All of the code (VB) is commented as usual so that you can understand what is going on.

I have also included a Test Output Generator Excel file that you can use to generate a new output file. Change the data and then export the Test Output tab as a csv file.

I am thinking that I will post any future sample Access databases in this thread as well.

Please post any questions or comments,
Brandon

P.S.
If you have problems running the code (You will know if you do) hit Alt-F11 to pull up the VB code editor. Goto Tools -> References and uncheck "Microsoft Office 12.0 Object Library" and then check the "Microsoft Office X.0 Object Library" that you have in your list. 11.0 is for 2003 and 12.0 is for 2007.

For those who may be unfamiliar with how to export data to a CSV file, here is some code that will export the name of the objects located directly beneath model() in the tree:

string filepath = filebrowse("*.csv","Excel Files",concat(cdir(),"userprojects")); // get the name of the file to open
if (stringlen(filepath) == 0) // This is true when the user presses Cancel on the file browse window
{
return 0;
}
fileopen(filepath); // open a link with an Excel workbook
for(int i = 1; i <= content(model()); i++)
{

fpt(getname(rank(model(),i)));fpr();
}
fileclose(0);


The basic steps are as follows:
-call fileopen(filepath) on a file
-call your ftp();,fpf();,fpd();, and fpr(); commands
-call fileclose(0);

Here is a quick rundown of the file print commands:
fpt() will export a string value to the open file
fpf() will export a float value to the open file
fpd() will export a integer value to the open file
fpc() will export a ASCII value to the open file
fpr() will tell the file you are moving to a new line (row)

*Some important things to remember are that you must print a comma inbetween each value that is exported. When you print a comma, that is just like moving to the next cell when working in excel.

If you need to move to a new line you must print a fpr(); to return to the next line.

For instance:
fileopen(filepath);

fpt("NAME");fpt(",");fpt("Processor4");fpt(",");fpt("RANK");fpt(",");fpd(10);fpr();
fpt("NAME");fpt(",");fpt("Combiner24");fpt(",");fpt("RANK");fpt(",");fpd(15);

fileclose(0);

Would write:

NAME,Processor4,RANK,10
NAME,Combiner24,RANK,15

to the csv file specified as 'filepath'


When this csv file is opened in excel it would have:

NAME in Row1 Col A, Processor4 in Row 1 Col B, RANK in Row 1 Col C, and 10 in Row 1 Col D.
NAME in Row2 Col A, Combiner24 in Row 2 Col B, RANK in Row 2 Col C, and 15 in Row 2 Col D.