Dataport: Importing text field to date field in table.

Aravindh_Navision

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.

vijay_g

How's the date field value in text format?

Aravindh_Navision

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

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.

Aravindh_Navision

Hi,

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

Thanks,
Aravindh.

Savatage

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

Aravindh_Navision

Thanks Savatage. It works perfect. How to handle the text file which contains characters around 2300?

Aravindh.