Create a Sample Management XSU File From Excel

Last Updated on Friday, 28 January 2011 10:06 Written by Admin Friday, 28 January 2011 10:06

Ever wanted to convert your XLS record to an Sample XSU file so that you can debug your scripts with sample management switched on? In this article we use some very simple principals to do just that.

First thing we need to do is to open up professional and create an mrs file. Next declare the following variables.

Dim oFSO, oFile
Dim sFieldTemplate
Dim sPath,sTmp
Dim oRecordset, oConnection,oField

You should be able to understand what the variables are used for from their names so we won’t bother explaining them. Next type the following in

sFieldTemplate = "   <SampleField>" + mr.newline
sFieldTemplate = sFieldTemplate + "   <name>[Name]</name>" + mr.newline
sFieldTemplate = sFieldTemplate + "   <type>[Type]</type>" + mr.newline
sFieldTemplate = sFieldTemplate + "   <value>[Value]</value>" + mr.newline
sFieldTemplate = sFieldTemplate + "  </SampleField>" + mr.newline

If you have ever opened up an XSU file you would have seen that it has a very simple layout, just a few simple xml tags , but what seems to get repeated is the sample field section. The code above has been setup so that we can re-use it , a bit like a template , as we loop the fields in the Excel file we will place the relevant values into this string and then finally write the line out. Next type the following

sPath = "D:DATASD"

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFile = oFSO.CreateTextFile(sPath + "Sample.xsu", True)

oFile.WriteLine("<SampleRecord ID=""ID1"">")
oFile.WriteLine(" <SampleFields>")

Set oConnection = CreateObject("Adodb.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrADODsc;MR Init MDSC=mrADODsc;Initial Catalog=" + sPath + "sample.adoinfo"

oConnection.Open()

What we do next is to setup the objects that are going to be used to write the XSU file out and create the database connections. You will notice also that we setup the first two tags of the XSU file , which you will see that we close at the end of the script before we close the file. To read the Excel file We will be using the ADODSC so you will need to create an “???.adoinfo” for your file, something similar to this one ( Excel 2003 called Sample.adoinfo)

<ADODSC>
    <ConnectionString>
        DSN=Excel Files;
        DBQ=./sample.xls;
        DriverId=790;
        MaxBufferSize=2048;
        PageTimeout=5
    </ConnectionString>
    <Table>
        Sheet1$
    </Table>
</ADODSC>

Once we have the Sample.Adoinfo file type the next bit of code.

If oConnection.State = 1 Then

 oRecordset.Open( "SELECT * FROM VDATA" , oConnection , 3 , 3)
 For Each oField In oRecordset.Fields
  sTmp = sFieldTemplate
  sTmp = Replace(sTmp,"[Name]",oField.Name)
  sTmp = Replace(sTmp,"[Type]","8")
  sTmp = Replace(sTmp,"[Value]",oField.Value)
  oFile.WriteLine(sTmp)
 Next
 oRecordset.close()

End If

In this bit of code we do the standard , let’s check we got connected to the data, and if we did , then rather than loop the data we loop the current fields collection. As we loop the fields we use take our template string , replace the values with the real values from the data , write the string out and then start the process over again. And Last of all when we reach the end we step out the loop and close all our variables down.

Set oConnection = Null
Set oRecordset = Null

oFile.WriteLine(" </SampleFields>")
oFile.WriteLine("</SampleRecord>")

Set oFile = Null
Set oFSO = Null

And there you have it , when you run this against your Excel file that holds your data it will produce a nice clean XSU file of the very first record.

Related posts:

1. Create your own RSS 2.0 Feed
2. Loading a card column file into Excel
3. Learn ODBC : Deleting a records
4. Printing respondent answers from a Data Management Script
5. Learn ODBC : Updating records