Hello all,
I am going to describe my specific problem. Also, I can program in C++ & Java although I am a little rusty. I took both classes in order to get my MIS degree but have not touched them since. Kid gloves with me would be awesome.
The Background:
I am pulling data out of student management system (OS/400 /DB2) into Crystal Reports. I have no command line access and will not be able to get it. I can use Crystal to query the student data and make a CSV document. This CSV document is then imported into a 3rd party transportation software to run bus routes.
The Problem:
There are 3 types of formats that the data is in. This is the output from Crystal Reports opened in notepad. The order is as following for the fields. Student.Address , Student.Street_ Name, Student.Street_ Type, Student.Address _Line_2.
Type 1: “59 St S”,,, (This will be correct. It will Import and populate the fields correctly)
Type 2: “81 E Ave, G”,,, (This is incorrect. It imports improperly) I need it to be formatted this way “81 E Ave”,,”G”,
Type 3: “E 65 St S”,,”716” (This is correct and will populate the fields correctly).
The Solution:
(I need help with syntax)
I need help if it is possible to write a Crystal Reports formula (I believe it can use Java syntax, I’ve only done it in C++) to write a regex to read these 4 fields and make sure they are in the correct format so I can properly import them into The Bus transportation Software so these kids get on buses.
My Idea:
If Student.Address = \d*\s\D*\s[Ave, St, Dr, Ln, Ct]
/*digit match followed by zero or more matches AND a white space character AND a non digit match with zero or more matches AND a white space character AND a match to Ave OR St OR Dr OR Ln OR Ct*/
Then execute;
Else I want it to take the number behind the Ave and populate it in the Student.Address _Line_2 field.
Please Help or give any suggestions you can.
I am going to describe my specific problem. Also, I can program in C++ & Java although I am a little rusty. I took both classes in order to get my MIS degree but have not touched them since. Kid gloves with me would be awesome.
The Background:
I am pulling data out of student management system (OS/400 /DB2) into Crystal Reports. I have no command line access and will not be able to get it. I can use Crystal to query the student data and make a CSV document. This CSV document is then imported into a 3rd party transportation software to run bus routes.
The Problem:
There are 3 types of formats that the data is in. This is the output from Crystal Reports opened in notepad. The order is as following for the fields. Student.Address , Student.Street_ Name, Student.Street_ Type, Student.Address _Line_2.
Type 1: “59 St S”,,, (This will be correct. It will Import and populate the fields correctly)
Type 2: “81 E Ave, G”,,, (This is incorrect. It imports improperly) I need it to be formatted this way “81 E Ave”,,”G”,
Type 3: “E 65 St S”,,”716” (This is correct and will populate the fields correctly).
The Solution:
(I need help with syntax)
I need help if it is possible to write a Crystal Reports formula (I believe it can use Java syntax, I’ve only done it in C++) to write a regex to read these 4 fields and make sure they are in the correct format so I can properly import them into The Bus transportation Software so these kids get on buses.
My Idea:
If Student.Address = \d*\s\D*\s[Ave, St, Dr, Ln, Ct]
/*digit match followed by zero or more matches AND a white space character AND a non digit match with zero or more matches AND a white space character AND a match to Ave OR St OR Dr OR Ln OR Ct*/
Then execute;
Else I want it to take the number behind the Ave and populate it in the Student.Address _Line_2 field.
Please Help or give any suggestions you can.