How To Read TAB Delimiter?

Elie · 2010-05-05

I need to create a function to import data into the Purchase Journal. The file I'm importing is TAB delimited but the structure of the data is different for each line in the file, so I don't think I can create a regular Dataport but instead I need to write code to read each line and extract the data I need. My Question is how can I read the TAB delimiter to determine when each field has started and ended?

I have tried to use the ConverString function to convert TAB to pipe symbol "|" but that did not work right it inserted a lot of Pipes and not one pipe for each tab. here is what I used: dhbTextFile := CONVERTSTR(dhbTextFile,' ',' |');

Thanks

Imranx101 · 2010-05-05

Is it a txt file? I may get solution for you just please let me know is it text with TAB delimiter between values or it is excel sheet?

Elie · 2010-05-05

Yes it is a txt file.

Thanks

matttrax · 2010-05-05

You can still use a dataport. Create global text variables to import into. Set the AutoSave, AutoUpdate, etc properties to No. Manually fill in your records with the text variables, using EVALUATE when necessary, and insert them.

Imranx101 · 2010-05-05

Still you can use dataport, instead of messing with head and writing code.
Open excel sheet (empty) and import the data from the this txt file. Use excel function 'Get External Data' and the choose From Text.
Text Import Wizard will be prompted. Make sure you choose delimited option then click Next, here make sure that Tab option has been selected then click Next and Finish.
Your data will be imported in correct format in separate columns. Now save this excel sheet as csv file and the result will be comma delimited excel file and now you can use dataport to import the data.

Thanks

Elie · 2010-05-05

Thanks Matttrax for your reply. I'll try that and let you know..

Thanks again..

Imranx101 · 2010-05-05

Please check my solution as well

Thanks.

Elie · 2010-05-05

Thanks Imranx101 for your reply.

users are getting several files everyday so they do not want to go through opening them in excel then saving them and then importing them. the customer want to press a button and Navision should take care of the rest.

Thanks again Imranx101.

matttrax · 2010-05-05

I suppose I should add, if possible, use an XMLport as Dataports are no longer supported in NAV 2009 RTC.

Savatage · 2010-05-05

Elie wrote:

The file I'm importing is TAB delimited but the structure of the data is different for each line in the file

Elie wrote:

users are getting several files everyday so they do not want to go through opening them in excel then saving them and then importing them. the customer want to press a button and Navision should take care of the rest.

Having a set structure, would be a big leap forward here. Perhaps where ever these files are coming from you can explain to them to use the same layout time after time.
PS..Without seeing your data file we can only point you in the right direction. I prefer Tab delimited over CSV for dataports myself.

matttrax · 2010-05-05

Savatage wrote:

Having a set structure, would be a big leap forward here. Perhaps where ever these files are coming from you can explain to them to use the same layout time after time.

I assumed (bad of me) that there was some sort of flag on each line telling what type it was. If that is not the case then you should definitely get them to standardize this.

Elie · 2010-05-05

There is a flag for each line example:

IH for Header
IS for Lines
and so on...

Thank you

Savatage · 2010-05-05

viewtopic.php?f=23&t=39402

My Question is how can I read the TAB delimiter to determine when each field has started and ended?

Do you know these properties of the dataport?
FieldStartDelimiter -> <None>
FieldEndDelimiter -> <None>
FieldSeparator -> <TAB>
RecordSeparator -> <NewLine>

If each line starts with either IS or IH then your on your way.
as Alex says in the post above import the data into text variable and using if and/or case statements
to map each imported field to the proper nav field. Don't to forget to validate where necessary.
There is also a chapter on dataports in the ADG on the product cd. If you get stuck let us know.

ps this forum is chock full of dataport posts. don't forget search is your friend.

Elie · 2010-05-05

Thank you all for your help.

How To Read TAB Delimiter?

Comments

Categories