Navision - Importing Data from Excel

[SHardy]

Version 3.70.B

When importing data from an Excel file, is there a way to get Excel to return the character length of the cell value before trying to assign the value to a variable?

The problem that I have is that if a cell contains more than 1024 characters then it will bomb out with out completing the rest of the data and without informing the user of the cell address causing the problem.

I can handle all other issues if only I could first test the length of the cell value.

Any help greatly appreciated.

Thanks,
Simon

 

[tomed]

Have you tried using the len function in Excel before you import the file?

=len(cell in question)

You could even nest that in an If statement and have it display an error if the length exceeds 1024.

 

[SHardy]

Hi,

I was hoping for a solution that did not require any changes to the Excel workbook.

What I need is a way, from within Navision, to test for a condition in Excel. In this instance I need to test for the cell length before trying to add the value to a Navision variable.

 

[SHardy]

OK, I will summarise what I have done. If you think that there is a better way of doing this, then please let me know.

1) I didn't want to modify the Excel workbook. However, I don't believe I have any choice. I have therefore added two very small functions - the first one returns the length of a given cell, the second returns the cell value truncated to 1024 characters. I have exported this module to a *.bas file to simplifiy adding it to any necessary workbooks.

2) I have added extra fields to the "Excel Buffer" table to store long strings, but split across these 5 fields. I have also included a flag to indicate a long string, but did not find this necessary.

3) Rather than modifying the standard "ReadSheet" function, I have added a duplicate function that I have then modified to use these functions to truncate any value > 1024 characters.

This seems to work fine now. The only thing that will cause it to break with an error is if the VBA module is not present in the workbook. As long as the user is aware of this, then they can add the module and re-run.

As I said, this is not how I had envisaged having to go about this. So I would still like to hear if there are any better methods.


For information:

I am trying to find out how else these workbooks (and their data) are used, with the possibility of having ALL of the data input & held on Navision. Makes a bit more sense. If any further analysis is required, then the appropriate data can always be exported if required.

 

[SHardy]

Hmmmm. I have been able to make a modification that gets rid of the need for the Excel functions to be added. This means that there are no changes at all required to the Excel workbook. It also means that it would not cause any problems when any other function calls the ExcelBuffer.ReadSheet function.

I have made the assumption that the last column ("IV") of the spreadsheet isn't used. I am sure that this will always be the case. I have then added the following into the readsheet function:


:
:
:
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);
:
:
:



This seems to work fine now, but it does seem to slow down the "ReadSheet" function quite drastically. Is there any way for me to jig this about so that it does not go so slow???

Thanks