Hi, Has any one experienced problems, when importing data from Excel, where the cell contains a value of greater than 1,024 characters? This can not be handled by Navision and causes the ReadSheet function (from the “Excel Buffer” table) to break. I have amended the “ReadSheet” function to allow for this. My “ReadSheet” function now looks like this: ReadSheet() Window.OPEN( Text007 + '@1@@@@@@@@@@@@@@@@@@@@@@@@\'); Window.UPDATE(1,0); DELETEALL; XlRange := XlWorkSheet.Range(GetExcelReference(5)).SpecialCells(11); Maxi := XlRange.Row; Maxj := XlRange.Column; i := 1; XlWorkSheet.Range('IV2').Formula := '=LEN(INDIRECT(IV1))'; XlWorkSheet.Range('IV3').Formula := '=LEFT(INDIRECT(IV1),1021)&"..."'; REPEAT j := 1; VALIDATE("Row No.",i); REPEAT VALIDATE("Column No.",j); XlWorkSheet.Range('IV1').Value := (xlColID + xlRowID); CellLen := XlWorkSheet.Range('IV2').Value; IF CellLen > 1024 THEN BEGIN MESSAGE((xlColID + xlRowID) + ': Cell contents too long for Navision.\' + 'This cell value has been truncated to 1,024 characters.'); CellVal1 := XlWorkSheet.Range('IV3').Value; END ELSE CellVal1 := FORMAT(XlWorkSheet.Range(xlColID + xlRowID).Value); CellVal1 := DELCHR(CellVal1,'<>',' '); Truncated := FALSE; IF STRLEN(CellVal1) > 250 THEN BEGIN CellVal2 := PADSTR(CellVal1,247) + '...'; Truncated := TRUE; END ELSE CellVal2 := CellVal1; "Cell Value as Text" := CellVal2; "Full Value Part1" := COPYSTR(CellVal1,1,250); "Full Value Part2" := COPYSTR(CellVal1,251,250); "Full Value Part3" := COPYSTR(CellVal1,501,250); "Full Value Part4" := COPYSTR(CellVal1,751,250); "Full Value Part5" := COPYSTR(CellVal1,1001,24); IF "Cell Value as Text" <> '' THEN BEGIN INSERT; END; j := j + 1; UNTIL j > Maxj; i := i + 1; Window.UPDATE(1,ROUND(i / Maxi * 10000,1)); UNTIL i > Maxi; XlWorkBook.Close(FALSE); XlApplication.Quit; CLEAR(XlApplication); Window.CLOSE; This modification assumes that the last column in the spreadsheet (“IV”) will never be used. I am pretty confident that this is true. It then adds values and formulas into this column to help check the length of cells before attempting to read the value into a Text1024 variable. Where there are more than 1,024 characters, the text is truncated. As you will also notice, I have added some extra fields to the “Excel Buffer” table to allow long strings to be kept. This all seems to work fine. However, the part of the code where the formulas and values are written to the worksheet seem to slow the whole process down considerably. Where before it was taking approx 10 to15 minutes to read just over 5,000 records from an Excel worksheet, it now takes nearly 40 minutes after adding these lines. I have checked, and the XLS file does NOT contain large amounts of formulas that would cause a wait for recalc. My question is, how could I possibly speed this up a bit, but keeping the required string checking/ truncating as described? Many Thanks, Simon
Did you try to remove the formula from column IV after you have finished a line ?
quote:
Did you try to remove the formula from column IV after you have finished a line ?
Originally posted by tb@softsys.at - 2006 Jan 13 : 08:47:15
Presumably you mean to add, use then remove the formulas for each cell that is processed? Is this really likely to speed it up? I shall give it a try anyway, and will let you know. Thanks
OK, I changed the function so that as it looped through each cell it: 1) Wrote the cell reference to IV1 2) Wrote the LEN formula to IV2 3) Read the value of IV2 to a variable 4) Cleared the cell IV2 Then, when assigning the cell value to a variable, IF the LEN > 1024 it did the following: 5) Wrote the TRUNC formula to IV3 6) Read the value of IV3 to a variable 7) Cleared the cell IV3 The worksheet then only contained the formulas wjile they were needed. They were then deleted as soon as they had been used. When I tried running the import I did not let it finish, as it was quite noticably slower. I would guess that it would have taken at least an hour to complete. I then changed the function again so that it only wrote the LEN formula once and left it there. I kept the function so that the TRUNC formula was written when it was needed and removed immediately after. I did this because it is very rare that a cell would be > 1024 chars, but it still needs to be captured when it happens. Upon running the import again, it took over 30 minutes again. So not much different to before making these changes. From this, I would guess that the increase in running time is nothing to do with Excel recalculating the formulas. It would appear that it is the actual writing of values to Excel that is causing the slowdown. Based on this, I will change the function back to how it was at the time of posting this question. Would you agree, in this case, that there is probably nothing I could do to speed up the process? Thanks, Simon
I’am not familiar with the Excel Buffer and I haven’t studied your problem and the given suggestions, but maybe you can use the following information to your benefit. Navision can get very slow when dealing with large amounts of data. You can see that for example when you copy a big table to paste it Excel by hand. The copy action can take “forever” to complete. The time needed seems to grow exponentially with te grow of data to transfer. Maybe this is an isue with the Excel Buffer too? Have you tried to use a dataport? This always solved the mentioned speed problem for me.
Another one: You could try to copy/paste : Write the formulas into the first line only, then use copy/paste to paste the value into all other lines in one shot. There must be commands for that. This would then run in Excel only and would not cause cell transactions between Nav and Excel.
Hi, Thanks for the replies. However: 1) A dataport is not possible as it is importing data from Excel workbooks. Hence I am using the Excel Buffer. 2) I am not pasting formulas for each row of data. All I am pasting is two formulas into 2 cells (IV2 & IV3). Then, on each run of the loop, I paste the cell reference as text. The first formula uses this to calculate the length of the cell ("=LEN(INDIRECT(location of cell reference))"). The second formula truncates that cell value to 1024 characters, again using the INDIRECT function against the text cell reference. To paste two formulas for each cell would not be feasible.