Excel XML Map Error...Need Help...NEWBIE

[hammer02]

We have been given an XML format that we need to generate for submitting environmental data to the Ontario Ministry of the Environment. Below is a sample of data that was provided.

I can import this into Excel but when I try to verify this for exporting, or even export with the validation turned off I get the following error: submission_map is not exportable because it contains the following: List of Lists

I know this is the fact that the result element is repeating and it is inside of a repeating element called sample. Is there anyway to overcome this and use the Excel export capabilities or am I stuck having the export file written using VB?

Thanks. MH

Sample XML file below...

<submission imis_company_code="0009990003" ws_name="INDUSTRIAL TEST PLANT">

<sample sample_date_time="20060607" imis_cp_ext="0100" cp_name="WASTE WATER FROM UNIT #1" dischg_sample_cd="" sample_comment="">
<result rpttime_name="THRICE WEEKLY" sis_imis_code="FTFLOW" parm_name="Flow" unit_abbrev="M3/D" rptas_abbrev="NOT APPL" result_structure="SI" component_type="SM" result_value="32101" remark_code_1="" remark_code_2="" remark_code_3="" result_comment="" imis_regulation_code_1="MC" imis_regulation_code_2="" imis_regulation_code_3="" imis_regulation_code_4="" imis_regulation_code_5="" imis_regulation_code_6="" imis_regulation_code_7="" imis_regulation_code_8="" imis_regulation_code_9="" imis_regulation_code_10=""/>
</sample>

<sample sample_date_time="20060607" imis_cp_ext="0400" cp_name="COOLING WATER STREAM" dischg_sample_cd="" sample_comment="">
<result rpttime_name="WEEKLY" sis_imis_code="PBUT" parm_name="LEAD, UNFILTERED TOTAL" unit_abbrev="MG/L" rptas_abbrev="NOT APPL" result_structure="SI" component_type="SM" result_value="3.2" remark_code_1="" remark_code_2="" remark_code_3="" result_comment="" imis_regulation_code_1="MC" imis_regulation_code_2="" imis_regulation_code_3="" imis_regulation_code_4="" imis_regulation_code_5="" imis_regulation_code_6="" imis_regulation_code_7="" imis_regulation_code_8="" imis_regulation_code_9="" imis_regulation_code_10=""/>
<result rpttime_name="WEEKLY" sis_imis_code="RSP" parm_name="RESIDUE,PARTICULATE" unit_abbrev="MG/L" rptas_abbrev="NOT APPL" result_structure="SI" component_type="SM" result_value="0.1" remark_code_1="" remark_code_2="" remark_code_3="" result_comment="" imis_regulation_code_1="MC" imis_regulation_code_2="" imis_regulation_code_3="" imis_regulation_code_4="" imis_regulation_code_5="" imis_regulation_code_6="" imis_regulation_code_7="" imis_regulation_code_8="" imis_regulation_code_9="" imis_regulation_code_10=""/>
</sample>

</submission>

 

[k5tm]

Are you planning on using Excel as the source of data you are submitting? Presuming this to be true, is your source spreadsheet going to follow this layout exactly?

It would seem more likely that you already have some information in Excel that does not match this format. You can use XSLT to process the spreadsheet saved in XML Spreadsheet format.

Tom Morrison

http://www.liant.com

 

[hammer02]

Yes, the data is going to be stored in Excel. We will be expected to use the facility's existing files they use for retaining the information.

Based on the way they are entering the data it looks like the information will have to be somewhat reorganized. Right now it looks like a logsheet (dates down the side with data values as the columns). The kicker is that some of the values will be submitted weekly while others will be monthly or quarterly. They have indicated they will need to submit the file on a quarterly basis.

I'm very new to this XML stuff so not sure how the XLST would be used.

Thanks,
MH

 

[k5tm]

XSLT is a 'language' for transforming XML documents, with the output being XML, HTML, or text. In this application, you would use XML output, since you are required to submit XML documents.

You may find an online tutorial here.

XSLT is designed to do reorganization. In this case, you may 'reorganize' the spreadsheet saved as an XML spreadsheet. You may download the Office XML schemas (XML documents that describe other XML documents) from the Microsoft web site here.

Finally, you would probably want a tool to help you develop XSLT stylesheets. Two good choices are Stylus Studio (which I prefer) and Altova XML Spy. Both companies offer free trial downloads.

Now, to be perfectly honest, it may be easier for you to resort to VB, but if you have some time to learn XSLT, you will find it a valuable tool in a world that is rapidly moving to XML as the lingua franca for data interchange.

Tom Morrison

http://www.liant.com