Skip to main content

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, new object[] { null, null, null, "TABLE" });

                foreach (DataRow row in datatable.Rows)
                {
                    if (row["TABLE_NAME"].ToString().Trim() != _xlnm#_FilterDatabase")
                    {
                        string Sheet = row["TABLE_NAME"].ToString();

                        OleDbCommand command = new OleDbCommand("SELECT * FROM [" + Sheet + "]", connection);
                        command.CommandType = CommandType.Text;

                        DataTable outputTable = new DataTable(Sheet);
                        outputds.Tables.Add(outputTable);
                        new OleDbDataAdapter(command).Fill(outputTable);
                    }
                }
            }
            return outputds;


        }

 Showing validation messages according to its data type mentioned in .XML files:

 The below code will validate the .XML file column names with Excel file column names and it will check if data type matches or not. It will also check  to see kf the field is nullable or not nullable.

foreach (DataRow xmlitem in dttbXml.Rows)
                    {
                        foreach (DataColumn dxmlcolumn in dttbXml.Columns)
                        {
                            if (dxmlcolumn.ColumnName == "ExcelColName")
                            {
                                foreach (DataColumn dcolumn in datatbValue.Columns)
                                {
                                    if (dcolumn.ColumnName == xmlitem.ItemArray[2].ToString().Trim())
                                    {
                                        int colNumber = 4;
                                        foreach (DataRow drow in datatbValue.Rows)
                                        {
                                            if (xmlitem.ItemArray[3].ToString().Trim() == "varchar2")
                                            {
                                                if (xmlitem.ItemArray[6].ToString().Trim() == "N")
                                                {
                                                    
                                                    
                                                                                                           if (drow[dcolumn.ColumnName].ToString().Trim().Length > 3)
                                                        {
                                                            if (Validation.ValidateCurrency(drow[dcolumn.ColumnName].ToString().Trim()))
                                                                CurrencyError = CurrencyError + xmlitem.ItemArray[0].ToString().Trim() + colNumber + ",";
                                                        }
                                                        else
                                                        {
                                                            if (Validation.ValidateCurrency(drow[dcolumn.ColumnName].ToString().Trim()))
                                                                NullError = NullError + xmlitem.ItemArray[0].ToString().Trim() + colNumber + ",";
                                                        }
                                                    

                                                }

                                            }
                                            else if (xmlitem.ItemArray[3].ToString().Trim() == "number")
                                            {
                                                if (xmlitem.ItemArray[6].ToString().Trim() == "N")
                                                {
                                                    if (drow[dcolumn.ColumnName].ToString().Trim() != string.Empty && drow[dcolumn.ColumnName].ToString().Trim() != "-")
                                                    {
                                                        if (Validation.ValidateNumber(drow[dcolumn.ColumnName].ToString().Trim()))
                                                            InvalidDatatype = InvalidDatatype + xmlitem.ItemArray[0].ToString().Trim() + colNumber + ",";
                                                    }
                                                    else
                                                    {
                                                        if (Validation.ValidateNumber(drow[dcolumn.ColumnName].ToString().Trim()))
                                                            NullError = NullError + xmlitem.ItemArray[0].ToString().Trim() + colNumber + ",";
                                                    }
                                                }

                                                else
                                                {
                                                    if (drow[dcolumn.ColumnName].ToString().Trim() != string.Empty && drow[dcolumn.ColumnName].ToString().Trim() != "-")
                                                    {
                                                        if (Validation.ValidateNumber(drow[dcolumn.ColumnName].ToString().Trim()))
                                                            InvalidDatatype = InvalidDatatype + xmlitem.ItemArray[0].ToString().Trim() + colNumber + ",";
                                                    }
                                                }
                                            }
                                            colNumber++;
                                        }
                                        break;
                                    }

                                }

                            }
                        }
                    }

