Upload data from an Excel spreadsheet to Siebel using the Siebel Adapter
If you have data stored in a Excel file and need to upload that to Siebel, this blog illustrates how go about doing that using the Siebel adapter. I will be using the “Account” business component and will show how to create insertion records.
Let’s take a sample Excel spreadsheet that has a bunch of fields that cover the various .Net types supported by the Siebel adapter.
1. Competitor – bool
2. Employees – long
3. Name – string
4. Profit – decimal
5. Reference Date – DateTime (UTC)
6. Start Date – DateTime (plain date)
7. Strategy – static bounded picklist
I will use an OleDbDataReader to read the data from the Excel spreadsheet and create the insertion records. The code below is self explanatory.
/// <summary>
/// Creates the insertion records by reading thru the excel spreadsheet
/// </summary>
/// <returns></returns>
private static AccountInsertRecord[] CreateInsertRecords()
{
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\junk\\ExcelToSiebel\\Account.xls;Extended Properties=Excel 8.0;";
OleDbConnection connection = null;
OleDbCommand command = null;
OleDbDataReader reader = null;
try
{
/// Connect to the excel file
connection = new OleDbConnection(connectionString);
connection.Open();
/// Start reading from the file & creating the insert records
command = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
reader = command.ExecuteReader();
List<AccountInsertRecord> insertRecList =
new List<AccountInsertRecord>();
while (reader.Read())
{
AccountInsertRecord insertRec = new AccountInsertRecord();
insertRec.Competitor = GetBool(reader, 0);
insertRec.Employees = GetLong(reader, 1);
insertRec.Name = GetString(reader, 2);
insertRec.Profit = GetDecimal(reader, 3);
insertRec.ReferenceDate = GetUtcDateTime(reader, 4);
insertRec.StartDate = GetDateTime(reader, 5);
insertRec.Strategy = GetStrategy(reader, 6);
Console.WriteLine("-------------------------------------------");
Console.WriteLine(" Competitor - " + insertRec.Competitor);
Console.WriteLine(" Employees - " + insertRec.Employees);
Console.WriteLine(" Name - " + insertRec.Name);
Console.WriteLine(" Profit - " + insertRec.Profit);
Console.WriteLine(" Reference Date - " + insertRec.ReferenceDate);
Console.WriteLine(" Start Date - " + insertRec.StartDate);
Console.WriteLine(" Strategy - " + insertRec.Strategy);
insertRecList.Add(insertRec);
}
/// Create the array of insertion records
AccountInsertRecord[] insertRecs =
new AccountInsertRecord[insertRecList.Count];
int index = 0;
foreach (AccountInsertRecord insertRec in insertRecList)
{
insertRecs[index++] = insertRec;
}
return insertRecs;
}
catch (Exception e)
{
Console.WriteLine("ERROR: " + e.ToString());
return null;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
The above code calls on a bunch of helper functions to do the value conversions to appropriate data types. There are a couple of ways to write these helper functions. They can read the raw value and then use standard functions like XmlConvert to actually do the conversion. Or one can set the “Category” on the columns in the Excel file and then use the OleDbReader’s get functions to retrieve the value as a specified type (GetBoolean, GetInt32, …). The helper functions in the code try to cover these different options.
/// <summary>
/// Gets a boolean value from the specific column of the Excel
/// spreadsheet.
/// </summary>
/// <param name="reader">Reader for the spreadsheet</param>
/// <param name="ordinal">Zero based column ordinal</param>
/// <returns></returns>
private static System.Nullable<bool>
GetBool(
OleDbDataReader reader,
int ordinal
)
{
try
{
return reader.GetBoolean(ordinal);
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// Gets a string value from the specific column of the Excel
/// spreadsheet.
/// </summary>
/// <param name="reader">Reader for the spreadsheet</param>
/// <param name="ordinal">Zero based column ordinal</param>
/// <returns></returns>
private static string
GetString(
OleDbDataReader reader,
int ordinal
)
{
try
{
return reader.GetString(ordinal);
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// Gets a decimal value from the specific column of the Excel
/// spreadsheet.
/// NOTE Assumes that the excel column "Category" has been set to
/// "Number"
/// </summary>
/// <param name="reader">Reader for the spreadsheet</param>
/// <param name="ordinal">Zero based column ordinal</param>
/// <returns></returns>
private static System.Nullable<decimal>
GetDecimal(
OleDbDataReader reader,
int ordinal
)
{
try
{
return (decimal)reader.GetDouble(ordinal);
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// Gets a long value from the specific column of the Excel
/// spreadsheet.
/// NOTE Assumes that the excel column "Category" has been set to
/// "Number", with "Decimal places" set to 0
/// </summary>
/// <param name="reader">Reader for the spreadsheet</param>
/// <param name="ordinal">Zero based column ordinal</param>
/// <returns></returns>
private static System.Nullable<long>
GetLong(
OleDbDataReader reader,
int ordinal
)
{
try
{
return XmlConvert.ToInt64(reader.GetValue(ordinal).ToString());
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// Gets a date/date-time value from the specific column of the Excel
/// spreadsheet.
/// </summary>
/// <param name="reader">Reader for the spreadsheet</param>
/// <param name="ordinal">Zero based column ordinal</param>
/// <returns></returns>
private static System.Nullable<DateTime>
GetDateTime(
OleDbDataReader reader,
int ordinal
)
{
try
{
string value = reader.GetValue(ordinal).ToString();
return DateTime.Parse(value);
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// Gets a UTC date time value from the specific column of the Excel
/// spreadsheet.
/// </summary>
/// <param name="reader">Reader for the spreadsheet</param>
/// <param name="ordinal">Zero based column ordinal</param>
/// <returns></returns>
private static System.Nullable<DateTime>
GetUtcDateTime(
OleDbDataReader reader,
int ordinal
)
{
try
{
string value = reader.GetValue(ordinal).ToString();
DateTime dateTime = DateTime.Parse(value);
return DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
}
catch (Exception)
{
return null;
}
}
/// <summary>
/// Get the Strategy from the specific column of the Excel
/// spreadsheet.
/// </summary>
/// <param name="reader"></param>
/// <param name="ordinal"></param>
/// <returns></returns>
private static System.Nullable<ESPBSUStrategyPickListOptionalPickListType>
GetStrategy(
OleDbDataReader reader,
int ordinal
)
{
try
{
string value = reader.GetValue(ordinal).ToString();
if (value ==
ESPBSUStrategyPickListOptionalPickListType.Create.ToString())
{
return ESPBSUStrategyPickListOptionalPickListType.Create;
}
else if (value ==
ESPBSUStrategyPickListOptionalPickListType.Exit.ToString())
{
return ESPBSUStrategyPickListOptionalPickListType.Exit;
}
else if (value ==
ESPBSUStrategyPickListOptionalPickListType.Expand.ToString())
{
return ESPBSUStrategyPickListOptionalPickListType.Expand;
}
else if (value ==
ESPBSUStrategyPickListOptionalPickListType.Protect.ToString())
{
return ESPBSUStrategyPickListOptionalPickListType.Protect;
}
else if (value ==
ESPBSUStrategyPickListOptionalPickListType.Pursue.ToString())
{
return ESPBSUStrategyPickListOptionalPickListType.Pursue;
}
}
catch (Exception)
{
}
return null;
}