I was playing a little bit more with a solution for when Street Address, City, and Post Code are in the same Excel field. Let’s assume the following was in the Excel file:
and if you want to sort by just the Post Code (I assume the last two items in the Address field - i.e. N1 385), then you first need to split out the address into the various components. I needed to add a Post Code to each object in the data array, so below are the steps to do it using a forEach loop to modify each object in the array. :
-
I broke out the Address into two parts using ", " as the delimeter. This allows me to have the street address separate from City/Post Code (see cityAndPostCode array in code below).
-
I was able split the cityAndPostCode array into 3 parts (City, Post Code part 1, Post Code part 2), because they were separated by spaces.
-
I reassigned the street address obtained in step 1 back to the object
-
I created a new property called “City” for the city which was in the first element of the array created in step 2.
-
Finally, I had to reassemble the two parts of the Post Code and assign the result back to a new property called “Post Code”
Here is the final JavaScript code to make this work.
print('Please upload the excel file by clicking on the button Choose File\n\n')
loadFile = function(event) {
alasql('SELECT * FROM FILE(?,{headers:true})',[event],function(data){
breakOutAddress(data);
data.sort(function(a, b){
var field = 'Post Code';
return (a[field] < b[field]) ? -1 : (a[field] > b[field]) ? 1 : 0;
});
print('\n' + JSON.stringify(data, null, '\t')+'\n\n');
});
};
breakOutAddress = function(data) {
data.forEach(function(obj) {
var addressArr = obj.Address.split(', ');
var cityAndPostCode = addressArr[1].split(' '); // second element of addressArr contains the city and post code
obj.Address = addressArr[0];
obj.City = cityAndPostCode[0];
obj['Post Code'] = cityAndPostCode[1] + ' ' + cityAndPostCode[2]; // needed to reassemble the post code
});
}
function print(x){
document.getElementById('output').textContent += x;
}
The above code used with the Excel file I showed above produces the following output:
Please upload the excel file by clicking on the button Choose File
[
{
"No": 3,
"Company": "Optic",
"Name": "Mr. Liam Docherty",
"Address": "40 Street",
"City": "London",
"Post Code": "E1 DE2"
},
{
"No": 4,
"Company": "Buildog",
"Name": "Mr. Jon Docherty",
"Address": "10 Street",
"City": "London",
"Post Code": "E3 85A"
},
{
"No": 2,
"Company": "FaZe",
"Name": "Mr. Jack Test",
"Address": "640 Street",
"City": "London",
"Post Code": "N1 385"
},
{
"No": 1,
"Company": "E6",
"Name": "Mr. Harry Nick",
"Address": "4 Street",
"City": "London",
"Post Code": "SW QWE"
}
]
NOTE: Since each object in the data array now has 3 separate properties for address (Street Address, City, and Post Code) instead of the originally combined address, you would need to concatenate them together, if you wanted to display the combined address in the future. Plus, having city separate would allow you to sort by city if needed in the future. If you do not care about being able to sort by city or another newly created property. then you would not reassign the Street Address to the Address property of each Object and not create the City property at all. You would still need the new “Post Code” property to make the sort work correctly. To accomplish this, you would just need to remove the following lines from the breakOutAdress function:
obj.Address = addressArr[0];
obj.City = cityAndPostCode[0];
