Welcome to Tek-Tips.

To do this you will need to use XSLT to translate between the 'native' XML created by Excel (named SpreadsheetML by Microsoft) and the XML demanded by Revenue Canada.  XSLT is designed to perform translations from XML to XML, HTML and text.

There are several tools that can help you build XSLT.  I prefer Stylus Studio.  Others include XML Spy and Cooktop.  Cooktop is free, but as with most 'free' things, it requires a bit more effort on your part to make it work.  Both Stylus Studio and XML Spy have free evaluation periods.  I will tell you the steps using Stylus Studio; YMMV.

Go to the Microsoft web site and download the reference schema for the version of Excel you are using.  These schema describe the XML documents produced by MS Excel.  Stylus Studio can use the Excel schema directly as an input document for building the XSLT transform.  See http://office.microsoft.com/en-us/excel/HA102063961033.aspx as a starting point.

It does not appear that Revenue Canada has an XML schema on its web site for the T4.  Perhaps there is one and I just did not find it.  However, the web site does provide an example T4 XML submission document.  Stylus Studio can use that example document as its output document for building the XSLT transform.

With the input and output document in place, you can use the drag-and-drop XSLT mapper in Stylus Studio to create the mapping from input to output document.  The text file thus produced is an XML document that is your XSLT.

You can then use the XSLT directly in Excel to transform the 'native' SpreadsheetML containing your data into a Revenue Canada T4 XML submission document.

This is the general outline of the steps you will need to take.  Be sure to consult the help files of whatever products you choose to use, as this is nowhere near a detailed specification of the process.  However, if you have problems, please feel free to come back here for help.  As you might discern from other threads, specific questions with details are rather quickly and efficiently answered here.  Vague questions, however, are not our top priority.

Tom Morrison
www.liant.com

Thanks Tom!!!

BTW, schema files are located at
http://www.cra-arc.gc.ca/eservices/magmedia/programmers/xml-e.html

Hi Tom,
I have downloaded the Stylus Studio and XML schema files from Revenue Canada's web site, and already converted my spreadsheets into spreadsheetML. Could you please tell me the steps how to use XSLT mapper in Stylus Studio to create the mapping from input to output document?

Thanks,

Kaschif

This is the subject of a Chapter in the Stylus Studio help file, Chapter 5 Creating XSLT Using the XSLT Mapper.

The basic steps are:

• File, New, XSLT Stylesheet
• Enter the pathname of your SpreadsheetML document for the Source XML URL
• Click on 'Set Target Document' (on the right side) and point to Revenue Canada schema.  I think you want layout.xsd
• Expand the Source Document and Target Document trees
• Start draggin'-and-droppin'.  Click on an element in your SpreadsheetML layout (displayed in the Source Document tree) and drag across the center to the corresponding element on the Target Document tree.

One special note:  I presume that you will have one Excel row/T4Slip.  In order to get the <xsl:for-each> instruction, you will have to drag from the <Row> (which should be depicted as a repeating element) to the <T4Slip> (which also is depicted as a repeating element).  Search the Stylus Studio help for "Document structure symbols" to see the symbol which depicts repeating elements.

Once you have a map created (or even partially created) you can use the XSLT/Preview Result command to see the output.

A hint:  you will probably want to add an <xsl:output indent="yes"/>  instruction immediately after the <xsl:stylesheet> instruction.  This makes things pretty to look at.

Happy draggin'!   

Tom Morrison
www.liant.com

Thanks again Tom!!!

Hi Tom,
Thanks for your help, now when I load my SpreadsheetML it doesn't load the contents (for example Column names) in the source tab, it loads generic column names in the source tab. And when I load layout.xsd in the target it loads exactly as described in the Chapter 5 Creating XSLT Using the XSLT Mapper. After loading both files how would I drag and drop those generic columns from source to the actual column schema names in the target tab?

kaschif

http://www.mediafire.com/?6lz4xosgnm9

• http://www.mediafire.com/?6lz4xosgnm9

Ok, I tried the XML Mapping feature of Excel, and that didn't work.  So what you must rely on is the fact that the <Cell> elements for each <Row> are output in the same order.  I crafted a dummy worksheet (reduced in size for the purpose of this example):



I did this in the map view by dragging the <Data> element to the T4 element then adding the predicate (e.g. the thing that looks a lot like an array subscript [1] above) to select the correct <Cell> within the row.  The resulting map had four lines originating in the <Data> element on the left (input) tree.

I am guessing that you have a lot of rows, one row/employee.

Sidebar question:  Is the data really originating in an Excel spreadsheet?

Tom Morrison
www.liant.com

For most of the spreadsheet data is coming from SQL Server, but there are some spreadsheet that have data of their own.

Kaschif

Kaschif, did you get something working?

Tom Morrison
www.liant.com

I had a bit of time to burn, so I did a small enhancement.  The following XSL expects the column headers (names) to appear in the first row, and the repeated data in the 2nd through nth rows.  The first part of the stylesheet sets variables for the column index values that contain the data of interest.  (Note that the output of the stylesheet has nothing to do with Revenue Canada data structures.  This is merely to demonstrate a technique that is a bit more mnemonic that literal column index values.)

Tom Morrison
www.liant.com