Loading SpreadsheetML to Oracle Database`
JEFTPHPHMar 14 2013 — edited Apr 15 2013Hi! Good Day to all.
I was given a task of processing an Excel File that was created over an XML (I think this is called an SpreadsheetML, please correct me if I am wrong).
With my little knowledge on XML I would like to seek help if it will be a possible to extract the data on this file and load it in an Oracle 10g Database?
Note: When opening this in excel, the file contains multiple worksheet., I only need to deal with one worksheet and this is all I need to load in the database to further process the data.
Below is an extract of the File:
<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> <LastAuthor>APS_OWNER</LastAuthor> <Created>2013-03-05T07:03:29</Created> <Version>11.6408</Version> </DocumentProperties> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>8445</WindowHeight> <WindowWidth>11115</WindowWidth> <WindowTopX>720</WindowTopX> <WindowTopY>375</WindowTopY> <RefModeR1C1/> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/><Font/> <Interior/> <NumberFormat/> <Protection/> </Style>
<Style ss:ID="ColumnHeader"> <Alignment ss:Vertical="Bottom" ss:Horizontal="Center"/> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="10" ss:Bold="1" ss:Underline="Single"/> <Interior/> <NumberFormat/> </Style>
<Style ss:ID="ColumnHeaderDate"> <Alignment ss:Vertical="Bottom" ss:Horizontal="Center"/> <Font ss:Size="10" ss:Bold="1" ss:Underline="Single"/> <Interior/> <NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yy;@"/> </Style>
<Style ss:ID="NumberStyleBlueCell"> <Alignment ss:Horizontal="Right"/> <Font/> <Interior ss:Color="Cyan" ss:Pattern="Solid"/> <NumberFormat ss:Format="###,###,###.00"/> </Style>
<Style ss:ID="TextStyleBlueCell"> <Alignment/> <Font/> <Interior ss:Color="Cyan" ss:Pattern="Solid"/> <NumberFormat/> </Style>
<Style ss:ID="NumberStyle"> <Alignment ss:Horizontal="Right"/> <Font/> <Interior/> <NumberFormat ss:Format="#,##0"/> </Style>
<Style ss:ID="NumberStyle2"> <Alignment ss:Horizontal="Right"/> <Font/> <Interior/> <NumberFormat ss:Format="####"/> </Style>
<Style ss:ID="DateStyle"> <Alignment/> <Font/> <Interior/> <NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yy;@"/> </Style>
<Style ss:ID="ColumnSum"> <Alignment ss:Horizontal="Right"/> <Font ss:Color="Blue"/> <Interior/> <NumberFormat ss:Format="###,###,###.00"/> </Style>
<Style ss:ID="RowSum"> <Alignment ss:Horizontal="Right"/> <Font ss:Color="Red"/> <Interior/> <NumberFormat ss:Format="###,###,###.00"/> </Style>
</Styles>
<Worksheet ss:Name="NO_Details_Down"> <Table ss:ExpandedColumnCount="18" ss:ExpandedRowCount="9978" x:FullColumns="1" x:FullRows="1">
<Column ss:Index="1" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="2" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="3" ss:AutoFitWidth="0" ss:Width="82"/>
<Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="6" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="7" ss:AutoFitWidth="0" ss:Width="82"/>
<Column ss:Index="8" ss:AutoFitWidth="0" ss:Width="110"/>
<Column ss:Index="9" ss:AutoFitWidth="0" ss:Width="110"/>
<Column ss:Index="10" ss:AutoFitWidth="0" ss:Width="110"/>
<Column ss:Index="11" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="12" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="13" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="14" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="15" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="16" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="17" ss:AutoFitWidth="0" ss:Width="55"/>
<Column ss:Index="18" ss:AutoFitWidth="0" ss:Width="165"/>
<Row>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 1</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 2</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 3</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 4</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 5</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 6</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 7</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 8</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 9</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 10</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 11</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 12</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 13</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 14</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 15</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 16</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 17</Data></Cell>
<Cell ss:StyleID="ColumnHeader"><Data ss:Type="String">Data 18</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">NO</Data></Cell>
<Cell ss:StyleID="DateStyle"><Data ss:Type="DateTime">2013-03-05T07:03:28</Data></Cell>
<Cell><Data ss:Type="String">NO</Data></Cell>
<Cell><Data ss:Type="String">CNG</Data></Cell>
<Cell><Data ss:Type="String">CNG</Data></Cell>
<Cell><Data ss:Type="String">SAD</Data></Cell>
<Cell><Data ss:Type="String">S039</Data></Cell>
<Cell><Data ss:Type="String">CP4-XS</Data></Cell>
<Cell><Data ss:Type="String">3SK263-5-TG-E</Data></Cell>
<Cell><Data ss:Type="String">3SK263-E-5-ASY</Data></Cell>
<Cell><Data ss:Type="String">3SK263-E-5-WDQ</Data></Cell>
<Cell><Data ss:Type="String">NO PNX</Data></Cell>
<Cell ss:StyleID="DateStyle"><Data ss:Type="DateTime">2013-03-09T00:00:00</Data></Cell>
<Cell><Data ss:Type="String">MP</Data></Cell>
<Cell ss:StyleID="NumberStyle"><Data ss:Type="Number">23280</Data></Cell>
<Cell ss:StyleID="NumberStyle"><Data ss:Type="Number">0</Data></Cell>
<Cell ss:StyleID="NumberStyle"><Data ss:Type="Number">468149</Data></Cell>
<Cell><Data ss:Type="String">MASD_NO_ASY_20130306F17405401</Data></Cell>
</Row>
...
</Table>
</Worksheet>
</Workbook>
Any help would be much appreciated..
Thanks,
Jeff.
Edited by: JEFTPHPH on Mar 14, 2013 5:42 PM