I need a desktop deployed application that accepts EXCEL /CSV files only (phone book list), parse it and runs the following procedures:
1. Normalize it, removing all of the " - , / ; . * +_ ?" and similar keys from the file data.
2. Make all letters capital letters, for example Jim will be JIM,
3. Check for duplicates lines AND check for duplicated table phone numbers cells from the phone numbers column. if there is a duplicated phone number you will need to write down in the "same number lines warning: " column all the repeated phone numbers.
after that first you need to erase the remainig phone number duplications. so in total there will be only the first one with the warning column (see attached examples)
4. It will check for anything that is not a number i.e a letters in the phone column cells. if there is a letter it will erase it.
5. Make sure all letters are in English letters, if not the letters will be erased.
6. Duplicated lines will be narrowed to one line only at the final output file as depicted in the attached examples.
7. Validate the phone number to be a USA legitimate landlines/cellphone number - check against area code act.. as much as possibly can without actually calling it. you can use the following link as an information help in order to understand how to determine if a number is legitimate. [url removed, login to view] &[url removed, login to view] but you have to find how to do this for cellphones in the USA as well.
8. if prior to a valid phone number you have +1 or 1, leave it because its fine, but if the valid phone dont have +1 or 1 you should edit the number and add +1.
8. The entire procedure will not take more than 5 seconds per a 100 names entree phone book.
9. 1-800 / 1-700 phones cells will be deleted , it will give an alert at "1-800/1-700 NUMBER" column .
10. The output file will be an Excel file normalized with a warning report column as depicted in the attached file stating which lines and cells are duplicated, for example look at the attachments.
11. The output file will be also stating which phone numbers are not legitimate at the USA in the "Not A USA number
The file will be created and saved on the desktop edited with the same name as a new file+checked .
* The desktop application Jar will be submitted with all source files!!
* The desktop application will be submitted with a Bat executable file for quick deployment.
An extra bonus payment will be given if you supply the Jar as a Java web service application (with source code files) for a server that will get handles these Java requests .
I've attached 2 simple phone book Excel files (4REPAIR and Good) with people for you to understand the requirement,
In order to apply please rename and resend back the "Good" Excel file to be "GoodFreelancer" so I can make sure you have actually read the entire request.