Hi Folks -
I have a XMLport reading a .CSV file which is comma separated. When each line is imported how do I check for the No. of characters in the line. In the below example I have 30 characters in the second line of the spreadsheet and second line I have 35 characters due to Column G, as and when the line is imported if the no. of characters exceeds 30 characters I want to skip the line. Is it doable? I would truly appreciate if someone knows how it can be done. Any help is truly appreciated.
So you have a CSV file with 7 columns/fields, and you want to exclude importing lines with the total string length of the 7 fields is more than 30? Sure you can do it, but not unless you import it to a "buffer" first. Meaning you disable the XMLports automatic handling of inserting the records and use the STRLEN(FieldToCheck) command to check each column, before insert the record.
But if the data is supposed to do anything in regards to "Prod. Order No.", "Serial No." and those kind of data, then my guess is that its more a subject for using a "buffer table". Here you have a "simple" table, with basically the 7 fields, plus a primary key and a status field. Then I would import everything into the table. The fields would be text fields, so that anything basically can be imported, and then do the field validations after reading the data. This would allow me to validate the data (i.e. does the customer no. exists) to make the line status = error, or in your case "Skipped because of more than 30 characters". It allows for professional error handling and logging and that that's how I would always import anything.
That is at least how it was in Navision and the early days of Dynamics NAV. Now a days I would either use an API or the exchange data framework. The latter takes a bit of learning, but once you got it, then you could import data like that, with just a few lines of actual code (which is only required because of your special validation).
PS: Sorry for the late reply. Have been a offline for a while...
I still use that method with complex data--import everything into tables that simply mirror the data, then use a codeunit or report to actually populate the NAV tables. Makes the XMLPorts much simpler, and as you say much more control over validation, etc. on the actual data writing.
What's " the exchange data framework"? Is that RapidStart?
It's not exchange data framework, it's of course the data exchange framework!
But it's a great tool which allows you to define almost any import/export via data definitions, and standard file handling etc. Use by standard NAV/BC for payroll and bank statement imports. https://docs.microsoft.com/en-us/dynamics365/business-central/across-about-the-data-exchange-framework
Thanks Erik, I had to do exactly the same way you have suggested and it works like a charm. Thanks again for replying.