mibuso.com
Home› NAV/Navision Classic Client

How to import data directly from Excel

vyankuvyanku Member Posts: 791
edited 2010-10-27 in NAV/Navision Classic Client
How can I import data directly from Excel sheet?
If we copy daa from excel to notepad we have to arange all data.But if we have very big data then what can we do?

Comments

  • lubostlubost Member Posts: 632
    There exist some ways to do it. Easiest of them is, I think, save your Excel sheet in some kind of CSV file and create simple dataport to import it. You can also use Excel automation, but it needs a bit more knowledges.
  • vyankuvyanku Member Posts: 791
    When I import the data it shows me error G\L account no. is already exist.
    But actually there is no no. in G\L account table.
  • MbadMbad Member Posts: 344
    Exactly in what table are you trying to insert?
  • nunomaianunomaia Member Posts: 1,153
    Navision has a tool called Excel Mapping tool. With this you can import and export directly from any table.

    This tool is under Setup Checklist
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • vyankuvyanku Member Posts: 791
    I am trying to insert in table G\L account
  • vyankuvyanku Member Posts: 791
    Navision has a tool called Excel Mapping tool. With this you can import and export directly from any table.

    This tool is under Setup Checklist

    When i am mapping and import the data it shows me error
    "Message is for c\cal programer"
    Could not invoke the member name.The OLE control or outomation server returned an unknown error code
  • JamieBrownJamieBrown Member Posts: 107
    vyanku, when you import a record into a table you have to populate the primary key(s) in the table, (the tables unique field(s)) in this case it's No.

    So you need to make sure each record in the the *.csv file you're importing from has a unique account no.

    hope I've helped.
  • nunomaianunomaia Member Posts: 1,153
    You must have Excel installed in computer.

    Export the table to Excel from Cronus to see the format. Import using a similar format.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • vyankuvyanku Member Posts: 791
    I have the same account no. in txt file.
    You must have Excel installed in computer.

    Export the table to Excel from Cronus to see the format. Import using a similar format.

    I there any other option in installation of excel .I already install excel as typical installation.
  • JamieBrownJamieBrown Member Posts: 107
    Just to double check, are you trying to import to the G/L Account table or the G/L Entry table?
  • vyankuvyanku Member Posts: 791
    G\L account
  • JamieBrownJamieBrown Member Posts: 107
    If you're trying to import a csv file using a dataport. You need to make sure you're: 1) Populating the tables primary field (in this case the No.) 2) and that the field is unique in the csv and on the table.

    e.g. 1000,Account Name,Search Name,Account Type etc etc
  • ssinglassingla Member Posts: 2,973
    If you are using version >= 4.0 SP1 then use Mapping Form. Its a good functionality and import data directly from Excel.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • vyankuvyanku Member Posts: 791
    I am using 4.0 SP2
    But by using mapping tool I got the same error like
    "Message is for c\cal programer"
    Could not invoke the member name.The OLE control or outomation server returned an unknown error code
  • HalMdyHalMdy Member Posts: 429
    Check wich object gives the error and try to recompile it , that could help ...
  • nunomaianunomaia Member Posts: 1,153
    You got the error exporting or import ?
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • ssinglassingla Member Posts: 2,973
    There is a strange thing about this mapping tool that it gives error in few cases and in few it runs perfectly. Try the following steps , it might help:

    Create a Code, define the table number, and give a new file name. Specify a mapping group. First Data Row as 5 (It should be 5 though I do not know the reason but if I change it, it gives error). From the function run "Insert Fields from Table" and do not select auto coloumn. Delete the fields not required by you. Then click "Line" button and select Auto Coloumn. Click Function and select "Export Definition". Ensure that no excel worksheet is open at that time. Once the definition is exported open the file and copy paste all your data in this sheet. After you are done on the mapping form select the file name again (Do remember to do it ) and then import. I hope there will be no problem. Also the computer you are using should have Microsoft Office with Excel installed on it.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • vyankuvyanku Member Posts: 791
    It dosend show me any error but It dosend import any data.Nothing is happend.
  • ssinglassingla Member Posts: 2,973
    Have you reselected the file name??
    CA Sandeep Singla
    http://ssdynamics.co.in
  • vyankuvyanku Member Posts: 791
    Oh. After reslecting the file it shows me same error
  • aksmaaksma Member Posts: 4
    I've been working with the same problem, and found out that if there is an option-field in the table, you try to import, you get the message.

    Try to avoid importing option fields. Just remove the mapping to that field

    Regards
    Aksma
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Hi,

    Most pobably you installed Excel after the Navision client. Sometimes it matters.

    Deinstall Navision, clean installation folder, and reinstall it once more. Do not copy installation from another comuter, just make fres install from CD.

    Regards,
    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • aksmaaksma Member Posts: 4
    I'll try to explain a little bit better, what to do about the error:

    "Message is for c\cal programer"
    Could not invoke the member name.The OLE control or outomation server returned an unknown error code"

    This error comes from an option field, where you have the text for the option instead of the code. Code is 0,1,2...n.

    Try to replace the text in your table with the code instead, before importing your excel-file to NAV.

    If you don't know, which fields are option-fields, start with "export definition". In the first row, where you have an option field in a column, you will see at red triangle in the excel-file, meaning a remark. Open the remark and you will see the option list.
  • aksmaaksma Member Posts: 4
    One thing more. If you don't get anything imported, it's probably because the code name in the form in NAV is not the same as the sheet name in excel.

    The code is referring to the sheet name in excel. You can have more than one sheet in an excel file, and import each sheet to NAV, just by changing the code name to the same as the sheet name, you wan't to import.
  • ssinglassingla Member Posts: 2,973
    =D>
    CA Sandeep Singla
    http://ssdynamics.co.in
  • HanenHanen Member Posts: 281
    Go to the table ExcelBuffer and look for the automation var you will find type unknown replace it by MS Office Excel 11.0 I think.

    You may use Excel 2007 and the automation defined on this ExcelBuffer Table is for Excel 2000 something like that, all you have to do is to change the right version.
    Regards

    Hanen TALBI
Sign In or Register to comment.