Uploading Bulk File Data Dynamically Into Oracle Database

A recent project, required that Excel file data be inserted dynamically into a Oracle database and I identified the following steps that need to be undertaken to do the same:

• Generate Excel file data into XML format.
• Validate each column value from the Excel file with given .xml file of Column Cell, data type,and Column Name.
• Import content from the Excel file.
• Generate Excel file data into xml format.
• Insert the generated XML data into an Oracle database.

The Pre-defined template of .XML file for validation is contained like this:

This file contains the Excel file shell, Column Name of table, Column Name of Excel file, Data type, Is Primary and Null able field.

<Validations>
 <Validation Category="Sales_Standard_Cost_Adjustments">
 <Sheet SheetName="Adjustments">
 <Columns>
 <Column Shell="A" Name="J_ENTITY_SRCCD" ExcelColName="Entity" Datatype="varchar2" Size="64" IsPrimary="True" Nullable="N" />
 <Column Shell="B" Name="J_ITEM_NUMBER" ExcelColName="Item #" Datatype="varchar2" Size="64" IsPrimary="True" Nullable="N" />
 <Column Shell="C" Name="J_CURR_SRCCD" ExcelColName="Currency" Datatype="varchar2" Size="3" Nullable="N" />
 <Column Shell="D" Name="J_ADJ_QTY" ExcelColName="Unit Adjustments (Eaches)" Datatype="number" Size="38,10" />
 <Column Shell="E" Name="J_SA_RETURNS" ExcelColName="Returns" Datatype="number" Size="38,10" />
 <Column Shell="F" Name="J_SA_GPO_FEES" ExcelColName="GPO Fees" Datatype="number" Size="38,10" />
 </Columns>
 </Sheet>
 </Validation>
 </Validations>

 Import the content from the Excel file:

The below code will read the Excel file data and insert into data table using provider of Microsoft.ACE.OLEDB.12.0.

public static DataSet ImportExcelXLSX(HttpPostedFile file, bool hasHeaders)
 {
 string fileName = Path.GetTempFileName();
 file.SaveAs(fileName);

return ImportExcelXLSXContent(fileName, hasHeaders);
}

public static DataSet ImportExcelXLSXContent(string FileName, bool hasHeaders)
{
string Header = hasHeaders ? “Yes” : “No”;

string strConnection = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + FileName + “;Extended Properties=”Excel 12.0 Xml;HDR=”” + Header + “”;IMEX=1″”””;

DataSet outputds = new DataSet();

using (OleDbConnection connection = new OleDbConnection(strConnection))
{
connection.Open();

DataTable datatable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables