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

JedrzejT

Hi

In dataport fields you must enter variable instead field name.

OnAfterImortRecord

IF STRLEN(variable)<250 THEN
"Field name":=FORMAT(variable)

That's all
Regards

JedrzejT

variable must be bigtext type

SHardy

Hi,

I understand that it has to first be read into a variable. As far as I am aware BigText is not available in v3.70.

I am currently reading the cell value into a text1024 variable. This is then (currently) truncated to 250 characters and then inserted into the table's text field.

However, if a cell contains more than 1024 characters then an error occurs that I am currently unable to handle. This is a bit of a problem. It would be less so if I could return the problem cell reference as a message (and possibly continue to process the file, skipping this cell).

Please see original post for ful details.

vgvozdev

hi,

You can create macro in excel that will return Lenght the field and then run this macro from Navision.

Valentin

SHardy

Thanks. I was hoping not to have to make any changes to their Excel file(s), but I think that this may be the only option.

How would I call the Excel macro? Would it be something like:

xlApp.MacroName(Parameter as XLRef);

Thanks,
Simon

sgg

Have you tried the following:

1. Add a new column to the excel File

2. Add the fomular [ =LEN(A1) ] in the new column to return the length of the Cell A1 contents (for example) then you may Use Data >> Filter >> AutoFilter menu to filter out those with Length >= 1024. Next you copy the fomular to the same Column in all the other rows with the data.

3. Do whatever you want to do with these ones
4. Remove your Formular
5. Save the new file
6. Import into Excel

SHardy

I have been trying to avoid making any changes in the Excel file. I was really after a method of dealing with this scenario within Navision. Is this not possible?

I did think that adding the macro to the Excel file may be a viable solution, as it would have absolutely minimal effect on the workbook. However, having thought about it some more it is not such a great idea because...

The file(s) in question have code in them that, upon opening, displays a user form and hides the workbook. This caused problems when accessing via Navision's Excel automation, as it required user intervention. I got around this by disabling macros in the code behind the "Excel Buffer" table (370).

Just to reiterate then, I understand that there are some quite easy ways of achieving this by making changes to the Excel workbook(s), but I wanted to avoid this. My question is: Can I deal with this issue from within Navision?

Many thanks,
Simon

sgg

Are you Importing into navision Using Excel automation or are you Importing with a Dataport?

if it is Using Navision Dataport and your file is in CSV Format, then the following is my Observation:

==========================

This is what I Tried and it worked without any errors :

Define Global Variable T1 (Text 1024)

In your DataPort DataItem for the Table, Instead of using the Actual table Field in the Table, Put the Variable T1 as your Dataport Field.

As far as i can see, once the Variable is defined as Text1024, then the length of the Data being read does not count.

I tried it with Excel cells with Lenght More that 2000 Characters and yet no errors.

However, the maximum Text Field size in a Table is Text250 (as you are already aware).

The System read the Excel file without any Errors.

My Guess is that You maybe reading INTO the TEXT250 Field Directly.
This will generate an error.

Make the DATAPORT FIELD the Variable (Text1024).
Afterwards in the OnAfterImportRecord() Trigger, u Can now Truncate the Imported Data and store as you wish.

SHardy

Hi,

Thanks, but I am importing using Excel automation. Basically it is a file that is used by several people to capture data. They enter data via an autorun user form. As such the file also contains VBA code. There is no function to save the data as a CSV or TXT file.

Currently there is some duplication in work, as where an entry has a populated comment field it is being entered onto Navision as in interaction. I am creating an import function to prevent re-typing.

I was hoping not to require an amendment to the Excel workbook because you can't always be sure how many copies/ versions of these user created Excel files there may be out there. If I don't need to amend the file, then a different version should never cause a problem. Well... hopefully.

As such, my intention is that any one of these files may be directly imported to Navision. I have already written code to prevent duplication, and to split text into many comment lines. This issue with cells containing text longer than 1024 characters is now the only thing that can cause problems.

I hope this makes it a bit clearer what I am trying to achieve and why.

Thanks

SHardy

vgvozdev wrote:

hi,

You can create macro in excel that will return Lenght the field and then run this macro from Navision.

Valentin

As I previously said, ideally I do not want to make any changes to the workbook.

However, I am looking at this as a backup solution.

I have added the following code to the Excel worksheet:

Function ReturnCellLength(CellRef As String)
On Error Resume Next
ReturnCellLength = 0
ReturnCellLength = Len(Range(CellRef).Value)
End Function

How do I then call this from Navision?

I tried as follows:

CellLen := XlApplication.Run('ReturnCellLength',(xlColID + xlRowID));

But it does not like this.

What code should I be using to call this Excel function?

Thanks

themave

why not take a different track, and create the data input forms in Navision and do away with the excel spreadsheet all together. Since it doesn't look like you are going to be able to solve the excel problem, make a better solution instead. If they need certain excel functions to report on the date, then they could pull the Navision data into excel,

SHardy

themave wrote:

why not take a different track, and create the data input forms in Navision and do away with the excel spreadsheet ...

Users...
Politics...
Licensing for extra tables...

They have there (unknown) reasons for wanting to have these Customer Satisfaction Survey results entered into spreadsheets. This is not to change. All that is required is to import any comments as interactions.

Excel is not a great idea, and one of the problems is that people usually end up with different copies or versions in various places. This is why I didn't really want to change the Excel file.

However, it would be handy to know how to pass a cell reference (as text) to an Excel function in order to return the cell length to Navision. I am completely new to Navision and have minimal knowledge of Excel automation.

SHardy

OK. I have got it working by adding a couple of small functions to the Excel workbook. With these changes it would now truncate any values over 1024 chars, and return a message to the user advising of any cell reference that have been truncated.

From the responses that I have had so far, would it be safe to assume that there is no way of achieving this WITHOUT adding this modification to the Excel file? As obviously, this would be preferred.

Thanks for your help,
Simon

SHardy

OK, I was starting to think that the only way forward WAS to modify the Excel file(s).

As such, I created a function that I could call from Navision to check for the cell's length.

This seemed to work fine.

However, this then creates another problem...

As this function call is used within the "Excel Buffer" table's "ReadSheet" function, it now causes a problem with any other dataport from Excel, as that custom function cannot be found.

So I am now in a position where I need to check that the function exists before trying to use it.

Is this possible?

Or am I just digging myself an even bigger hole?

Thanks.

BTW, Hope all had a great Xmas.

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

Gandalf_The_Grey

This will help you read the Entire string, then you can
put it togehter/tear it appart afterwards in Navision.....

1. Save the Value as a Textfile, then read it into a stream and do whatever afterwards.

XFile.OPEN(Filename);
XFile.CREATEINSTREAM(Instr);
WHILE NOT (Instr.EOS()) DO
BEGIN
Instr.READTEXT(TextString,1024);