I have three databases.
1. Properietary: List of cities.(around 40,000) [Cityid1, Country Name, RegionName1, City Name, Citycustomname, countrycode]
2. Maxmind Location: List of Cities with additional fields. (300,000) [cityid2, Countrycode, RegionID, CityName, L1, L2]
3. Maxmind Regions (List of Region Codes, Region Label) [Regionid, RegionName2]
I want one database for 40,000 records as mentioned in database #1. The records from 2 and 3 needs to be merged to appropriate rows in database#1. So in the end, the final database with 40,000 rows will have the following structure.
[Cityid1, Cityid2, CountryName, RegionName1, RegionName2, City Name, CityCustomName, RegionID, L1, L2, Countrycode]
This will require both the SQL skills and Manual data verification. This can be done all manually if required but bulk can be done using the database.
a. Merge #2 and #3 to create a database #4. So that you get the regionname+cityname+countrycode combination.
b. Find through the database, all the records that match countrycode, Regionname, Cityname. Merge this data as specified above. Store this data in table #5.
c. Find through the database, all the records in #1 table that do not exist in table #5. Store these in table #6.
d. Manually update records in table #6.
e. Merge table #5 and table #6 to come up with finalized table.
We have a quick method to check for accuracy for each row and we will NOT pay unless the data is accurate for every row in the database.
These are all cities so Countrycode, Region etc should all match up fairly easily for the MOST part.
Again, we are OK if you wish to update the records in an excel sheet manually. We just want the final data and as we said, we can check for the accuracy of all 40K records in one click of a button if done right so do bid only if you are going to do this properly.
Payment will be done upon data verification and project completion.
Attached is our properietary file.
Maxmind files can be accessed at:
http://www.maxmind.com/app/fips10_4 (Please choose the appropriate FIPS file listed according to the data in above file).
Again, we want maxmind columns to be added to the allcities.csv (attached) with appropriate values for the row.
Maxmind data has some 300K or more cities. We only want 34K approximate those are listed in the attached excel to be populated with maxmind data columns.
Note: Chinese City names are URL encoded. In the event that cityname column is encoded, please use the citynameunique column. Remove anything after the first dot (.) and you should get the city name in english.
You have all the information now to give the best possible bid.
17 pekerja bebas membida secara purata $179 untuk pekerjaan ini
Good Morning, I have excellent skills in excel. I will not manually merge the data but rather use excel formula and macro to do the job. All 40,000 data can be done in 1 day.