Import from Excel hitting blank field

Business Central/NAV Developers Forum
1

Hello all. I am importing fields from an Excel worsheet into a temporary item table using a report (not a dataport as I need to report too). e.g. TmpItem.“Transfer Cost” := XLWorksheet.Range(ColumnID+FORMAT(RowNo)).Value; The problem I have is that the field in the worksheet can be blank. When this happens I get “The expression Text cannot be type-converted to a Decimal value.”. I would like to be able to check the cell first, and if it is blank then simply set TmpItem.“Transfer Cost” to zero. Can anyone tell me how I would check the cell? The obvious does not work i.e. IF XLWorksheet.Range(ColumnID+FORMAT(RowNo)).Value = 0 THEN… Thanks in advance. Craig.

2

Further to my previous post, I have also tried this, where TransferCostCode = code 20 Text001 = %1 is blank in %2%3 (please ignore the underscores…my indents seem to drop off in this forum) TransferCostCode := XLWorksheet.Range(ColumnID+FORMAT(RowNo)).Value; IF TransferCostCode = ‘’ THEN BEGIN __CloseExcel; __ERROR(Text001,‘Transfer Cost’,StandardCostColumn,RowNo); END ELSE __TmpItem.“Transfer Cost” := XLWorksheet.Range(ColumnID+FORMAT(RowNo)).Value; So I thought I would put the XL value into a code field first, check to see if it was blank. If it were I would advise the user, if not then it would carry on. However I have found that I have the same problem only reversed. If there IS a decimal value in the XL cell, I can not bring it through into a code (or text) field. Rgds C

3

Table 370 - Excel Buffer is full of tricks that deal with the messy stuff… Using hints from the ‘ReadSheet’ procedure, you could handle your case roughly like this:

txtCellValue := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' '); IF txtCellValue <> '' THEN IF EVALUATE(myDecimal,txtCellValue) THEN ....

4

Hey, thats excellent Fritz. Seems to work a charm. Thankyou and thanks to the forum. Cheers Craig NZ

5

I think there’s another solution. I don’t have tested it. Declare Variant var VarValue := XLWorksheet.Range(ColumnID+FORMAT(RowNo)).Value; IF VarValue.ISDECIMAL THEN Store to decimal field Best regards Patrick