This is a very simple project that should not take more than a couple of hours.
In the attached .zip file, you will find 6 files, two of which are excel files and 4 of which are xml files. I need various things added in bulk to the columns in the excel files, and then I need those excel files transformed to XML files identical to the XML files also attached.
First, please open the two excel files. We'll begin with airport_code. Essentially, I am creating a search widget that is going to allow people to search for flights and hotels (all of the information is contained within the two excel files). 90% of the users who use this widget are going to be located in the United States... therefore, we are going to need to give the user the option to search "Domestic" or "International". If the user selects Domestic, it will ONLY give the rows under "airport_codes" that have "United States" under Column B and the rows under "hotel_city" that have "United States" under Column A. If the user selects "International", it will simply give ALL the results in both of the respective excel files.
So for the file [url removed, login to view], we are going to need to copy EVERY entry in the airport_codes excel file that has United States in Column B and create an excel file just for those entries. So we will have two excel files for flights, one of them with ONLY United States entries and one of them with ALL entries. Then, we are going to need to play with some of the entries in bulk to make sure that the entries in the XML file look exactly how we need for them to look in our widget. I would like for you to make all of these changes in the excel file and then also add them to the XML file similar to how the XML files are currently formatted.
In the excel file, for domestic, the XML file for each row is going to need to be read like this: "Miami, FL (MIA) - Miami Intl". (Currently it reads "mia Miami FL Miami Intl" but this is not the way I would like it). Looking to the excel columns, this will be Column D, Column E, Column F, Column G for the Excel file. Also, to make sure the format is perfect, every entry in column D needs to have a comma and a space after it. Every entry in column E needs a space after it. Every entry in column F needs to be in CAPS with parenthesis around it followed by a space a dash and another space. Then column G can remain as-is. Once all of the entries in the excel file have been properly done, all of these entries must be added to an XML file identical to [url removed, login to view], with one row per line. Please send me both the newly created Excel file and newly updated XML file.
International flights is going to be VERY similar to Domestic Flights, but please replace Column E with Column C, and put a comma and space after the entries in Column C. So for domestic, the XML file for each row is going to need to be read like this: "Miami, US (MIA) - Miami Intl". Looking to the excel columns, this will be Column D, Column C, Column F, Column G for the Excel file, with all the same commas and whatnot that was added for domestic. Once all of the entries in the excel file have been properly done, all of these entries must be added to an XML file identical to [url removed, login to view], with one row per line. Please send me both the newly created Excel file and newly updated XML file.
No matter whether it is Domestic and International, the XML file has to be sorted in alphabetical order in by Column D. Column D (the city) is always going to be the first thing that is seen in each row in the XML files.
Hotels are very simple. First of all, open up hotel_city. Please copy every entry with "United States" in column C and paste them into their own Excel file. Then just convert Column D of the new file to an XML file named [url removed, login to view] (similar to the one attached) and then convert Column D of the Excel file with ALL cities (hotel_city) to an XML file named [url removed, login to view] similar to [url removed, login to view] attached. Please send me both the newly created Excel files and newly updated XML files.
VERY IMPORTANT: Everything need to be in alphabetical order based upon the first letter of the entry. You are probably going to need to make sure all of these are in order and double check what you have because it looks like the entries for the United States are in order based upon the state, so you need to make sure that it is in order solely by Column D.
This is a very simple project for anyone who knows the basics of this type of stuff. Please make sure you pay VERY close attention to what is asked because I don't have much time to ensure that everything is done perfectly.
26 freelancers are bidding on average $81 for this job
Hi I can give you this in one day, and with a code whihc can handle any XLs file, so that in feature you need not need to change the code to get the similer thing done.