Options

Problem when export data from excel

emigrantemigrant Member Posts: 6
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!

Comments

  • Options
    ara3nara3n Member Posts: 9,256
    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:
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    emigrantemigrant Member Posts: 6
    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
  • Options
    ara3nara3n Member Posts: 9,256
    turn on the debugger and find out on what line it errors out.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    emigrantemigrant Member Posts: 6
    on 16 line. But there only 1 symbol in excel.
  • Options
    ara3nara3n Member Posts: 9,256
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    emigrantemigrant Member Posts: 6
    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.
  • Options
    emigrantemigrant Member Posts: 6
    I found :bug: .All ok now \:D/
  • Options
    ara3nara3n Member Posts: 9,256
    would you share what the problem was?

    Another column?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    blogdynamicsblogdynamics Member Posts: 3
    Hi I solve this problem, you can view the solution on this post

    http://blogdynamics.blogspot.com/2010/1 ... es-de.html
    Be Dynamics
  • Options
    Luc_VanDyckLuc_VanDyck Member, Moderator, Administrator Posts: 3,633
    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.
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • Options
    Joe_MathisJoe_Mathis Member Posts: 173
    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.
Sign In or Register to comment.