Excel Import Service Broker

Mr. Fang -

  Thanks very much for sharing this really useful Excel Import Service. I discovered a couple of attributes that were problems for me:

• it does not deal well with empty cells (Excel efficiently does not store anything for an empty cell and just jumps to the next cell reference that does contain a value) which results in data appearing to become "offset". In my case, this also resulted in the databroker throwing an exception, reporting "Received an invalid column length from the bcp client for colid" when it tries to store a value to big for the destination column. Instead, it should insert blanks into the the data table when a cell is empty
• Also, in some cases, I think having the option to remove space characters from the header row's column name values (instead of replacing with underscores) is very useful. I think having one more optional property to act as a control parameter between these two options would be a good way to provide this.

I hope you will accept a patch that I have created implementing the aforementioned feature changes. I am not sure how best to send it to you. The diff is about 240 lines, so it might be a bit much to just paste here.

For other users out there, a couple of other quick usage notes that I did not see mentioned in the documentation:

• File must be in XLSX format, old XLS will be interpreted as "corrupt".
• Column headernames should be the very first row, don't have "general header" or "metadata" in the first row(s). (Maybe instead use a cell comment for this if you really need it).
• Some of the possible exceptions will not come back with full details, just a general SmartObjectException; you will need to inspect the databroker messages (I did this with Visual Studio, perhaps there are other tools or logs)

- Justin Warwick

Figured out the problem, whenever I pass the excel file it jumps some fields and put the data into another fields. So what happened is it took a text (String) field and put it into Yes/No (bit) field which causes the error. Below is the excel file that I am dealing with:

As you can see it skiped Region field and put the Branch data into X_Coordinate field and so on.. !

Anyone can help me please.

Seem is has to do with the collumn called Request which has the dates. I have checked the format off the smartbobject file created in designer and it is the same a the example. Also the excel file properties off the date cells are formated on the date and the headign name on general. 

I need to make sure this is workign before I start my actual fiel that will have multiple collumn names and data in it. 

Hi Justing

Just some more info on the error. I saw on the documentation tha the date is executed in the the smart Object tester adn splits the he date and en exectuing it. Is there a wasy that the dsate can just be imported without the seperation. Meaning it just imports it as it is shows being yyyy-mm-dd hh.mm.ss

I am still very new to programming and K2 so excuse me if i sound stupid

I had to make a change on the the source code as well and not sure if this is the issue as before it did not want to load the changes were the names maker in red bold now where the first one was only Date and second one only Time. Visual studio gave and error on rebuild before and the service object loaded. So I made then DateTime. This is in the file ExcellImportService.cs

// for handling date and datetime types
DateTime tmpDate;

// handle Date amd DateTime columns correctly. Convert Excel datetime format (double) to .NET DateTime type
if (newSmartObject.Properties[sColName].Type == SOC.PropertyType.DateTime) // DateTime column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(dr[sColName].ToString()));
newSmartObject.Properties[sColName].Value = String.Concat(tmpDate.ToShortDateString(), " ", tmpDate.ToShortTimeString());
}
else if (newSmartObject.Properties[sColName].Type == SOC.PropertyType.DateTime) // Date column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(dr[sColName].ToString()));
newSmartObject.Properties[sColName].Value = tmpDate.ToShortDateString();
}
else if (newSmartObject.Properties[sColName].Type == SOC.PropertyType.DateTime) // Time column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(dr[sColName].ToString()));
newSmartObject.Properties[sColName].Value = tmpDate.ToShortTimeString();
}
else // not a Date or DateTime column
{
newSmartObject.Properties[sColName].Value = dr[sColName].ToString();

How should the code be written to import he date and time in the same column. As the rest off our project is build that way. 

I also picked up that if I try and pull a column in tha is a Id number example 7400534584084 it does nto import it, and just gives it a null value even if the excel columis formated to text, or number or general and the same on the smart object created on k2 designer 

the code given above where would one add this and what woudl this then give 

Hi Johnny!

I would like to add a system date/time to the broker. What is the easiest way to accomplish this?

Thanks

Dave

Hi Colleagues,

Is there an option to specify, which Excelsheet to upload?

Can we also specify the range to get uploaded?

Thanks for any info on the matter!

Best regards,

Konstinti

I've tested this a bit and found that the import can't handle greater than the AA column.  The broker allows for it, but the data is dropped into the wrong columns in the database.

 The comments in the source code says it should be able to handle A-Z and AA-ZZ.

"   /// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ). "

Hi Johnny,

Thanks for the Excel Import Service Broker. It does work great.

Just the suggestion. I looked into the current code and I saw that you used the "ExecuteBulkScalar" method and surround with try catch

try
{

smoClientServer.ExecuteBulkScalar(soImport, inputList);
}
catch (SmartObjects.Client.SmartObjectException ex)
{
foreach (SmartObjects.Client.SmartObjectExceptionData smOExBrokerData in ex.BrokerData)
{
this.Results = smOExBrokerData.Message + " Before failure, " + this.Results;
}
return this;
}

However, the problem I found is that if I imported 5 rows of excel data and the 3rd row data contain incorrect format. The "ExecuteBulkScalar" will return error and all of the remaining 4 records were not added. Should we consider to force the execute "add" method in foreach item and then we store the row ID for the error. So we can still import the correct data and output the row that has problem. (I knew that it's not performance wise but from the current knowledge, it seems like the ExecuteBulkScalar does not serve this requirements)

What do you think?

Best Regards,

Amornthep C.

Is this compatible with K2 Five (5.1)?  Secondly, when trying to use it, I get the following error:  "Could not load file or assembly 'EPPlus, Version=4.0.5.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1' or one of its dependencies. The system cannot find the file specified."

FrozenForms - Did you have to do something with the EPPlus dll file for it to work?

We are using this and have couple of questions regarding the data import. We have few columns in Excel file which as special characters such as "/", "&" and of course date field. When I tried using this in my application it brings NULL records for all those fields. 

Any suggestions or work around for these fields?