Validated data to be passed to the Oracle database by generating the same in XML format:

 We need to create a Rowset, and create the Number of rows based on the read data from the Excel file. We also need to pass the table name which needs to be inserted.

                            foreach (DataColumn dc in datatbValue.Columns)
                            {
                                dc.ColumnName = dc.ColumnName.Trim();
                            }

                                                       System.Xml.XmlDocument doctbConfig = new System.Xml.XmlDocument();
                            System.Xml.XmlNode xmltbNode = doctbConfig.CreateNode(System.Xml.XmlNodeType.XmlDeclaration, "", "x");
                            System.Xml.XmlElement roottbElement = doctbConfig.CreateElement("ROWSET");
                            doctbConfig.AppendChild(roottbElement);
                            string ColumnNameFromtbXml = string.Empty;
                            string valueoftbCell = string.Empty;

                            foreach (DataRow drow in datatbValue.Rows)
                            {
                                System.Xml.XmlElement hedder = doctbConfig.CreateElement("ROW");
                                foreach (DataRow dr in dttbXml.Rows)
                                {

                                    doctbConfig.DocumentElement.PrependChild(hedder);
                                    doctbConfig.ChildNodes.Item(0).AppendChild(hedder);

                                    if (dr["Name"].ToString() != string.Empty)
                                        ColumnNameFromtbXml = dr["Name"].ToString().Trim();

 valueoftbCell = drow[dr["ExcelColName"].ToString()].ToString().Trim();

  System.Xml.XmlElement installationElement = doctbConfig.CreateElement(ColumnNameFromtbXml);
                                    System.Xml.XmlText installationIdText = doctbConfig.CreateTextNode(Convert.ToString(valueoftbCell));
                                    installationElement.AppendChild(installationIdText);
                                    hedder.PrependChild(installationElement);
                                }
                          
                            }

                            doctbConfig.Save(Server.MapPath("~/TBalance.XML"));
                            DataSet dstbread = new DataSet();
                            DataTable dttbreadxml = new DataTable();
                            System.IO.StringWriter swStringWriter = new System.IO.StringWriter();
                            dstbread.ReadXml(Server.MapPath("~/TBalance.XML"));
                            dttbreadxml = dstbread.Tables[0];
                            dttbreadxml.WriteXml(swStringWriter);
                            tbxml = swStringWriter.ToString().Trim();
                            System.IO.StringWriter swStringWritervalue = new System.IO.StringWriter();
                            string TableName = "PS_JWX_FIT_TB_CHL";
                            DataTable dtableofrows = new DataTable();
                            DataTable dtxmlcost = new DataTable();



 for (int i = 0; i < RowsofTBdtable; )
                                {
                                    DataTable dtableTBofrows = new DataTable();
                                    DataSet datasettb = new DataSet();
                                    System.IO.StringWriter swStringWriterTBvalue = new System.IO.StringWriter();
                                    string xmlTBvalue = string.Empty;

                                    dtableTBofrows = dttbreadxml.Clone();
                                    Int32 RowValue = Convert.ToInt32(ConfigurationManager.AppSettings["CountOfRecords"].ToString());

                                    for (int j = 0; j < RowValue; j++)
                                    {
                                        if (i <= (RowsofTBdtable - 1))
                                        {
                                            dtableTBofrows.ImportRow(dttbreadxml.Rows[i]);
                                            i++;
                                        }
                                    }

datasettb.Tables.Add(dtableTBofrows);
                                    datasettb.DataSetName = "ROWSET";
                                    dtableTBofrows.TableName = "ROW";
                                    dtableTBofrows.WriteXml(swStringWriterTBvalue);
                                    xmlTBvalue = swStringWriterTBvalue.ToString().Trim();

 if (xmlTBvalue.Trim() != string.Empty)
                                    {
 try
                                        {
                                       
                                            if (objbl.Inserttbxml(xmlTBvalue, TableName))
                                            {
                                                lblErrorMsg.Text = "Successfull";
                                                tblerror.Visible = false;                                              
                                                dtableTBofrows.Rows.Clear();
                                                dtableTBofrows.AcceptChanges();
                                                datasettb.Clear();
                                                datasettb.AcceptChanges();
                                                swStringWriterTBvalue = null;
                                            }
 }
catch (Exception ex)
                                        {
                                            objbl.DeleteRowsForTrailBalance(TBPeriod, TableName);                                           
                                            tblerror.Visible = false;
                                            throw ex;
                                        }
}

 Oracle stored procedure to insert the data into the table:

 In the below stored procedure we use DBMS_XMLSave.newContext to get the context handle and DBMS_XMLSave.insertXML to insert the XML data into table on given table name. And DBMS_XMLSave.closeContext to close the handle.

PROCEDURE INSERTTBXML(xmltbDoc IN CLOB, tableName IN
VARCHAR) is  
insCtx DBMS_XMLSave.ctxType;

rows number;  

BEGIN
 begin
 insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle

rows := DBMS_XMLSave.insertXML(insCtx,xmltbDoc); -- this inserts 

DBMS_XMLSave.closeContext(insCtx); -- this closes the handle
 EXCEPTION 
        WHEN OTHERS 
        THEN dbms_output.put_line(SQLCODE);
        
        end;
 
END INSERTTBXML;

 Delete rows if the transaction fails:

In the following code it will delete all the rows if any transaction fails.

create or replace

PROCEDURE SP_DELETEROWSFORTrailBalance(MonthYear IN varchar2, DivisionName IN
VARCHAR) is
l_count number;
l_str varchar2(1000);
type c is ref cursor;
cur c;

BEGIN  
l_str := 'select count(*) from '||DivisionName||' where J_GL_PERIOD='||MonthYear||''; 
open cur for l_str;
fetch cur into l_count;
close cur;
dbms_output.put_line(l_count);
if(l_count > 0) then

 l_str := 'delete from '||DivisionName||' where J_GL_PERIOD='||MonthYear||''; 
 execute immediate l_str;
end if;
 
END SP_DELETEROWSFORTrailBalance;

 

 

G.M. Nagarjuna

G.M. Nagarjuna

Nagarjuna works as Senior Software Engineer with Trigent Software. He has nearly six years of experience and has worked extensively on .Net platform and .Net technologies.