Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Loading SpreadsheetML to Oracle Database`

JEFTPHPHMar 14 2013 — edited Apr 15 2013
Hi! 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
This post has been answered by odie_63 on Mar 21 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 13 2013
Added on Mar 14 2013
26 comments
2,778 views