Hi, I’ve been browsing all the post related to dataports and how you can manage to save an excel file to text (tab delimited) and import the text file as is, so that the 1st line (header) of the file will have to be removed in the dataport. Well folks, i might be doing something wrong here but i guess i tried almost all solutions there are in the book but the bottom line is that in Navision 3.60 or 3.70 CurrDataport.SKIP just doesn’t work unless the 1st line is blank. Basicly what i am doing is something like this: OnAfterImportRecord=BEGIN IF ImpHeader = 0 THEN BEGIN ImpHeader := 1; CurrDataport.SKIP; END; Any comments on this would be most appreciated as i would like to create a dataport with this feature working as it’s suppose to. Thanks. Joao Jesus NSC IBdos Portugal jpjesus@ibdosportugal.com
I haven’t tried it, but shouldn’t you put the SKIP in the OnBeforeImportRecord trigger?
**OnInitDataport()** BeginOfFile := TRUE; **OnPreDataItem()** // tmpString is Text 1024 IF BeginOfFile THEN BEGIN CurrFile.TEXTMODE := TRUE; // so the READ function will only read one line CurrFile.READ(tmpString); // X times where X is the nb of lines in your header MESSAGE('header removed: %1', tmpString); BeginOfFile := FALSE; END; Hi If i understand the question correctly, when i know how many lines at the beginning of the file the header is (Usually 1 line) i have code like this OnPreDataItem() RecCount = 0 OnAfterImportRecord() RecCount := RecCount + 1; IF RecCount = 1 THEN CurrDataport.SKIP;
I see one problem with Stephen’s solution… If the file (csv) contains for example some integers, the headers will still be in text. ex: Player No,Hits,PP,BB 54,32,12,66 67,12,10,3 In this case, your dataport fields will be integers and you will have the error “You cannot enter ‘Player No’ in Integer”.
Hi David What i forgot to say was, I import the Data into Text Variables then, assign the variables after any Check/Validation is done. OnPreDataItem() RecCount = 0 OnAfterImportRecord() RecCount := RecCount + 1; IF RecCount = 1 THEN CurrDataport.SKIP; Evaluate(TextVar1,Player No); Evaluate(TextVar2,Hits); Evaluate(TextVar3,PP); Evaluate(TextVar4,BB);
First of all I would like to thank you guys for taking the time to help me out with this. I just try the solution presented by David Godbout and it works just fine. The first run I got a new error because i was using the callvalidate on all fields, and i had to remove it from the fields that have ‘modify’ or ‘insert’ in the code under this function. So thank you, David. [:D]
Later tonight i will be posting some of the conclusions i gather while trying to work this dataport issue so that others might be able to save time and better understand how dataports behave. Dataports are easy to use and a great tool to import/export data. However, as I am discovering, when you want to take the next step using them, it’s better to have a good knowledge of their nature. So I am learning something new everyday. Great.[8D]
As promised here I am to share some more information regarding Dataport. During the process of finding out how to automate the creation of a simple dataport based just on a single table, putting an header to each field exported, changing data with excel and then importing it as it is, with the 1st line being the header, my biggest problem was how to get rid of the header under import. Thanks to David I was able to overcame this and now I am a happy guy.[:D] This said, here are some of my new found knowledge over navision dataports: 1st of all one word of advice: K.I.S.S. Keep It Simple (the last S stands for…Stupid). This is sort of a joke but true. How does import work? First of all it reads the data file and interpret the 1st record. It formats the fields and evaluates if the information being imported matches the field type. This was my 1st problem, because only after this evaluation takes place it will move on to the SKIP command. So, if one of the fields on the header doesn’t match this it will produce an error and does nothing. However if you leave the 1st line as blank, the SKIP command will work. If you select one field of your dataitem and then hit F9 you will see the code for each field. If you place a SKIP here it will manage to SKIP not the record but the evaluation of this single field. So this might be usefull in some situations. After i try the code provided by David, another error occurred. This error happened because in all fields I placed the property CallFieldValidate = Yes. I believe it is a good policy to do this. In most cases you should do it. There are, however, some field validations that contain INSERT or MODIFY commands. If that is the case then this property must be set to it’s default value ‘No’. All this information might seem pretty obvious to some people. But then again others might not agree with that view. Wich is why I decide to share this information. From all the posts I’ve seen while searching for a solution, people usually post a message asking for help. In most cases there is always someone trying to help out wich is very positive. What I don’t see is the person who was asking for help in the 1st place, after they manage to work out a solution posting it and sharing the result and their recent acquired knowledge. So to finish I would like to challenge all who eventually might read 'till the end of this message to start giving back to this small community a little bit more. I believe we would all gain much more with it. Thank you all for your help.
David’s solution is neat and easy. I have 2 minor reservations though:
- The use of the variable BeginOfFile seems superfluous.
- If the dataport’s 1. line contains more than 1024 characters (unlikely, maybe) you’re in trouble. In this case you’d have to use either Stephen’s suggestion or, perhaps simpler, inserting an Integer DataItem (Number = 1, skipping the header line) will do the job.
Well, all problems seems to have more than one solution. And this may be the case. Personally I like David solution because it’s simple. And if you only have one line you wanna remove then it will do the trick. With, of course’ the 1024 lenght in mind. So it is not a perfect and universal solution, but it does solve part of the problem. The reason for the BeginOfFile is just to execute this piece of code once, therefore removing just one line. If you would like to remove more than one line then you can adapt it to an integer variable and change the cicle to as many lines you wish to remove. But I guess you know this much already. I think I should clarify the origin of the problem i had when i posted the first time. I was in the process of adapting as tool i got from the web (Dataport Generator) into doing a litle bit more than it was doing. So here is what it is doing right now, apart from it’s original features: 1. Uses a report (adapted from a codeunit provided by Navision I think) called Ansi2Ascii, so that when I generate the code certain types of fields will be processed with this report to avoid strange characters. 2. Creates the header file for each field in the dataport, so that editing the exported file will be more easier to understand 3. and after manipulating the data on a spreadsheet, saving it back to a text file, being able to import it directly without the need to open the file and removing the header line. 4. Places a validate on all the fields: I choose to do this on code instead of properties to make it easy to change on or off as you please. You have to keep in mind that this is a tool to generate dataports the easy way. Then you just need to clip out what you don’t need and that’s it. Instead of constructing a dataport from scratch, i build a complete version and then just cut what i don’t need. I find it more easy this way. But this is just my opinion. In this case there is no right or wrong, just the way i choose to do it because it works for me and the people i work with. Cheers, -
quote:
The reason for the BeginOfFile is just to execute this piece of code once, therefore removing just one line.
My point was that the piece of code will execute only once whether you use the variable BeginOfFile or not, since it’s placed in the OnPreDataItem trigger.
Hey, don’t get mad with me [:)] Of course you are right about that. Yes it is not necessary at all. I didn’t realized it before because i was more than happy with the solution. Thanks for calling my attention to it.
One question still regarding this topic: With the code on the trigger OnPreDataItem, it is possible to avoid loading the 1st line of the text file without any sort of validation. My question is: What about if I have 2 lines i want to ignore? or 3? If I move the code to the OnBeforeImportRecord, the data will be validated by the dataport and if the data types mismatch or anything an error pops up, so this rules out CurrDataport.SKIP (I think). How do you ‘force’ a new record when reading the text file using TEXTMODE and placing the code on the trigger OnPreDataItem? Thanks
Hi João, (olá? [:D]) I think you answered yourself even before you thought about the question [:)]
quote:
Originally posted by jpjesus
If you would like to remove more than one line then you can adapt it to an integer variable and change the cicle to as many lines you wish to remove.
When you set the File.TextMode property to TRUE, each time you call the File.READ function it will read 1 line of text. You just have to put the File.READ inside a loop to remove the desired number of header lines. If you want to do it really nice, put an option in the RequestForm so the user can select the number of lines to be skipped. Good luck (boa sorte [:)])
Hi Nelson, Nice to meet you (prazer em te conhecer) [:D] I just try it and it works perfectly. Here’s a sample code: OnInitDataport BeginOfFile := 0; OnPreDataItem CurrFile.TEXTMODE := TRUE; REPEAT CurrFile.READ(tmpString); BeginOfFile += 1; UNTIL (BeginOfFile = 3); Cheers,