Problem when export data from excel

emigrant

Hello!

I have error when import data from excel 'The length of the text string exceeds the size of the string buffer'.
Can you help me with this problem? How I can increase string buffer?

Thanks in advance!

ara3n

You can't, if the length of text is longer than 1024, Nav cannot pass the string to your variable.

You option is to save it as xml and use xmlport and bigtext to load the file. :roll:

emigrant

XlRange := XlWrkSht.Range(ExcelBuf.GetExcelReference(5)).SpecialCells(11);
Maxi := XlRange.Row;
Maxj := XlRange.Column;
i := 2;
REPEAT
  Item.INIT;
  //ItemVend.INIT;
  xlRowID := FORMAT(i);
  j := 1;
  REPEAT
      CASE j OF
        1: Item.VALIDATE("No.", FORMAT(XlWrkSht.Range('A' + xlRowID).Value));        
        2: EVALUATE(RequiredAction, FORMAT(XlWrkSht.Range('B' + xlRowID).Value));
        3: Item.VALIDATE(Description,XlWrkSht.Range('C' + xlRowID).Value);
        4: Item."Package Size" := FORMAT(XlWrkSht.Range('D' + xlRowID).Value);
        5: EVALUATE(Item."Creation Date", FORMAT(XlWrkSht.Range('E' + xlRowID).Value));
        6: EVALUATE(Item."Deleted Date", FORMAT(XlWrkSht.Range('F' + xlRowID).Value));
        7: EVALUATE(Item."Combi Pack", FORMAT(XlWrkSht.Range('G' + xlRowID).Value));
        8: EVALUATE(Item."Bar Code", FORMAT(XlWrkSht.Range('H' + xlRowID).Value));
        9: Item."Pharmaceutical Regulations 1" := XlWrkSht.Range('I' + xlRowID).Value;
        10: Item."Pharmaceutical Regulations 2" := XlWrkSht.Range('J' + xlRowID).Value;
        11: Item."Subsidy Group" := XlWrkSht.Range('K' + xlRowID).Value;
        12: EVALUATE(Item."Unit Price", FORMAT(XlWrkSht.Range('L' + xlRowID).Value));
        //13: reserved for Sales Price
        14: EVALUATE(Item."Vendor Unit Factor", FORMAT(XlWrkSht.Range('N' + xlRowID).Value));
        15: Item."Base Unit of Measure" := XlWrkSht.Range('O' + xlRowID).Value;
        16: Item."Conversion Code" := XlWrkSht.Range('P' + xlRowID).Value;
        17: Item."Brand Item Division" := XlWrkSht.Range('Q' + xlRowID).Value;
        18: Item."Brand Item Subgroup" := XlWrkSht.Range('R' + xlRowID).Value;
        19: Item."Calculation Code" := XlWrkSht.Range('S' + xlRowID).Value;
        10: Item."Packaging Group" := XlWrkSht.Range('T' + xlRowID).Value;
        21: Item."Packaging Type" := XlWrkSht.Range('U' + xlRowID).Value;
        22: Item."Tertiary Packaging" := XlWrkSht.Range('V' + xlRowID).Value;
        23: Item.Narcotics := XlWrkSht.Range('W' + xlRowID).Value;
        24: EVALUATE(Item.Strength,FORMAT(XlWrkSht.Range('X' + xlRowID).Value));
        25: Item."ATC Code" := XlWrkSht.Range('Y' + xlRowID).Value;
      j := j + 1;
  UNTIL j > Maxj;
  IF RequiredAction = RequiredAction::N THEN 
    Item.INSERT(TRUE);

  ItemUnitOfMeasure.INIT;
  ItemUnitOfMeasure."Item No." := Item."No.";
  ItemUnitOfMeasure.Code := Item."Base Unit of Measure";
  ItemUnitOfMeasure.VALIDATE("Qty. per Unit of Measure");
  ItemUnitOfMeasure.INSERT;

  Item.VALIDATE("Base Unit of Measure");
  Item.VALIDATE("Vendor No.");

  i := i + 1;
  Window.UPDATE(1,ROUND(i / Maxi * 10000,1));
UNTIL i > Maxi;

Here is piece of my code.
In excel I don't have information with length more than 1024.
May be I don't understabd somthing? :-k

ara3n

turn on the debugger and find out on what line it errors out.

emigrant

on 16 line. But there only 1 symbol in excel.

ara3n

save the excel file as tab delimited, does it still have just one letter? Find the item record it is importing, There should be hidden characters or something.

emigrant

I am sure that this colomn have only 1 character, and I have only one line, its a test file and all data I write manualy. All other fields contain not more then 12 characters. I have no idea why I have this error. Maybe all line writing in this buffer, but I now that all line will contain maximum 120 characters, or maybe colomn with date type or with numeric type occupies a lot of place in this buffer.

emigrant

I found :bug: .All ok now \:D/

ara3n

would you share what the problem was?

Another column?

blogdynamics

Hi I solve this problem, you can view the solution on this post

http://blogdynamics.blogspot.com/2010/1 ... es-de.html

Luc_VanDyck

blogdynamics wrote:

Hi I solve this problem, you can view the solution on this post

Is this going to be a new trend: answering a forum question in a (non-English) blog post, and refer to that blog to read the answer? I hope not.

Joe_Mathis

Google Translate makes it:

The shape of Dynamics Nav to import data from excel is through a table called Excel Buffer and the book Microsoft Excel 12.0 Object Library.
Is loaded in each table entry value of each cell in the Excel spreadsheet and a series of properties (font, formula, etc. ..). The problem I have raised is that the library has a 1000 character string buffer, if a leaf cell has more than 1000 characters to use the Value method to get the contents of the cell and record it in the Excel Buffer table fails buffer overflow in the library. Obviously the size of the buffer can not be changed in the dll.
To fix it I have done the following:




XlWrkSht.Range ("IV3 '). Formula: =" = MID (' xlColID xlRowID ', 1, 1000)';
XlWrkSht.Range ("IV4"). Formula: = "= MID ('xlColID xlRowID'; 1001; 1000) ';
XlWrkSht.Range ("IV5"). Formula: = "= MID ('xlColID xlRowID', 2001, 1000) ';


locTextLargo1: = FORMAT (XlWrkSht.Range ("IV3 '). Value);
locTextLargo2: = FORMAT (XlWrkSht.Range ("IV4"). Value);
locTextLargo3: = FORMAT (XlWrkSht.Range ("IV5"). Value);


What I do is use the formula in Excel to record blocks REMOVE thousand thousand characters of the cell that I'm importing (xlColID xlRowID) in other cells, in this case I use IV3 IV4 IV5 cells that being the last columns I assume they will be empty. Then we can obtain the values of the three separate cells.