Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

Import item from excel with category

Posted on by 95

Hello 

I have an file xls that have à lot of colons and in this file i have two films catégorie code and sur category so when i validate my table to create item is great but i need to créât category item indented with sur category get from sheet file .

My code is 

Var

Itemcat: record item category;

Tabxbuffer:record mytable;

Item:record item

If not item.get(tabxbuffer.category) tient begin

Itemcat.code:= tabxbuffer.itemcategory;

Itemcqt.code:=tabxbuffer.subcategory;

Itencat.insert();

Please can you help to correct my code

  • Suggested answer
    asouiak Profile Picture
    asouiak 95 on at
    RE: Import item from excel with category

    Thank you , please i have an problème to transféré variable wosh i put copstr of description field in import into my tab× before update item table , how can créât extend text, to and i have to 3 field in my sheet whish is the item attribut how can be for 1 item how to créât item attribut with this 3 field for one item thank you

  • Suggested answer
    TabrezAjaz Profile Picture
    TabrezAjaz 190 on at
    RE: Import item from excel with category

    Nice, to hear back from you that you have resolved.

  • asouiak Profile Picture
    asouiak 95 on at
    RE: Import item from excel with category

    @Tabrez Ajaz thank you i resolved by this code :

    child := ItemRecBuffer.ItemSubCategorie;

                       parentcategory := ItemRecBuffer.ItemCategorie;

                       If not CategorieItem.get(parentcategory) then begin

                           CategorieItem.init();

                           CategorieItem.code := parentcategory;

                           CategorieItem.insert;

                       end;

                       //Child

                       If not CategorieItem.get(child) then begin

                           CategorieItem.init();

                           CategorieItem.code := child;

                           CategorieItem."parent category" := parentcategory;

                           CategorieItem.insert;

                       end;

                       ItemRec.Validate("Item Category Code", ItemRecBuffer.ItemCategorie);

                       // Update or Insert Categorie

    it s ok and please if you can help me for another probléme is :

    i have evaluation ( description) to creat (extend text) on import liste from excel to my TablX:

    with this code

    //Exteded text

               TotalTxt := GetValueAtCell(RowNo, 4);

               if StrLen(TotalTxt) > 100 then begin

                   ItemTab.ItemDesc1 := TotalTxt.Substring(1, 100);

                   ExtTxt := TotalTxt.Substring(101);

               end

               else

                   ItemTab.ItemDesc1 := TotalTxt;

               //Exteded text

    so after kno i need to recuper my ExtTxt to be exteded text for the Item when a validat my tabx

  • Suggested answer
    Govinda Kumar Profile Picture
    Govinda Kumar 2,203 Super User 2024 Season 1 on at
    RE: Import item from excel with category

    Hi,

    Based on the scenario you describe, it seems that the code you provided may not meet your requirements. And Mr Tabrez Ajaz has already provided a code solution for you in his response. Kindly use that code and test if it matches your requirements. However, if you need more information about excel buffer or want to explore other solution, you may refer to this blog post https://yzhums.com/4223

    Regards

  • Suggested answer
    TabrezAjaz Profile Picture
    TabrezAjaz 190 on at
    RE: Import item from excel with category

    Hi asouiak,

    Based on your code and scenario, here is an updated version of the code that should help you achieve what you are looking for:

    // Define the variables
    var
      ItemCategory: Record Item Category;
      TabXBuffer: Record MyTable;
      Item: Record Item;
    
    // Open the Excel file using the Excel Buffer
    ExcelBuffer.OPEN(TabXBuffer, 'MyExcelFile.xls');
    
    // Loop through each record in the Excel file
    WHILE ExcelBuffer.NEXT = true DO BEGIN
      // Get the category and subcategory codes from the Excel file
      ItemCategory.GET(TabXBuffer."Category Code", TabXBuffer."Sub Category");
      // Check if the category exists in the Item Category table
      IF ItemCategory.GET(ItemCategory."Code") THEN BEGIN
        // The category already exists, so use its code to set the category code for the new item
        Item."Item Category Code" := ItemCategory."Code";
      END ELSE BEGIN
        // The category does not exist, so insert a new category and use its code to set the category code for the new item
        ItemCategory."Code" := TabXBuffer."Category Code";
        ItemCategory."Description" := 'Category Description';
        ItemCategory."Insert";
        Item."Item Category Code" := ItemCategory."Code";
      END;
      
      // Set the remaining fields for the new item and insert it
      Item."No." := TabXBuffer."Item No.";
      Item.Description := TabXBuffer.Description;
      Item."Base Unit of Measure" := TabXBuffer."Base UOM";
      Item."Insert";
    END;
    

    In this updated code, we first open the Excel file using the Excel Buffer and loop through each record in the file. For each record, we get the category and subcategory codes from the Excel file and check if the category already exists in the Item Category table. If it does, we use its code to set the category code for the new item. If it does not exist, we insert a new category and use its code to set the category code for the new item. Finally, we set the remaining fields for the new item and insert it into the Item table.

    Note that you may need to modify the code based on the specific field names in your tables and the structure of your Excel file.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templates⚡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,228 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans