Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
XML case: automated export with dynamic schema
Hi all, not sure this is right newsgroup for my questions.
I'd like to provide my users with Excel documents (simple lists with column headers). Then, I'd like to upload this excel document into SQL Server, in a normalized model The excel has a not-predictable layout, except that column headers will be within a predefined set of possibilities and there will always be a field CODE. CODE LONG_DESC SHORT_DESC DUNS ... 1 LONGa SHORTa DUNSa 2 LONGb SHORTb DUNSb 3 LONGc SHORTc DUNSc -- upload into data model CODE FIELD (e.g. LONG_DESC,SHORT_DESC, DUNS...) VALUE (e.g. LONGa, SHORTa, DUNSa, LONGb, SHORTb, DUNSb, LONGc, SHORTc, DUNSc...) My idea is to use XML: - export excel to XML: this will be denormalized (lots of troubles with this) - normalize this XML with XSLT to CODE, FIELD, VALUE elements (I have covered this) - upload to SQL Server with OPEN XML (I have covered this) My main problem is to automate the process of exporting to XML from an Excel file. Different parts of the problem, as far as I got: 1. I need to be able to generically create an XML map for exporting purposes 2. this must run stand-alone: the Excel-sheets (no macros in the sheet) will be dropped in a directory and then the process must start. Maybe, I could make a long XSD with all possible fields, but then I must map the columns with the corresponding XML elements. The column order in the Excel sheet might be different than the element order in the XML map. Anyone did something comparably, or can point me towards some reference that would help me? Thanks in advance. Bert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count colored cells? | Excel Worksheet Functions | |||
VBA Code | Excel Discussion (Misc queries) | |||
convert numbers to text | Excel Discussion (Misc queries) | |||
How to .. | Excel Discussion (Misc queries) | |||
Identifying the Active Fill Color | Excel Discussion (Misc queries) |