Dataport: Importing text field to date field in table.

Aravindh_Navision
Aravindh_Navision Member Posts: 258
edited 2011-07-20 in NAV/Navision Classic Client
Hi,

I have a requirement to import data from text files of fixed length to table. When I try to import the text (for date) field to date field in the table, I am unable to do it.

My requirement is how to convert the text field to date field and store in the table? I have searched in the forum, but I could not able to fine the solution.

Can anyone help me in achieving this?

Thanks,
Aravindh.

Comments

  • vijay_g
    vijay_g Member Posts: 884
    How's the date field value in text format?
  • Aravindh_Navision
    Aravindh_Navision Member Posts: 258
    Hi Vijay,

    02011062201400200000001 - are the data in text file.

    Fieldwise

    Field 1 - 0
    Field 2 -20110622: This is the format of the date field in text format (yyyymmdd)
    Field 3 - 01
    Field 4 - 4002
    Field 5 - 00000001

    Thanks,
    Aravindh.
  • matttrax
    matttrax Member Posts: 2,309
    Use the COPYSTR function to parse the data into substrings and build your date using DMY2DATE. You'd have to import it into a temporary text variable instead of the actual date field.
    Matt Traxinger - ArcherPoint
  • Aravindh_Navision
    Aravindh_Navision Member Posts: 258
    Hi,

    Tried in different possibilities. Can you still more clear, how to approach?

    Thanks,
    Aravindh.
  • Savatage
    Savatage Member Posts: 7,142
    Your Data::02011062201400200000001

    Global Variables
    ImportString->Text->250
    isDay->Integer
    isMonth->Integer
    isYear->Integer

    View->Dataport Fields->SourceExp-> (type in) ImportString
    OnAfterImportRecord()
EVALUATE(isYear, COPYSTR(ImportString,2,4));
EVALUATE(isMonth, COPYSTR(ImportString,6,2));
EVALUATE(isDay, COPYSTR(ImportString,8,2));

"YourTable".Field1 := COPYSTR(ImportString,1,1);
"YourTable".Field2 := DMY2DATE(isDay, isMonth, isYear);
"YourTable".Field3 := COPYSTR(ImportString,10,2);
"YourTable".Field4 := COPYSTR(ImportString,12,4);
"YourTable".Field5 := COPYSTR(ImportString,16,8);
    If the key fields need validation it would be like:://ie.
    VALIDATE("YourTable".Field1, COPYSTR(ImportString,1,1));

    The 4 fields other than the date field are type TEXT.
    If the field you are importing to are not type text //ie Integers or something use EVALUATE.

    Save->Compile->Enjoy
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • Aravindh_Navision
    Aravindh_Navision Member Posts: 258
    Thanks Savatage. It works perfect. How to handle the text file which contains characters around 2300?

    Aravindh.

Categories