Hello, I’m new to Navision and I’m having problem importing data via Dataport. I have an external Excel File in .csv format containing data for G/L Account Table. The field in the Excel file mapped to the “name” field in G/L Account table is 40 bytes long while in G/L Account table is 30 bytes long. I tried but with no success. First question How Can I import this field using the dataport? Second Question How can I skip those fields from the Excel file that do not have to be imported into G/C Account Table? Cheers Giovanni Michielin
quote:
Hello, I’m new to Navision and I’m having problem importing data via Dataport. I have an external Excel File in .csv format containing data for G/L Account Table. The field in the Excel file mapped to the “name” field in G/L Account table is 40 bytes long while in G/L Account table is 30 bytes long. I tried but with no success. First question How Can I import this field using the dataport?
Create a global variable, tx40 (well, maybe you can think of a better name), use that in the Dataport Field list to name your 40 bytes long description. In the OnAfterImportRecord trigger for “G/L Account” dataitem write: "G/L Account".Name := COPYSTR(tx40, 1, 30); That will transfer the first 30 characters of tx40 into the G/L Account Name and drop the exceeding 10.
quote:
Second Question How can I skip those fields from the Excel file that do not have to be imported into G/C Account Table?
Originally posted by henrymiller - 2005 Oct 18 : 15:23:51
Again create global variables for each field you don’t want to import (making sure they are of the right type for the fields that they will be associated to) and use them in the Dataport Field list in place of the unwanted fields, then just ignore them. Since their names aren’t in the receiving table they will not go anywhere if they aren’t explicitly assigned to some fields in the table. Anna
Anna, I was doing exactly what you are suggesting. What do you mean by : “use that in the Dataport Field list to name your 40 bytes long description”? I created a global variable and added “G/L Account”.Name := COPYSTR(tx40, 1, 30); in the onAfterImportRecord Trigger. Save and compile Thanks Giovanni
Anna, I was doing exactly what you are suggesting. What do you mean by : “use that in the Dataport Field list to name your 40 bytes long description”? I created a global variable and added “G/L Account”.Name := COPYSTR(tx40, 1, 30); in the onAfterImportRecord Trigger. Save and compile Thanks Giovanni
quote:
Anna, I was doing exactly what you are suggesting. What do you mean by : “use that in the Dataport Field list to name your 40 bytes long description”? I created a global variable and added “G/L Account”.Name := COPYSTR(tx40, 1, 30); in the onAfterImportRecord Trigger. Save and compile Thanks Giovanni
Originally posted by henrymiller - 2005 Oct 18 : 17:05:55
In a dataport there are dataitems which are related to existing Navision tables. The table structure is known to Navision. To each dataitem is also associated a Dataport Fields list (in the dataport editor editor see menu View - Dataport Fields); the Dataport Fields list describes the external file structure. In a variable file format like .csv you need to assign a variable name to each field in the structure. Where the type and lenght of the external field match the type and lenght of the corresponding field in the dataitem table you can assign the same name to the dataport field and have the the receiving field automatically populated, where not you need a global variable and write code in the OnAfterImportRecord trigger to populate the fields. BTW - you should have posted this thread into the Beginners forum or… maybe into the Italian one where I don’t need to struggle to explain myself in a foreign language! [;)] Anna
Hi, A perhaps easier solution to your first question is to put the following code in the OnBeforeEvaluateField trigger of the field: Name - OnBeforeEvaluateField(VAR Text : Text[1024]) Text := COPYSTR(Text, 1, MAXSTRLEN(Name)); Press F9 on the field to find the trigger.
Hi, great! This way works fine. Thank you very much. I didn’t understand the way Anna Perotti explained to me , because I don’t understand how Navision is able to associate the name of each field in in the external .csv record to the global variable (ie tx40). I can’t find any Dataport field list for the external file. Cheers Giovanni
Hi, the field list of the external file is called “Dataport Fields” and you put the cursor on the dataitem, and select View, Dataport Fields. In fact, to realize Steffen’s solutions, you already have been in the dataport field list, to access the OnBeforeEvaluateField trigger. Instead of a table field in that list, you can also “import” to any variable you define within that dataport. Saludos Nils
Nils, maybe there’s a misunderstanding. To me the external file IS my excel file in .cvs format and not “G/L Account” table. Navision doesn’t know at all the record layout and fields name for this .csv file. This file is not a Navision Table. I’m writing from this .csv file directly into “G/L Account” table. On the dataitem I have only “G/L Account” table and when I select view , dataport fields I can select only fields from “G/L Account” table. Cheers Giovanni
Giovanni, I think a understand better your doubt right now… in fact, the dataport itself doesn’t know anything about the “column names” of the csv file, just that each field is seperated by “,” or “;” and that’s it.
quote:
On the dataitem I have only “G/L Account” table and when I select view , dataport fields I can select only fields from “G/L Account” table.
That’s right, you can only select fields from the dataitem table, but you can also type/write any variable name into the dataport field list, if you prefer to assign the imported value to a variable, and then later on modify or assign that variable to another field - in fact that is what Anna is suggesting. Hope i was clearer this time [;)] Saludos Nils
Nils, thank you very much for your explanation!. No I understand, what Anna was trying to explained to me. I didn’t know at all how to use the variable in the datapart fields list. Cheers Giovanni Michielin [8D]
You’re welcome Giovanni, glad I was able to help. [:)]