Hi Johnny,

Great control. But when  I created the SmartObject , The only method loaded is the 

Excel Import Service Broker . How do iGet the other methods like GETLIST and UPDATE.

Anticipating your kind response.

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

This component wouldbe very useful for us.

However, i am facing the below error, any pointers?

Thank you Mr. Fang for this great tool. I am facing an error whenever I test a spreadsheet through SmartObjects Services Tester:

Error message: The given value of type String from the data source cannot be converted to type bit of the specified target column.  Before failure, 1 rows found. Columns found: processid,city....etc . 

I am not using "bit" type for any field. Even the type "bit" is not exist in K2 !
Anyone could help me with this issue.

Thank you.

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.

I have loaded the tool but I kep on getting the same issue where it states that

" Exception has been thown by the target of an inocation. Input stting was not in a correct format. 

Service:Sourcecode.SmartObject.Services.excellImport

I have checked the samrtobject created in designer and it is exacly the same as the same in the doc provided with the tool. 

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. 

A while ago I downloaded the 1.0.3 version of the Excel Import Service. (Thanks for adding sorely missing date support, Mr. Higgs!) One issue I noticed: since dates are now handled sort of separately, they do not benefit from blank-handling. I have made a very small change to allow a date column to also have blanks without causing exception. Alas, I have not had a chance to perform full testing, and I may not get a chance soon. @Neville1: if you have some blanks in your Date column that just might be the problem. Below is a paste of the very small diff for my fix. If it works for you, please let me know so that I can hopefully submit the changes for publishing.

293a294
>                                     int nonblankCharCount = dr sColName].ToString().Length;  //It is plausible that there will valid cases of null/empty cells
296c297
<                                     if (newSmartObject.PropertiesisColName].Type == SOC.PropertyType.DateTime) // DateTime column
---
>                                     if (nonblankCharCount > 0 && newSmartObject.PropertiesisColName].Type == SOC.PropertyType.DateTime) // DateTime column
301c302
<                                     else if (newSmartObject.PropertiesisColName].Type == SOC.PropertyType.Date) // Date column
---
>                                     else if (nonblankCharCount > 0 && newSmartObject.PropertiesisColName].Type == SOC.PropertyType.Date) // Date column
306c307
<                                     else if (newSmartObject.PropertiesisColName].Type == SOC.PropertyType.Time) // Time column
---
>                                     else if (nonblankCharCount > 0 && newSmartObject.PropertiesisColName].Type == SOC.PropertyType.Time) // Time column

 - Justin

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.PropertiesrsColName].Type == SOC.PropertyType.DateTime) // DateTime column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(drlsColName].ToString()));
newSmartObject.PropertiesrsColName].Value = String.Concat(tmpDate.ToShortDateString(), " ", tmpDate.ToShortTimeString());
}
else if (newSmartObject.PropertiesrsColName].Type == SOC.PropertyType.DateTime) // Date column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(drlsColName].ToString()));
newSmartObject.PropertiesrsColName].Value = tmpDate.ToShortDateString();
}
else if (newSmartObject.PropertiesrsColName].Type == SOC.PropertyType.DateTime) // Time column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(drlsColName].ToString()));
newSmartObject.PropertiesrsColName].Value = tmpDate.ToShortTimeString();
}
else // not a Date or DateTime column
{
newSmartObject.PropertiesrsColName].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 

I think that the change you have made will not fix it, and will instead introduce a new bug. Time, Date, and DateTime are all different (although obviously related) data types. Sometimes in programming and certainly in Excel UI, there are some automatic conversion abilities. But here, we must handle all thrree separately, which is what the if-elseif-else statement block does. The very first if statement handles the DateTime data type.

The Excel sheet's cell data type must match the destination table column, I do not believe there is any "intelliigent conversion" programmed into this loader.

I am not sure why you had the build problem. I do not think I had any issue building the 1.0.3 source. 

I am not sure about your problem with ID numbers. Check the destination column type and special properties. If you mean it is a MSSQL server ID column type for instance, perhaps you are not allowed to set that anyway. In which case you would have to maintain a totally separate, additional ID column for loaded values.

Keep experimenting, and good luck.

- Justin

Hi Johnny!

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

Thanks

Dave

I have gotten this to work correctly with MS SQL but it seems to have issues when working with Oracle. In versions of Oracle prior to 12c, you have to use a stored procedure to surface the CRUD functionality (auto ID) properly. It appears that using this method doesnt allow the service broker to match the columns in the Smartobject with those in the Excel spreadsheet. Is there a way around 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

Worked great!

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). "

Also, the code isn't able to handle decimal precisions greater than 2.  The SQL column is set up as Decimal(16,6) but an error is displayed when attempting to bring this value in.

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.

Hi, 

Can you please provide me the Link to Download this Service Broker to Import the Excel in K2?

My deadline is coming closer, I appriciate your link.

Thank You,

Krupesh Patel

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."

So far it is still working for us in 5.1. I would love to see it incorporated into the newest version but so far, it works fine.

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

I had it working in 4.7 and when I upgraded to 5.1 it continued to work. I didnt have to make any changes.

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?

Hi All, 

I'm using Excel Import Service on the K2 management site, does anyone know how to use this service and how? please see the error when trying to execute the SMO or is there something I'm missing?

Thanks